how to Export excel to csv file with "|" delimted and utf-8 code

Question

I had to export excel file to csv file through vba script. csv file must be utf-8 code and delimted is "|"

I use SetLocaleInfo to set list separate as "|", but it only effect "xlcsv" not effect "xlunicodetext".

Private Function SetLocalSetting(LC_CONST As Long, strSetting As String) As Boolean

    SetLocaleInfo GetUserDefaultLCID(), LC_CONST, strSetting

 End Function
 SetLocalSetting LOCALE_SLIST, "|"

When to use "xlUnicodeText", code is UTF-8 ,but the list seperator still is "Tab"

.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlUnicodeText, Local:=True

When to use "xlCSV", the list seperator still is "|" what I need, but codepage is ANSI.

.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlcsv, Local:=True

How to export a csv file with "|" seperator and UTF-8 ?

Do you have to keep the separator as | is there any way it could be ,? just throwing an idea out there maybe you have to ref it in UTF-8 see http://www.fileformat.info/info/unicode/char/007c/index.htm — user3271518, Aug 08 '14 at 13:32
Maybe it's silly, but is it possible to set somehow the locale code page to [65001](http://msdn.microsoft.com/en-us/library/windows/desktop/dd317756%28v=vs.85%29.aspx) ? — , Aug 08 '14 at 14:27

score 2 · Answer 1 · answered Aug 08 '14 at 14:30

2

the best solution I have found is to use ADODB.Stream: http://msdn.microsoft.com/en-us/library/ms677486%28v=VS.85%29.aspx

Sub saveUnicodeCSV()
 Set oAdoS = CreateObject("ADODB.Stream")

 oAdoS.Charset = "UTF-8"
 oAdoS.Mode = 3
 oAdoS.Type = 2
 oAdoS.Open

 lRow = 1
 lCol = 1
 Do Until Sheets(1).Cells(lRow, lCol).Value = ""
  oAdoS.WriteText (Sheets(1).Cells(lRow, lCol).Text)
  lCol = lCol + 1
  Do Until Sheets(1).Cells(lRow, lCol).Value = ""
   oAdoS.WriteText ("|" & Sheets(1).Cells(lRow, lCol).Text)
   lCol = lCol + 1
  Loop
  oAdoS.WriteText (vbCrLf)
  lCol = 1
  lRow = lRow + 1
 Loop

 oAdoS.SaveToFile "test.csv", 2
 oAdoS.Close
 Set oAdoS = Nothing


End Sub

Greetings

Axel

answered Aug 08 '14 at 14:30

Axel Richter

56,077
6
60
87

thanks, I try to use "ADODB.Stream", it can be save as "UTF-8" , but the Chinese character are all garbage in this "UTF-8" file – maomifadacai Aug 11 '14 at 08:31
The FileFormat:=xlUnicodeText is *not* UTF-8. If FileFormat:=xlUnicodeText is OK for you, try oAdoS.Charset = "Unicode". – Axel Richter Aug 11 '14 at 09:14
This does not work with cells with character similar to the delimiter. – user462200 May 03 '17 at 12:12

how to Export excel to csv file with "|" delimted and utf-8 code

1 Answers1

Linked