0

Possible Duplicate:
Excel CSV - Number cell format

I have written a C# .net windows application that writes data to a csv file, one of the fields is an invoice number which start with "00" i.e. 00144544. I have checked the csv and it is in the correct format.

Is there anything I can do either in my export or in excel to prevent this?

Community
  • 1
  • 1
CR41G14
  • 5,464
  • 5
  • 43
  • 64
  • Excel "helps" you format data when it opens a CSV file, such as detecting when you might be using a date. As long as the data is correct in the CSV file, do you actually need it viewable in Excel? – Sid Holland Jan 21 '13 at 16:21

5 Answers5

1

Wrap the value in quotes so excel recognizes it as a string. for example: 

"00144544"
Dave Hogan
  • 3,201
  • 6
  • 29
  • 54
  • The "cleanest" solution, as the CSV file is still readable. – Neil Knight Jan 21 '13 at 16:22
  • I agree but @Matt Burlands is good if you still want excel to treat the text as a number and not a string. – Dave Hogan Jan 21 '13 at 16:23
  • This doesn't work (at least in Excel 2007). The quotes are just used to allow commas to be embedded in the text but the value is still being interpreted as a number. – DocMax Jan 21 '13 at 16:26
  • @DaveHogan: Actually, I think Excel still treats it as a string. Your solution is probably more robust and better from a CSV "standards" (what there are of them) stand point. My solution works, in Excel, but I couldn't guarantee that it'll work anywhere else. – Matt Burland Jan 21 '13 at 16:26
  • hum... you're right. I just tested both double quotes and a single starting quote and they don't work in 2010? Have MS changed something? – Dave Hogan Jan 21 '13 at 16:30
  • @DaveHogan: Yeah, I just tried it and found the same thing. – Matt Burland Jan 21 '13 at 16:31
  • 3
    I just found http://stackoverflow.com/questions/137359 which shows how close you were: `="00144544"` will do it. – DocMax Jan 21 '13 at 16:34
  • the equals sign did it, thanks @DoMax – CR41G14 Jan 21 '13 at 16:42
1

The solution as @DocMax stated is to create your csv and put a "=" sign before your value so when writing a line you can do:

this._exportText.Append("=" + "\"" + (row.Index != null ? row.Index.ToString() : "").Trim() + "\",");

Without the "=" it doesn't work.

CR41G14
  • 5,464
  • 5
  • 43
  • 64
0

Excel is a pain for doing this. One way around it is to prefix your number with a '. This will make Excel treat it as a string instead of automatically converting it to a number and lopping off your leading zeros.

Matt Burland
  • 44,552
  • 18
  • 99
  • 171
  • When I tried this by creating a CSV file with `a,'00034` and opening it in Excel 2007, the `'` is shown in the column. – DocMax Jan 21 '13 at 16:28
  • @DocMax: Huh, I just tried it in 2010 and you are right. The `'` does appear. I thought an earlier version, at least, hid it. Maybe I'm misremembering. At least it does preserve the leading zeros. – Matt Burland Jan 21 '13 at 16:30
0

When you import the data into Excel, tell it (in the import wizard) to consider the column as text rather than auto-detecting the column type.

DocMax
  • 12,094
  • 7
  • 44
  • 44
  • 1
    @NeilKnight: The OP says C# is writing the CSV, not necessarily that it is being used to do the import. – DocMax Jan 21 '13 at 16:25
  • I am writing it to a csv so cannot set the column settings – CR41G14 Jan 21 '13 at 16:39
  • @CR41G14: My solution has to be done when you open the CSV in Excel (which is cleary sub-optimal). Check out the SO question I linked in a comment on your question for the best approach (which is very close to DaveHogan's). – DocMax Jan 21 '13 at 16:42
  • you comment was the solution therefore +1 – CR41G14 Jan 21 '13 at 16:43
0

You can put a ' character at the front or, more usefully, set the column number format to 00000000

To do that, select the column header (or cells), click on the number format dropdown, choose 'more number formats', then custom and then type in your format.

Tim Croydon
  • 1,866
  • 1
  • 19
  • 30