excel changed the precision of my data in .csv

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have some data in .csv format and I would like to open it, do some modifications and resave it as .csv file. But I found that when I saved the file in excel as a .csv file, the precision of some data will change even if I did no modification to my file. e.g. one number 4445409800000 was originally shown in the table as 4.45E+12 and in the formula bar as 4445409800000 (which I suppose it means that the data is stored as 4445409800000). After I save the file with excel in .csv format and reopen it in excel, the number would be rounded to 4450000000000

I know that if I use notepad to open and save the csv file, the precision will not be changed. But it is a large data file and I need to do some modification such as changing the order of the columns, which is impossible to be done in notepad

I also tried setting the cell format to text and then save the file. It didn't solve the problem

Any suggestions? Thanks
 
Try changing the extension from .csv to .txt, then open and import using the
Text Import Wizard, specifying that column of data as text within the wizard.
Make your changes, save the file as a .txt, and then when done, change the
extension back.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



xyy said:
Hi. I have some data in .csv format and I would like to open it, do some
Click to expand...
modifications and resave it as .csv file. But I found that when I saved the
file in excel as a .csv file, the precision of some data will change even if I
did no modification to my file. e.g. one number 4445409800000 was originally
shown in the table as 4.45E+12 and in the formula bar as 4445409800000 (which I
suppose it means that the data is stored as 4445409800000). After I save the
file with excel in .csv format and reopen it in excel, the number would be
rounded to 4450000000000.
I know that if I use notepad to open and save the csv file, the precision will
Click to expand...
not be changed. But it is a large data file and I need to do some modification
such as changing the order of the columns, which is impossible to be done in
notepad.
 
Ken,

Thanks. There are several text formats available in
excel's "save as". e.g. unicode text, text(tab
delimited), text(ms-dos).

I tried both unicode text and text(tab delimited). It
seems to me that the unicode text format is the one I
should select.

Any suggestions?

Again, many thanks.

-----Original Message-----
Try changing the extension from .csv to .txt, then open and import using the
Text Import Wizard, specifying that column of data as text within the wizard.
Make your changes, save the file as a .txt, and then when done, change the
extension back.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------- -------------------
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------- -------------------




(e-mail address removed)... to open it, do some
modifications and resave it as .csv file. But I found that when I saved the
file in excel as a .csv file, the precision of some data will change even if I
did no modification to my file. e.g. one number 4445409800000 was originally
shown in the table as 4.45E+12 and in the formula bar as 4445409800000 (which I
suppose it means that the data is stored as
Click to expand...
4445409800000). After I save the
 
Back
Top