如何格式化CSV文件 [英] How to Format CSV File

问题描述

大家好！
有没有办法格式化CSV文件??.对于前. 021原始数据...在excel 21中查看...在记事本021中查看.我想要的是..在查看excel中，前导零将显示021和记事本021中的相同数据.是否存在用于格式化csv文件的函数或任何代码?

Hello guys!
Is there a way, that you can format CSV file???. For ex. 021 original data...viewing in excel 21 ...viewing in notepad 021 . I want is.. in viewing excel the leading zero will show 021 and same data in notepad 021 .Is there a function or any code to format csv file?

推荐答案

CSV文件无法格式化.如果可能的话，Microsoft将提供存储格式信息的选项.

我个人认为这很好. CSV文件可用于交换数据，而不用于格式化.不幸的是，一旦在计算机上安装了Office，Microsoft默认会在Excel中打开CSV文件.您不知道用户多久给我打一次电话，说日期值没有正确的日期格式，因为它是在Excel中打开的.

但是，尽管我本人不会使用它，但可能有一种解决方法.我更喜欢将它们用于预期目的.缺点是:
-不独立于语言
-CSV中的数据已发生很大变化
-保存会更改CSV

似乎可以在Excel解释为公式的CSV文件中提供文本.例如，以下CSV在我的荷兰语Excel版本中会显示001、002和003 (这显示了第一个问题，因为在您的英语版本中它不起作用).

CSV files can''t be formatted. If this was possible Microsoft would have supplied the option to store formatting information.

Personally I think this is good. CSV files are there to exchange data, not formatting. Unfortunately Microsoft opens CSV files by default in Excel as soon Office is installed on your computer. You don''t know how often users called me with a remark that date value didn''t have the proper date format because the opened it in Excel.

However there might a workaround, although I personnaly would not use it. I prefer using things for the purpose they are intended for. Disadvantages are:
- not language independent
- data in the CSV is changed quite a lot
- saving would change the CSV

It seems to be possible to supply text in your CSV file that Excel interprets as formulas. For instance the following CSV would show 001, 002 and 003 in my Dutch Excel version (which shows the first problem, because in your English version it won''t work).

"ID";"Test"
1;"=TEKST(1;""000"")"
2;"=TEKST(2;""000"")"
3;"=TEKST(3;""000"")"

但是，如果您非常确定所有用户都使用英文Excel，则可以使用以下命令(我假设英文版本中的默认分隔符是逗号-您可能需要尝试一些不同的字段和文本分隔符):

If however you are very sure that all your users use an English Excel, it might work to use the following (I assume the default seperator in the english version is a comma - you might need to try some different field and text seperators):

"ID";"Test"
1,"=TEXT(1,""000"")"
2,"=TEXT(2,""000"")"
3,"=TEXT(3,""000"")"

另一个问题是，只有对数据进行一些高级解析，才能将CSV读取为纯数据.

最后一个问题是，一旦在Excel中打开了CSV并再次保存了该CSV，该CSV就会更改为

The other problem would be that reading your CSV as plain data is only possible with some advanced parsing of the data.

A last problem would be that once the CSV is opened in Excel and it is being saved again, the CSV would be changed to

ID;Test
1;001
2;002
3;003

我诚实地说，您应该按预期使用CSV:传输数据.实际上，Excel是这里的问题. Excel将对CSV文件中的数据进行格式化.

有多种方法可以影响Excel内的格式.在Excel中，您可以通过菜单数据打开CSV.如果选择从文本(用于打开文本文件的按钮)，则向导将使您指示列是文本列.您可以记录一个宏，以查看其在VBA中的编码方式.使用此代码编写您的用户可以启动的宏，或使用其对VB.NET进行编程以控制Excel.当然，这仅在您使用代码控制CSV的打开时才有效.如果用户双击打开CSV文件，它将无法正常工作.

I my honest opinion you should use CSV as it is intended: to transfer data. Actually Excel is the problem here. Excel does the formating of the data in the CSV file.

There are ways to influence the formating within Excel. In Excel you could open a CSV through the menu Data. If you choose From Text (the button for opening textfiles), the wizard lets you indicate that a column is a text column. You could record a Macro to see how it is coded in VBA. Use this code to write a macro that your users could start or use it to program VB.NET to control Excel. Ofcourse this only works if you use code to control the opening of the CSV. It won''t work if the user opens the CSV file by double clicking.

这篇关于如何格式化CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！