如何将excel导出到具有“|”的csv文件分隔符和utf-8代码 [英] how to Export excel to csv file with "|" delimted and utf-8 code

问题描述

我不得不通过vba脚本将excel文件导出到csv文件。
csv文件必须是utf-8代码，并且分隔符为|

我使用SetLocaleInfo将列表单独设置为|，但只影响 xlcsv不影响xlunicodetext。

 私有函数SetLocalSetting（LC_CONST As Long，strSetting As String）As Boolean 
 
 SetLocaleInfo GetUserDefaultLCID（），LC_CONST，strSetting 
 
结束函数
 SetLocalSetting LOCALE_SLIST，| 
  

什么时候使用xlUnicodeText，代码是UTF-8，但列表分隔符仍然是Tab

  .SaveAs filename：=C：\temp\1.csv，FileFormat：= xlUnicodeText，Local： = True 
  

什么时候使用xlCSV，列表分隔符仍然是|我需要什么，但是代码页是ANSI。

  .SaveAs filename：=C：\temp\1.csv ，FileFormat：= xlcsv，Local：= True 
  

如何使用|导出csv文件分隔符和UTF-8？

解决方案

我发现最好的解决方案是使用ADODB.Stream：

  Sub saveUnicodeCSV（）
设置oAdoS = CreateObject （ADODB.Stream）
 
 oAdoS.Charset =UTF-8
 oAdoS.Mode = 3 
 oAdoS.Type = 2 
 oAdoS.Open 
 
 lRow = 1 
 lCol = 1 
直到表格（1）.Cells（lRow，lCol）.Value =
 oAdoS.WriteText（Sheets（1 ）.Cells（lRow，lCol）.Text）
 lCol = lCol + 1 
直到表格（1）.Cells（lRow，lCol）.Value =
 oAdoS.WriteText |& Sheets（1）.Cells（lRow，lCol）.Text）
 lCol = lCol + 1 
循环
 oAdoS.WriteText（vbCrLf）
 lCol = 1 
 lRow = lRow + 1 
循环
 
 oAdoS.SaveToFiletest.csv，2 
 oAdoS.Close 
设置oAdoS = Nothing 
 
 
 End Sub 
  

问候语

Axel

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 ?

解决方案

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

这篇关于如何将excel导出到具有“|”的csv文件分隔符和utf-8代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！