如何从csv导入长数字到excel，而不需要在VBA中转换成科学的表示法

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而不是那个数字。有什么问题吗?

3 个解决方案

#1

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)
以excel表格形式打开(如果不能打开，可以转换为excel表格)
highlight the (barcode/scientific notation) column
突出显示(条码/科学符号)列。
Go into Data / text to columns
进入数据/文本到列。
Page 1: Check 'Delimited' / Next
第1页:勾选“分隔”/ Next
Page 2: Check ' Tab' and change 'Text Qualifier' to " / Next
第2页:检查“标签”和更改“文本限定符”到“/下”。
Page 3: Check 'Text' rather than 'general'
第3页:检查“文本”而不是“一般”
Finish
完成

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

希望这有助于! !

#2

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位。您的示例号太大，无法准确表示，这可能解释了转换为科学符号的原因。

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”paramter我觉得有点不对劲:你应该有3列，但你已经把col2标记为文本……

#3

My system is mac os X EI 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 EI Capitan，当我从html表格拷贝数据到Excel时，数字总是自动转换为1.10101E+17等，当我将“cells format”改为text时，数字是不正确的。

My resolve is :

我的决心是:

open the Mac os software "Numbers" and create a new tab
打开Mac os软件“数字”并创建一个新的选项卡
copy data to tab, you will see correct number
将数据复制到标签页，你会看到正确的数字。
open Microsoft excel change number columns "cells format" to text
打开Microsoft excel，将数字列“单元格格式”更改为文本
copy data from Numbers tab;
从“号码”选项卡复制数据;
right-click "Optional paste" select Text and ok.
右键单击“可选粘贴”，选择“文本”，然后单击“确定”。

I don't known why but It's work for me.

我不知道为什么，但这对我来说是可行的。

#1