How to import long number from csv to excel without converting to scientific notation in VBA

Question

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?

Answer 1

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

Answer 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.

You should import that column into Excel as Text, not Number: then you won't lose any precision.

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