CODEWITHSUNDEEP
excelvba
Zulu Z
Zulu Z

Reputation: 1121

How to import long number from csv to excel without converting to scientific notation in 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.

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?

Upvotes: 25

Views: 99080

Answers (9)

ruth zamora
ruth zamora

Reputation: 1

Use the Import and select Do not detect Type. otherwise your number will be truncated and filled with 0s when convert to text

Upvotes: 0

petra
petra

Reputation: 2792

Instead of opening the file directly in Excel, import it by using

Data --> From Text/CSV

and use the wizard to specify the type of the column as text.

In the Data Type Detection dropbox, select Do not detect data types

Upvotes: 10

user890332
user890332

Reputation: 1361

I have a solution that works for me with a text file tab delimited.

Open the text file in notepad etc..

Copy the text file with Ctrl-A and Ctrl-C then Ctrl-V into a blank sheet.

Note all the columns that look scientific.

Then Ctrl-Z (undo). You should now have a blank worksheet.

Highlight all columns (Ctrl-Click on each column header) that you noted before.

Change format to Text

Click on first cell and Ctrl-V again.

Upvotes: 1

Rick A
Rick A

Reputation: 21

Insert an apostrophe before the number. This forces Excel to display the whole number.

Upvotes: 2

sharath kumar
sharath kumar

Reputation: 27

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

Upvotes: 0

Steve Jiang
Steve Jiang

Reputation: 743

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.

My resolve is:

  1. open the Mac os software "Numbers" and create a new tab
  2. copy data to tab, you will see correct number
  3. open Microsoft excel change number columns "cells format" to text
  4. copy data from Numbers tab;
  5. right-click "Optional paste" select Text and ok.

I don't know why but it works for me.

Upvotes: 1

Y. Eman
Y. Eman

Reputation: 31

It looks like you are using a text file as an input. Steps:

  1. Go to cell A1
  2. File -> Open -> select the .txt file -> Open
  3. Text Import Wizard will open. Go through steps 1, and 2 according to your input file format. For step three, do this:
  4. 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.

You will be good to go.

Upvotes: 0

Finn2013
Finn2013

Reputation: 409

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

Upvotes: 40

Tim Williams
Tim Williams

Reputation: 166790

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

Upvotes: 3

Related Questions