csv文件中的Excel结尾逗号错误 [英] Excel trailing comma bug in csv files

问题描述

我有一个XLS文件在Excel 2003上创建一个带有宏的CSV文件，我有40多列，最后3个是可选的，因此在XLS上有很多空值，当我运行导出子程序它不会在所有的行，尾随逗号，为什么？因为： http://support.microsoft.com/kb/77295 -.-

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 -.-

在Microsoft Office Excel中，如果以文本或CSV（逗号分隔值）格式保存文件，Excel会在每个列。但是，某些文本文件可能保存为16行块中不同数量的制表符或逗号。

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.

这里是他们建议的工作：

here is their suggested work around:

要确保Excel为所有空列保存制表符或逗号分隔符，请验证文件中的最后一列是否至少在整个文件中的每16行中包含一些数据。如果行块不包含数据，则每隔16行向最后一列的单元格中添加空格或其他字符，或者重新排列工作表中的列，以使工作表中的最后一列始终包含信息。

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.

-.-方式去微软！ -.-

-.- way to go microsoft ! -.-

好的，所以我的主要问题是生成的文件将被我的范围内的另一个程序解析，需要该特定的格式，因为它是现在我'如果该字段为空，则每隔16行添加一个空白，这似乎是这样做的，但是数据处理Deparment抱怨那个空白的空间...你能相信他们吗？

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!?

无论如何我还试图添加一个标志，并删除它与find函数，但是当你保存文件它会再次带走分隔符...

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...

感谢阅读我的历史; p

thanks for reading my history ;p

任何建议？

编辑：不幸的是使用Excel是必须的，数据是不同用户通过excel表手动输入的，vba代码做的更像模板生成等。这是我唯一的问题，它是不可行的改变整个过程。

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.

推荐答案

手动示例;

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

这篇关于csv文件中的Excel结尾逗号错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！