3

Win XP, Excel 2007

I know there are various other posts on csv formatting but couldn't quite find what i needed.

Some of our data is held off site by another company and they send us a csv file every morning with the previous days data.

The problem is this data has come from web input forms that may have drop-down lists.

For example there may be a drop down list of Number of Employees with options like 1-10, 11-25, 26-50 etc

When we open the csv file in Excel certain options like 1-10 has been turned into Oct-01 date format which we do not want.

Is there an easy way to change these back OR reformat the cells and do a find...replace? (This didn't seem to work terribly well as it kept reverting back to the date)

Indeed is there a better way of opening the csv file to keep the formatting intact? and save us doing lots of find...replaces.

Ultimately we will need to open the csv in Excel though.

Grateful for any hints

Improve this question
1
  • Just to add a bit to this - if you install the excellent ASAP Utilities addon for Excel this has an import function built in which will preserve the original formatting. Have been using it for a while now and seems to work nicely. Commented Oct 30, 2013 at 15:08

1 Answer 1

Reset to default
3

Isn't that SO annoying? Here's how I deal with this issue:

When you open the CSV file in Excel, you should get a dialog with parsing options. First you select delimited or fixed then you get a screen that previews the data parsing.

It's easy to miss, but in the upper right corner of the dialog box there's an option to set a specific data format for each column. Select the column you want to protect and set the format to text. (This keeps Excel from dropping the leading zeros in ZIP codes for New England too!)

Once you get it into Excel, you can do a vlookup or replace to reset the values to your own codes.

Hope this helps. Good luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I would like to add that CSV files are not Excel files. CSV files are plain text files, and don't contain any formatting information. As a result, it is impossible to keep the formatting, unless by converting the plain text file to an Excel file. I've written an in depth article that touches upon this issue: theonemanitdepartment.wordpress.com/2014/12/15/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.