☰

导出 CSV 文件，值用双引号括起来

tag-icon • tag-icon microsoft-excel csv office-for-mac
导出 CSV 文件，值用双引号括起来

如何从 Excel 2004（Mac）导出值和字段都用双引号（“）括起来的 CSV 文件？

例如

"Name","Telephone"
"John Doe","555-5009"

答案1

编辑：
我只花了将近两年的时间就回来编辑了这篇文章。这里有一个解决方法，希望可以将您的所有数据括在引号中并保存为 CSV。您需要将引用添加Microsoft Scripting Runtime到您的 VBA 项目中（在Tools>下References...）。

Sub addquotes()
Dim s As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant, out As String
Dim fso As FileSystemObject, ts As TextStream

Application.DisplayAlerts = False
Set s = ActiveSheet
Set tmpR = s.UsedRange

'Set format of all cells as Text.
tmpR.NumberFormat = "@"
tmpArray = tmpR.Value

For i = LBound(tmpArray, 1) To UBound(tmpArray, 1)
    For j = LBound(tmpArray, 2) To UBound(tmpArray, 2)
        If j = LBound(tmpArray, 2) Then out = out & Chr(34)
        out = out & tmpArray(i, j) & Chr(34)
        If j <> UBound(tmpArray, 2) Then
            out = out & "," & Chr(34)
        Else
            out = out & vbLf
        End If
    Next j
Next i
Set fso = New FileSystemObject
Set ts = fso.OpenTextFile("C:\Users\Editor 3\Desktop\yourcsv.csv", ForWriting, True)
ts.Write out
ts.Close
Application.DisplayAlerts = True
End Sub

上次失败的尝试（仅供参考；请不要使用此）：
这是一个宏，它将打开一个新工作簿并输入工作表中的所有数据，每个值都用引号括起来。然后，您可以将这个新工作簿保存为 .CSV，而不会干扰原始工作簿。即使您的原始数据包含公式，这也将有效。

Sub addquotes()

Dim s As Worksheet, newS As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant

Set s = ActiveSheet
Set tmpR = s.UsedRange

'Loads all data from sheet into array.
tmpArray = tmpR.Value

'Adds quotes around members of array if not blank.
For i = 1 To UBound(tmpArray, 1)
    For j = 1 To UBound(tmpArray, 2)
        If tmpArray(i, j) <> "" Then
            tmpArray(i, j) = """" & tmpArray(i, j) & """"
        End If
    Next j
Next i

'Open new workbook and enter transformed data.
Set NewBook = Workbooks.Add
Set newS = NewBook.Sheets(1)
newS.Range("A1").Resize(UBound(tmpArray, 1), UBound(tmpArray, 2)) = tmpArray

End Sub

相关内容