1

I'm working on a .NET application which exports CSV files to open in Excel and I'm having a problem with preserving leading zeros when the file is opened in Excel. I've used the method mentioned at http://creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel

This works great until the user decides to save the CSV file within Excel. If the file is opened again in Excel then the leading zeros are lost.

Is there anything I can do when generating the CSV file to prevent this from happening.

Improve this question
2
  • Not to be pedantic, but don't let the users edit the file in Excel. If they must adjust data, they are going to have to use something like notepad. Commented Jul 8, 2011 at 16:23
  • 1
    Not really a realistic option especially for the average person. Commented Jul 8, 2011 at 19:44

4 Answers 4

Reset to default
4

This is not a CSV issue.

This is Excel loving to play with CSV files.

Change the extension to something else.

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

1 Comment

"Change the extension to something else." Indeed, this is the solution. Export your CSV data with file extension .txt or rename your .csv file. If you open the text file with Excel, you can use the function "Text to Columns". That feature allows you to declare a column as "Text" which preserves leading zeros or other number formats. After that, users should save their changed worksheet as .xlsx.
1

As @GSerg mentions, this is not a CSV issue.

If your users must edit/save in Excel they need to select the entire worksheet, right-click and choose "Format Cells" and from the Category list select "Text" after opening the csv file. This will preserve the leading zeros since the numbers will be treated as simple text.

Alternatively, you could use Open XML SDK 2.0, or some other Excel library, to create an xlsx file from your csv data and programmaticaly set the Cell type to Text in order to take the end users out of the equation...

Improve this answer

Comments

1

I found a nice way around this, if you add a space anywhere along the phone number, the cell is then not treated as number and is treated as a text cell in both Excel and Apple's iWork Numbers.

It's the only solution I've found so far that plays nice with Numbers.

Yes I realise the number then has a space, but this is easy to process out of large chunks of data, you just have to select a column and remove all spaces.

Also, if this is web related, most web type things are ok with users entering a space in the number field. E.g you can tap-to-call on mobiles.

The challenge is to get the space in there in the first place.

In use:

01202123456 = 1202123456

but

01202 123456 = 01202 123456

Improve this answer

1 Comment

Even better, and prevents ALL Excel reinterpretation (dates, formulas, etc) is to add a non-breaking-space "\xA0" as the first character of your field. (You'll see it as padding if your column is left-align, though). Tab also works for this, but pads on the right instead, and also screws up the cursor position when editing.
-1

Ok, new discovery.

Using Quick Preview on Mac to view a CSV file the telephone column will display perfectly, but opening the file fully with Numbers or Excel will ruin that column.

On some level Mac OS X is capable of handling that column correctly with no user meddling.

I am now working on the best/easiest way to make a website output a universally accepted CSV with telephone numbers preserved.

But maybe with that info someone else has an idea on how to make Numbers handle the file in the same way that Quick Preview does?

Improve this answer

Comments

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.