MySQL 导出到 csv/excel 方式以包含前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7070710/
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
Export to csv/excel way to include leading zero
提问by Sharpeye500
Exporting select query data from mysql to csv & opening with excel, the leading zeros are gone.
将选择查询数据从mysql导出到csv并用excel打开，前导零消失了。
Although the column is varchar, when the data gets exported, it writes like
虽然该列是 varchar，但当数据被导出时，它会写成
4567 instead of 04567,is there a way to preserve the leading zero while exporting to csv?
4567 instead of 04567,有没有办法在导出到 csv 时保留前导零？
采纳答案by Paul
You need to wrap the number in double quotes like: ...,"04567",..., but then it will be interpreted as text in the spreadsheet so integer arithmetic will not work on it unless you do a conversion first (integers cannot begin with 0 in excel).
您需要将数字括在双引号中，例如: ...,"04567",...，但随后它将被解释为电子表格中的文本，因此除非您先进行转换（整数不能以 0 开头，否则整数运算将无法在电子表格中使用）。
回答by leoh
Place the data in between single quotes when you export the csv.
导出 csv 时将数据放在单引号之间。
i.e. 04567 will be '04567'
即 04567 将是 '04567'
回答by Magne
Your CSV file will have the leading zeroes. Just open it in a text editor and see.
您的 CSV 文件将包含前导零。只需在文本编辑器中打开它并查看。
The problem is that importing into Excel and Numbers (for Mac) will remove leading zeroes, and convert strings with numbers into number format. This is by intent, since a lot of input into excel comes from various sources, where numbers often are treated as text. And when you put numbers into excel you typically want to do something with them as numbers (ie. calculations) and not as text. So they have made it automatically handle the most common use case.
问题是导入 Excel 和 Numbers（对于 Mac）将删除前导零，并将带有数字的字符串转换为数字格式。这是有意为之，因为 excel 的大量输入来自各种来源，其中数字通常被视为文本。当您将数字放入 excel 时，您通常希望将它们作为数字（即计算）而不是文本进行处理。所以他们让它自动处理最常见的用例。
The only way to force excel to display numbers as text from the input, without the user having to do anything, is by outputting an excel file and specifying a "custom number format" in it. I recommend using a XLS export library for this, as it doesn't seem as simple as adding a line of config text into a CSV file.
强制 excel 将数字显示为来自输入的文本而无需用户执行任何操作的唯一方法是输出 excel 文件并在其中指定“自定义数字格式”。我建议为此使用 XLS 导出库，因为它看起来不像在 CSV 文件中添加一行配置文本那么简单。
回答by JonnyAppleseed
If you are using SSRS, go the the textbox you need to have leading zeros, and over in the text box pane, scroll down to format and enter zeroes for as many numbers will be in the number. For example, in a zip code, you would enter 00000 into the format box. I know this works for Excel 2013.
如果您使用的是 SSRS，请转到需要前导零的文本框，然后在文本框窗格中向下滚动以设置格式并输入零，以便数字中的数字尽可能多。例如，在邮政编码中，您可以在格式框中输入 00000。我知道这适用于 Excel 2013。
