CODEWITHSUNDEEP
excelvbautf-8character-encodingexport-to-csv
Shrangi Tandon
Shrangi Tandon

Reputation: 63

Convert Xlsx to CSV UTF-8 format

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

Upvotes: 0

Views: 5715

Answers (2)

Tom Blodget
Tom Blodget

Reputation: 20772

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.

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21619

Simply:

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

More Info:

Upvotes: 1

Related Questions