cakester
asked on
Excel molests my data!
A quicky for you,
I open a CSV file in excel 2007, it has many lines of the following data, all exactly the same format:
8990,GBP/USD,2003-01-01 19:02:38.000,1.609300,1.60
8990,GBP/USD,2003-01-01 19:07:39.000,1.609200,1.60
I want to make a simple change to one of the columns (which i know how to do) and then save again. As expected, Excel comes up with that message saying there are incompatable characters in the CSV that excel dont like or whatever, its a common message. So as usual I click No to preserve my existing format. However, when I open my csv in a text viewer I still find it has removed things like - and chops 0's of the end of cells.
Does not matter if you click yes or no to that message, excel still messes around with data I have not touched.
example of its damage:
8990,GBP/USD,02:38.0,1.609
8990,GBP/USD,07:39.0,1.609
How do I stop excel changing anything unless I change it myself? A walkthrough instruction would be useful of how I can open, edit a coloumn, save, then close, without anything else being touched.
Thanks
C
I open a CSV file in excel 2007, it has many lines of the following data, all exactly the same format:
8990,GBP/USD,2003-01-01 19:02:38.000,1.609300,1.60
8990,GBP/USD,2003-01-01 19:07:39.000,1.609200,1.60
I want to make a simple change to one of the columns (which i know how to do) and then save again. As expected, Excel comes up with that message saying there are incompatable characters in the CSV that excel dont like or whatever, its a common message. So as usual I click No to preserve my existing format. However, when I open my csv in a text viewer I still find it has removed things like - and chops 0's of the end of cells.
Does not matter if you click yes or no to that message, excel still messes around with data I have not touched.
example of its damage:
8990,GBP/USD,02:38.0,1.609
8990,GBP/USD,07:39.0,1.609
How do I stop excel changing anything unless I change it myself? A walkthrough instruction would be useful of how I can open, edit a coloumn, save, then close, without anything else being touched.
Thanks
C
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another way to solve the problem is to open the file as you have been, make your changes, and then format the data as you want it appear in the exported file. When you save as a CSV the data will appear as you formatted it.
Use these custom formats:
Column C: yyyy-mm-dd hh:mm:ss.000
Columns D and E: 0.000000
Kevin
Use these custom formats:
Column C: yyyy-mm-dd hh:mm:ss.000
Columns D and E: 0.000000
Kevin
ASKER
dr_linux,
That worked well. I had to select save as and change to CSV then also I still had to click no to the warning message to avoid scramble but it did work.
Thanks
ASKER
yes most easy and almost perfect
Glad it worked out. There is also a way to change your data to be more CSV friendly. You can modify your data say 00009 to ="00009" excel will leave it alone. However this works best if you control the datasource and it will be used really only by excel. This is what I did in my case for my application addressing the same issue. But if anybody else uses the data or if you have no control over the datasource, that won't be a good option for you.
Make your changes and then save your file by choosing Save As and selecting CSV. Since all the values are stored as text the formatting will be preserved in the exported file.
Kevin