如何在不转换为 VBA 中的科学记数法的情况下将长数从 csv 导入到 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13732077/
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
How to import long number from csv to excel without converting to scientific notation in VBA
提问by Zulu Z
I opened semicolon delimited txt file with this code below and long account number showed up as scientific notation after saving to excel regardless of formatting to text that column.
我用下面的代码打开了以分号分隔的 txt 文件，在保存到 excel 后，长帐号显示为科学记数法，而不管该列的文本格式如何。
What did I do wrong here?
我在这里做错了什么？
Application.ScreenUpdating = False
Workbooks.OpenText fileName:=Filetxt, Origin:=xlWindows, StartRow _
:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, Local:=True, _
FieldInfo:=Array(Array(1, 4), Array(2, xlTextFormat)
'Save text file as csv file
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
wb.SaveAs fileName:=fileXls, FileFormat:=6, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
wb.Close savechanges:=True
Record in txt file looks like this: 2011-12-21;100,00;"21375000120000010020601764"
txt 文件中的记录如下所示：2011-12-21;100,00;"21375000120000010020601764"
And when I open newly saved file I see 2.117500012E+25 instead of that number. what's wrong here?
当我打开新保存的文件时，我看到的是 2.117500012E+25 而不是那个数字。这里有什么问题？
回答by Finn2013
I was importing products into an excel file and the barcodes would come up as a scientific notation (eg 5.4265E+12)
我将产品导入到 excel 文件中，条形码将作为科学记数法出现（例如 5.4265E+12）
This meant when I converted the file into a csv file to upload the details, the csv wasn't reading the barcodes properly and changing them to 52862300000 etc.
这意味着当我将文件转换为 csv 文件以上传详细信息时，csv 无法正确读取条形码并将其更改为 52862300000 等。
To combat it:
为了对抗它：
- Open as an excel sheet (or convert if you can't open as one)
- highlight the (barcode/scientific notation) column
- Go into Data / text to columns
- Page 1: Check 'Delimited' / Next
- Page 2: Check ' Tab' and change 'Text Qualifier' to " / Next
- Page 3: Check 'Text' rather than 'general'
- Finish
- 作为 Excel 工作表打开（如果无法打开，则进行转换）
- 突出显示（条形码/科学记数法）列
- 进入数据/文本到列
- 第 1 页：勾选“分隔”/下一个
- 第 2 页：选中“Tab”并将“文本限定符”更改为“/下一步”
- 第 3 页：选中“文本”而不是“一般”
- 结束
This should convert them all to display as the long number. You can then save it as a CSV file and the numbers won't be converted/formatted into scientific numbers.
这应该将它们全部转换为显示为长数字。然后，您可以将其保存为 CSV 文件，并且数字不会被转换/格式化为科学数字。
Hope this helps!!
希望这可以帮助！！
回答by Tim Williams
In Excel numbers are limited to 15 digits of precision. Your example number is too large for Excel to represent accurately - that may explain the conversion to scientific notation.
在 Excel 中，数字被限制为 15 位精度。您的示例数字太大，Excel 无法准确表示 - 这可能解释了转换为科学记数法的原因。
You should import that column into Excel as Text, not Number: then you won't lose any precision.
您应该将该列作为文本导入 Excel，而不是数字：这样您就不会失去任何精度。
EDIT: if you step through the "open from text" process while recording a macro you should get something like this:
编辑：如果您在录制宏时逐步执行“从文本打开”过程，您应该得到如下内容：
Workbooks.OpenText Filename:= Filetxt, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 5), Array(2, 1), _
Array(3, 2)), TrailingMinusNumbers:=True
There is a step in that process which allows you to select what type of data is in each column.
该过程中有一个步骤允许您选择每列中的数据类型。
Your "FieldInfo" paramter is a bit off I think: you should have 3 columns, but you've tagged col2 as text...
我认为您的“FieldInfo”参数有点偏离：您应该有 3 列，但您已将 col2 标记为文本...
回答by Steve Jiang
My system is mac os X El Capitan , when I copy data to Excel from html table, the number always auto convert to 1.10101E+17 etc. when I change "cells format" to text, the number is incorrect.
我的系统是 mac os X El Capitan ，当我将数据从 html 表复制到 Excel 时，数字总是自动转换为 1.10101E+17 等。当我将“单元格格式”更改为文本时，数字不正确。
My resolve is:
我的决心是：
- open the Mac os software "Numbers" and create a new tab
- copy data to tab, you will see correct number
- open Microsoft excel change number columns "cells format" to text
- copy data from Numbers tab;
- right-click "Optional paste" select Text and ok.
- 打开 Mac os 软件“Numbers”并创建一个新选项卡
- 将数据复制到选项卡，您将看到正确的数字
- 打开 Microsoft excel 将数字列“单元格格式”更改为文本
- 从数字选项卡复制数据；
- 右键单击“可选粘贴”选择文本并确定。
I don't know why but it works for me.
我不知道为什么，但它对我有用。
回答by sharath kumar
Select the cells where the foramt should be changed:
选择应更改格式的单元格：
'Example:
Columns("A:E").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
'The exponential or scientific notation will be converted
回答by Y. Eman
It looks like you are using a text file as an input. Steps:
看起来您正在使用文本文件作为输入。脚步：
- Go to cell A1
- File -> Open -> select the .txt file -> Open
- Text Import Wizard will open. Go through steps 1, and 2 according to your input file format. For step three, do this:
- Use the mouse to select the column that gives you the headache, long account numbers, in your case. Under 'Column data format' select the 'Text' radio button, then, click Finish.
- 转到单元格 A1
- 文件 -> 打开 -> 选择 .txt 文件 -> 打开
- 文本导入向导将打开。根据您的输入文件格式执行步骤 1 和 2。对于第三步，请执行以下操作：
- 在您的情况下，使用鼠标选择让您头疼、帐号长的列。在“列数据格式”下选择“文本”单选按钮，然后单击“完成”。
You will be good to go.
你会很高兴的。
