How to import long number from csv to excel without converting to scientific notation in VBA
excel, vba
Solution
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!!
Problem
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?