Convert Xlsx to CSV UTF-8 format

Question

i want to convert my XLXS file to CSV UTF-8 format using vb script or macros.

    if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"enter code here

The above script works fine for normal formats.

Please help me in converting in into UTF-8 format

i have also tries the below ,code but it converts into junk characters

Public Sub convert_UnicodeToUTF8()

   Dim parF1, parF2 As String

   parF1 = "C:\shrangi\SX_Hospital.xlsx"

   parF2 = "C:\shrangi\SX_Hospital.csv"

    Const adSaveCreateOverWrite = 2
    Const adTypeText = 2

    Dim streamSrc, streamDst ' Source / Destination
    Set streamSrc = CreateObject("ADODB.Stream")
    Set streamDst = CreateObject("ADODB.Stream")
    streamDst.Type = adTypeText
    streamDst.Charset = "UTF-8"
    streamDst.Open

    With streamSrc
        .Type = adTypeText
        .Charset = "UTF-8" 
        .Open
        .LoadFromFile parF1
        .copyTo streamDst
        .Close
    End With
    streamDst.SaveToFile parF2, adSaveCreateOverWrite
    streamDst.Close
    Set streamSrc = Nothing
    Set streamDst = Nothing

End Sub

refer to [this](https://stackoverflow.com/questions/46589803/create-csv-file-from-excel-data-for-an-each-distinct-value-in-a-column/46597259#46597259) — Dy.Lee, Jan 12 '18 at 14:08
The advantage of VBA is that it is stored with and runs in the context of the document. You seem to be processing external files. Would using a different programming environment such as PowerShell be more appropriate? — Tom Blodget, Jan 14 '18 at 15:59
https://stackoverflow.com/users/2226988/tom-blodget how can i achieve it using powershell — Shrangi Tandon, Jan 16 '18 at 07:29

score 1 · Answer 1 · answered Jan 12 '18 at 08:28

1

Simply:

ActiveWorkbook.SaveAs Filename:="C:\yourPath\yourFileName.csv", FileFormat:=xlCSVUTF8

More Info:

MSDN: Workbook.SaveAs Method

answered Jan 12 '18 at 08:28

ashleedawg

18,752
7
68
96

since i am new to vba how to integrate it in existing script without using ActiveWorkbook – Shrangi Tandon Jan 12 '18 at 08:51
This solution only works with office 2016 . How can i achieve this in office 2010 – Shrangi Tandon Jan 12 '18 at 11:46
1

Sort of a workaround *before Office 2016* would be to assign `xlUnicodeText` to the *FileFormat* argument, e.g. `ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\test.txt", FileFormat:=xlUnicodeText`. Thereafter you could change delimiters manually or save the text file as 'test.csv' or whatever. – T.M. Jan 21 '18 at 17:42
There's some problem with xlCSVUTF8 being undefined for some users, even when using Excel 2016 (which I'm using). I've been searching for a while and haven't found anything about how to fix it. – StevenHB Apr 25 '18 at 20:15

score 0 · Answer 2 · answered Jan 21 '18 at 16:30

Since you are converting an external file to an external file, you don't need to do it within Excel with VBA. That opens up some possibilities. With the OpenXML SDK you don't even need Excel.

OpenXML SDK is a bit hard to use so there are a few wrappers for it to optimize Workbook programming. EPPlus has a PowerShell wrapper around it called PSExcel. It makes this task really easy in PowerShell

One-time setup, typically as an Administrator:

Install-Module PSExcel

Once per PowerShell session:

Import-Module PSExcel

Then:

Import-XLSX 'C:\shrangi\SX_Hospital.xlsx' | Export-CSV 'C:\shrangi\SX_Hospital.csv' -Encoding UTF8

For a simple workbook, that's all you need.

Side note on CSV: Converting from xlsx to csv throws out almost all the metadata and introduces the need for more metadata. Along with the file, you need to communicate the character encoding, the data types of each column, whether there is a header row, the line terminator, the field separator (not always comma), the culture-specific numeric formatting, the quote character (aka "text qualifier"), and the quote character escape mechanism. You can see all of these question that Excel has to ask when you use its text import wizard.

Convert Xlsx to CSV UTF-8 format

2 Answers2

Linked