vba csv文件中的Excel尾随逗号错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7079009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel trailing comma bug in csv files
提问by isJustMe
I have an XLS file creates a CSV file with a macro on Excel 2003, I have 40+ columns and the last 3 ones are optional, therefore there are a lot of empty values on the XLS, when I run the exporting subroutine it wont put a trailing comma on all of the rows, why ? Because : http://support.microsoft.com/kb/77295-.-
我有一个 XLS 文件，它在 Excel 2003 上创建了一个带有宏的 CSV 文件，我有 40 多列，最后 3 列是可选的，因此 XLS 上有很多空值，当我运行导出子程序时，它不会放所有行的尾随逗号，为什么？因为：http: //support.microsoft.com/kb/77295-.-
In Microsoft Office Excel, if you save a file in the text or in the CSV (comma separated value) format, Excel puts tabs or commas between each column of the worksheet. However, certain text files may be saved with a different number of tabs or commas in 16-row blocks.
在 Microsoft Office Excel 中，如果您以文本或 CSV（逗号分隔值）格式保存文件，Excel 会在工作表的每一列之间放置制表符或逗号。但是，某些文本文件可能会以不同数量的制表符或逗号保存在 16 行块中。
here is their suggested work around:
这是他们建议的解决方法：
To make sure that Excel saves tab or comma delimiters for all empty columns, verify that the last column in the file contains some data in at least every 16 rows throughout the file. If the blocks of rows do not contain data, add spaces or other characters in every 16 rows to the cells in the last column, or reorder the columns in the worksheet so that the last column on the worksheet always contains information.
要确保 Excel 为所有空列保存制表符或逗号分隔符，请验证文件中的最后一列是否在整个文件中至少每 16 行包含一些数据。如果行块不包含数据，请在每 16 行的最后一列单元格中添加空格或其他字符，或重新排列工作表中的列，以便工作表上的最后一列始终包含信息。
-.- way to go microsoft ! -.-
-.- 去微软的路！-.-
Ok so my main issue is that the generated file will be parsed by another program out of my scope which needs that specific format as it is right now I'm adding a blank every 16 row if the field is empty, this seems to do it but Data Processing Deparment is complaining about that white space... can you believe them!?
好的，所以我的主要问题是生成的文件将由我范围之外的另一个程序解析，该程序需要该特定格式，就像现在一样，如果该字段为空，我将每 16 行添加一个空白，这似乎可以做到但是数据处理部门抱怨那个空白......你能相信他们吗！？
Anyway I also tried to add a flag and remove it with the find function, but ofc when you save the file it will take away the delimiters again...
无论如何，我也尝试添加一个标志并使用 find 函数将其删除，但是当您保存文件时，它会再次删除分隔符...
thanks for reading my history ;p
感谢阅读我的历史；p
Any suggestions ?
有什么建议 ？
Edit: Unfortunately using Excel is must, the data is manually inputted through the excel sheet by different users, the vba codes does a lot more like template generation etc.. this is the only issue that I'm having and it is not feasible to change the whole process.
编辑：不幸的是，必须使用 Excel，数据是由不同的用户通过 Excel 表手动输入的，vba 代码更像是模板生成等。这是我遇到的唯一问题，这是不可行的改变整个过程。
采纳答案by Alex K.
Manual example;
手动示例；
Dim r As Range: Set r = Range("A1:C10") '// or ActiveSheet.UsedRange for everything
Dim buffer As String, delimiter As String, c As Range
Dim i As Long
For Each c In r
If (i Mod r.Columns.Count) = 0 Then
If (Len(buffer)) Then delimiter = vbCrLf
Else
delimiter = ","
End If
buffer = buffer & delimiter & """" & CStr(c.Value) & """" '//or c.text to preserve formatting
i = (i + 1)
Next
Open "c:\xxx.csv" For Output As #1
Print #1, buffer
Close #1
回答by bik128
Simplest way:
最简单的方法：
ActiveWorkbook.SaveAs "MyFileNameAndDir.csv", xlCSV, Local:=True
回答by Task
Suggestion 1) Stop using Excel to generate a CSV file.
建议 1) 停止使用 Excel 生成 CSV 文件。
What's your data source? Where does Excel get the data from? Perhaps you can do something with the original data source to generate a CSV instead of using Excel as the middleman. If you like really big hammers, Biztalk is the ultimate MS tool for data input/output manipulation. Few can pull that one out of their toolbox, but throwing together a custom c# program to generate a CSV file can be done in a couple hours.
你的数据来源是什么？Excel 从哪里获取数据？也许您可以对原始数据源做一些事情来生成 CSV，而不是使用 Excel 作为中间人。如果您喜欢真正的大锤子，Biztalk 是用于数据输入/输出操作的终极 MS 工具。很少有人可以从他们的工具箱中取出那个，但是可以在几个小时内完成一个自定义的 c# 程序来生成一个 CSV 文件。
In short: If at all possible, use a better tool!
简而言之：如果可能，请使用更好的工具！
回答by k.schroeder31
If you don't have commas in your data, it would be very easy to write a text reader that goes through and counts the number of columns in a line and just add commas to the end if there aren't enough. It's not ideal, but it's probably easier than writing a custom Excel to CSV converter.
如果您的数据中没有逗号，那么编写一个文本阅读器会很容易，它会遍历并计算一行中的列数，如果不够，只需在末尾添加逗号。这并不理想，但它可能比编写自定义 Excel 到 CSV 转换器更容易。
