0

I'm importing a CSV file that contains a 20-or-so digit number (for example 12345678901234567890123). Since Excel only supports 15 digits in each cell, importing the data as number yelds something like 123456789012345000000, which is not correct. When importing as string it automatically takes the exponential form - something like 1.23456E+23, which is also not correct and hard (if not impossible) to transform to the original form. So far, I've tried wrapping the number in double quotes in the CSV file in conjunction with the xlTextQualifierDoubleQuote flag and using .NumberFormat ="0" but both solutions did not work. At this point, I'm so tired of Excel assuming the cell value type and I just want everything to be in text format. Is this somehow possible?

EDIT: I'm using [worksheet].QueryTables.Add to import the CSV (that's what the xlTextQualifierDoubleQuote flag is associated to)

Martin
  • 710
  • 2
  • 11
  • 18

4 Answers4

3

Edit:

Suggestion in comments that helped solve the problem was to use:

.TextFileColumnDataTypes = Array(xlTextFormat)

The usage can be found in the official documentation as @Martin linked in his comment.

Use .NumberFormat = "@" before putting the number in the cell.

This will stop Excel from making assumptions on the cell format.

Another options might be to pre/appends a non-numeric character in the value, the value will be recognized as text and not converted. A non-printing character would be good as it will not alter the displayed value. A space character " " ASCII code (9), appended as an example.

You can see more options or approx in this Stack overflow answer by @fr13d.

Miguel_Ryu
  • 1,390
  • 3
  • 18
  • 26
  • This somehow doesn't work when importing CSV files with .QueryTables.Add – Martin Feb 19 '18 at 14:59
  • 1
    @Martin, have you tried `.TextFileColumnDataTypes := xlTextFormat` with query? – Miguel_Ryu Feb 19 '18 at 15:21
  • Ok, that did the trick. The correct usage form is .TextFileColumnDataTypes = Array(xlTextFormat) as suggested by the [docs](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/querytable-textfilecolumndatatypes-property-excel). Thanks! – Martin Feb 19 '18 at 15:28
0

If you write the numbers in the CSV using this format, Excel will interpret them as text:

"=""12345678901234567890123""","=""12345678901234567890123"""

J_P
  • 761
  • 8
  • 17
  • This is not an option because then the value in the cell will be "="12345678901234567890123", which cannot be matched to 12345678901234567890123 by .find – Martin Feb 19 '18 at 14:58
0

if this is a one time action, you can rename the file from file.csv to file.txt. If you open the file.txt now via openfile-dialog, Excel starts with textconverting-assistent. There you can specify the format for each column.

Best regards

Denyo
  • 115
  • 1
  • 13
  • with this you can specify the format for the column while import via QueryTables ".TextFileColumnDataTypes = Array(xlTextFormat, COLUMN2,COLUMN3,COLUMN4, ...)" edit: Miguel_Ryu was faster :) – Denyo Feb 19 '18 at 15:23
0

Are you using a CSV which does not have the cell type embedded in it?

If so Excel will automatically interpret the cell as having a scientific number which is why you are seeing your numbers "12345678901234567890123" as an exponential number.

Create a new Excel workbook and format all your cells as text. Afterwards paste your data.

EDIT: If you have control over your data, apparently Excel will automatically detect the format of a CSV field unless the CSV column is in this format:

"=""Data Here"""

Victor York
  • 1,621
  • 4
  • 20
  • 55