如何在将 Excel 工作表另存为 csv 文件时在每个字段中强制引用？

发布于 2024-09-16 09:14:27 字数 334 浏览 4 评论 0原文

这是我的情况。

data = "" & data & ""
xlsheet.Range(crange).Value = data
xlfile.SaveAs(FileName:=finalfilename, FileFormat:=6)

此代码将“放在数据周围，将数据放在范围内，并将工作簿（xlfile）和一个工作表（xlsheet）保存为csv文件。

如果我在记事本中打开csv文件，我会发现以下

1.quotes被删除< br> 2.删除数字中的前导 0

如何确保以编程方式保存文件时存在引号？提前致谢！

原文

Here is my situation.

data = "" & data & ""
xlsheet.Range(crange).Value = data
xlfile.SaveAs(FileName:=finalfilename, FileFormat:=6)

This code puts " around the data, puts the data in the range and saves the workbook(xlfile) with one worksheet(xlsheet) as a csv file.

If I open the csv file in notepad I find these following

1.quotes are removed
2.leading 0 from numbers are removed

How to ensure that quotes are present while saving the file programatically? Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放赐 2024-09-23 09:14:27

这一行没有做任何事情：

' Append empty strings on front and back (i.e., do nothing). '
data = "" & data & ""

试试这个：

' Append double quotes on front and back. '
data = """" & data & """"

这有帮助吗？

更新：我在这里可能有偏差，但我认为问题可能只是在于您对 Excel 在保存 CSV 文件时正在执行的操作没有足够的细粒度控制。我记得，默认情况下，它不会在值周围加上引号，除非需要它们来限定可能包含可能会弄乱 CSV 格式的数据的离散单元格。例如，它会在文本值包含逗号的单元格周围添加引号。

据推测，它还会将文本值中的单引号转换为 CSV 文件中的双引号，并用引号将包含引号的文本值括起来。

对我来说，解决这个问题最明显的方法就是自己将数据保存到磁盘。这样，您可以手动在每个单元格周围添加引号，而不必担心 voo doo Excel 在 SaveAs 调用上可能会自动执行的操作。

显然，这很烦人，因为它需要更多的工作。但这是我此刻唯一的想法。不管怎样，它不应该是那么多的工作。

This line is not doing anything:

' Append empty strings on front and back (i.e., do nothing). '
data = "" & data & ""

Try this:

' Append double quotes on front and back. '
data = """" & data & """"

Does that help?

UPDATE: I could be off-base here, but I think the problem may simply be that you don't have enough fine-grained control over what Excel is doing when it saves a CSV file. As I recall, by default it does not place quotes around values unless they are needed to qualify a discrete cell that may contain data that would otherwise mess up the CSV format. For example, it puts quotation marks around cells with text values that include commas.

Presumably it also converts single quotes in text values with double quotes in the CSV file and encloses text values containing quotation marks with, well, quotation marks.

The most obvious way of getting around this, to me, would be simply to save the data to disk yourself. This way you can manually put quotation marks around every cell and not worry about what voo doo Excel may be performing automatically on the SaveAs call.

Obviously, it's annoying because it's more work. But it's the only idea that's coming to my mind at the moment. Anyway, it shouldn't be that much work.

回复收藏 0 原文