![如何让 Excel 使用逗号和引号保存 .csv?](https://linux22.com/image/1465653/%E5%A6%82%E4%BD%95%E8%AE%A9%20Excel%20%E4%BD%BF%E7%94%A8%E9%80%97%E5%8F%B7%E5%92%8C%E5%BC%95%E5%8F%B7%E4%BF%9D%E5%AD%98%20.csv%EF%BC%9F.png)
我正在尝试将文件保存为 .csv,但是 Excel 未使用标准逗号分隔符和引号。以下是我想要的示例:
"0","70","0","4/29/2012 12:00","13311250""1","70","0","4/30/2012 12:00","13311250""2","70","0","5/1/2012 12:00","13311250"
Excel 实际给我的结果如下:
0 70 0 4/29/2012 12:00 13311250
1 70 0 4/30/2012 12:00 13311250
2 70 0 5/1/2012 12:00 13311250
那么到底是怎么回事,为什么我甚至没有得到任何引号?我遵循的过程是使用来自文本文件选项的数据从 .csv 导入文件(如代码片段 1 所示),我对其进行了修改,然后将其再次保存为 .csv,但我得到的文件却是以第二种方式格式化的。
答案1
以下网站显示了执行导出的 VB 宏代码 https://support.chartio.com/knowledgebase/exporting-csv-files-with-double-quotes-from-excel
在 Excel 中打开 CSV 文件 > 查找并替换所有双引号 (
"
)。按照此 Microsoft KB 文章中提供的说明进行操作。但是,不要使用 Microsoft KB 文章中提供的宏,而是使用下面的宏。
Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Long
Dim RowCount As Long
Dim MaxRow As Long
Dim MaxCol As Long
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()
' Turn error checking off.
On Error Resume Next
' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on.
On Error GoTo 0
MaxRow = ActiveSheet.UsedRange.Rows.Count
MaxCol = Selection.Columns.Count
MsgBox "Processing this many rows: " & MaxRow
MsgBox "Processing this many columns: " & MaxCol
' Loop for each row in selection.
For RowCount = 1 To MaxRow
' Loop for each column in selection.
For ColumnCount = 1 To MaxCol
' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = MaxCol Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount
' Close destination file.
Close #FileNum
End Sub
答案2
我用您的 .csv 内容创建了一个文本文件。然后我:
- 将 .txt 文件导入 Excel 并选择
Delimited
- 我勾选了
tab
“否”comma
- 我选择
General
不text
这是我的输出:
0,"70","0","4/29/2012 12:00","13311250""1","70","0","4/30/2012 12:00","13311250""2","70","0","5/1/2012 12:00","13311250"
每个程序/应用程序对逗号分隔的真正含义都有自己的解释。在我的 Excel 示例中,严格来说我没有使用,comma delimited
而是使用了tab delimited
。您也可以使用text delimited
,具体取决于您要完成的任务。
翻翻RFC4180,嵌入的双引号应该重复,并且字段必须用双引号分隔。
答案3
使用此脚本。
Excel 宏 Microsoft 以 Excel 中的宏形式提供对 Visual Basic 的访问,使我们能够执行 Excel 自身无法管理的操作。要创建 VB 宏,请打开 Visual Basic 编辑器 (Alt+F11),然后从菜单中选择“插入”>“模块”。这将打开一个新的模块代码窗口,您应该将其复制并粘贴到以下脚本中:
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")
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 Sub
它为我解决了同样的问题。我从一个应用程序中将 CSV 导出到 Excel,在编辑它时,我正在保存 CSV 文件。检查后,它们在逗号限制值周围没有引号,但此脚本会使用引号保存 CSV 文件,因此保存的文件可以在其他应用程序中使用。