java csv 不转科学计数,如何在不转换为VBA的科学计数法的情况下从csv导入长数字到excel...

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.

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"

And when I open newly saved file I see 2.117500012E+25 instead of that number.

what's wrong here?

解决方案

I was importing products into an excel file and the barcodes would come up as a scientific notation (eg 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.

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

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.

Hope this helps!!