Change csv export file enclosed-by sign

Excel另存为CSV (comma separator)格式时,不带字段的限定符(enclosed-by sign)。

因为magento import csv前特别提醒,限定符不得为空,所以我就想着怎么把Excel CSV指定限定符为双引号。可以左找右找Excel本身不能设置限定符(连修改默认的逗号分隔符都很麻烦,要在Excel以外,Windows Control Panel->Regional Setting那里修改),倒有人提供了一个宏代码,用起来也很方便。

将Excel文件导出为逗号分隔、双引号限定的CSV文件的宏代码如下:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *