Number in text format in CSV file
 

16 digit number formatted as text in a csv file is truncated when the csv
file is opened in Excel ... actually the 16th digit is changed to a 0. For
example, in the csv file, the text 1000000001423086 is shown as
1000000001423080 when the csv file is opened with Excel. Is there a setting
in Excel 2003 and/or 2007 that will prevent this from happening? This
specifically is invoice information generated by one system for loading into
another so it's really important for the invoice numbers to be correct!

Number in text format in CSV file
 

Nope.

Excel only keeps track of 15 significant digits.

Your choices are to bring the data in as Text (not general, not as a number).

You can use Data|Import External Data (xl2003 menus) and specify each field.

Or you can rename the .csv file as .txt and use file|Open and see a similar
wizard.

Excel "knows" how to treat .CSV file extensions--even if it's not what you want!


mewild58 wrote:

16 digit number formatted as text in a csv file is truncated when the csv
file is opened in Excel ... actually the 16th digit is changed to a 0. For
example, in the csv file, the text 1000000001423086 is shown as
1000000001423080 when the csv file is opened with Excel. Is there a setting
in Excel 2003 and/or 2007 that will prevent this from happening? This
specifically is invoice information generated by one system for loading into
another so it's really important for the invoice numbers to be correct!

--

Dave Peterson

Number in text format in CSV file
 

Thanks, Dave. I just saw that in another post about renaming the .csv file
as .txt and then specify the format of each column. This will actually solve
a few other issues that I'm having to manually correct each time I do the
upload. Thanks again.

"Dave Peterson" wrote:

Nope.

Excel only keeps track of 15 significant digits.

Your choices are to bring the data in as Text (not general, not as a number).

You can use Data|Import External Data (xl2003 menus) and specify each field.

Or you can rename the .csv file as .txt and use file|Open and see a similar
wizard.

Excel "knows" how to treat .CSV file extensions--even if it's not what you want!


mewild58 wrote:

16 digit number formatted as text in a csv file is truncated when the csv
file is opened in Excel ... actually the 16th digit is changed to a 0. For
example, in the csv file, the text 1000000001423086 is shown as
1000000001423080 when the csv file is opened with Excel. Is there a setting
in Excel 2003 and/or 2007 that will prevent this from happening? This
specifically is invoice information generated by one system for loading into
another so it's really important for the invoice numbers to be correct!

--

Dave Peterson

Number in text format in CSV file
 

Hi,

when you import a txt file the Import Wizard allows you to specify in step 3
that any specified column (field) should be treated as text. So to get this
to work don't copy the file, open the .csv from inside of Excel.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"mewild58" wrote:

16 digit number formatted as text in a csv file is truncated when the csv
file is opened in Excel ... actually the 16th digit is changed to a 0. For
example, in the csv file, the text 1000000001423086 is shown as
1000000001423080 when the csv file is opened with Excel. Is there a setting
in Excel 2003 and/or 2007 that will prevent this from happening? This
specifically is invoice information generated by one system for loading into
another so it's really important for the invoice numbers to be correct!

Number in text format in CSV file
 

Opening the .csv file in Excel is what got me into this. Saving or renaming
the file as .txt then opening it from within Excel and going through the
wizard will work beautifully.

"Shane Devenshire" wrote:

Hi,

when you import a txt file the Import Wizard allows you to specify in step 3
that any specified column (field) should be treated as text. So to get this
to work don't copy the file, open the .csv from inside of Excel.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"mewild58" wrote:

16 digit number formatted as text in a csv file is truncated when the csv
file is opened in Excel ... actually the 16th digit is changed to a 0. For
example, in the csv file, the text 1000000001423086 is shown as
1000000001423080 when the csv file is opened with Excel. Is there a setting
in Excel 2003 and/or 2007 that will prevent this from happening? This
specifically is invoice information generated by one system for loading into
another so it's really important for the invoice numbers to be correct!