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.609800,2
8990,GBP/USD,2003-01-01 19:07:39.000,1.609200,1.609700,2

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:

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.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rename your CSV to .TXT   Then open excel by itself, then choose file->open and pick your text file.  It will then prompt you how you would like to import the data and by what it is delimited.  This is where you can set the formatting of the columns so that it won't mess up your data after you make your changes.  Make the columns as general or text format and your zeros will be preserved after making your changes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
Import the csv file into a new Excel workbook using Data->Import External Data->Import Data. Set the options for comma delimited. On the second dialog of the import wizard, select all fields (SHIFT+click) and choose Text as the type. Click Finish and then OK.

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.

zorvek (Kevin Jones)ConsultantCommented:
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

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

cakesterAuthor Commented:


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.

cakesterAuthor Commented:
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.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.