0

I have an excel spreadsheet which contains addresses. I'm reading the data from the spreadsheet using OLEDB and storing it into a DataTable. I then loop through the DataTable and create my output, which is written to a .csv file. I'm doing all of this in C#.

Here's the problem: When I open the .csv file, any zip codes that were hyphenated have been replaced with 0's.

12345-1234 --> 0

So I want to correct this behavior so that the zip code appears as it should. If i have to chop off the digits after the hyphen, that would be fine. How can I ensure the zip code gets transferred?

Also, is there something I can do in code to maintain the leading 0's in a zip code? Zip codes like this:

01234 --> 1234

Is there something I can do in code to keep the leading 0?

MAW74656
  • 3,449
  • 21
  • 71
  • 118

3 Answers3

0

If the zip codes are formatted in the excel spreadsheet, you should read them in, store them in memory, and write them out all as strings. Do NOT convert them to integers.

  • I'm not converting them, each column's datatype is String. – MAW74656 Jan 31 '11 at 17:53
  • @MAW if the leading zero is being stripped you *are* converting them. If not explicitly, then implicitly, somewhere. The question is what code you're calling into that is converting the type, and what to do about it. –  Feb 01 '11 at 14:50
  • @Will- Can you suggest any points in my operation that may be problematic? Full code is long and impractical to post, but I'll try to strip out the irrelevancies and post. – MAW74656 Feb 01 '11 at 14:57
  • @MAW I'd suggest debugging every step along the way. Might be in the actual import from excel, depending on the library you're using. –  Feb 02 '11 at 11:09
  • Not using any library to do the import, just writing oledb code as select statements against the Excel sheet's "database". But I will try debugging everywhere. – MAW74656 Feb 07 '11 at 20:51
  • Problem is with the oledb driver. Its not returning these values. How can I correct? – MAW74656 Feb 24 '11 at 19:03
0

The problem is that CSV files have to be interpreted by Excel and its assuming that something that looks numeric should be treated according to the default numeric rules. The fixes, unfortunately, don't work for all possible cases. You can do one of the following options:

  1. Keep the CSV file as is and use the Data Import wizard in Excel
  2. Write a leading tick ' to the field
  3. Write the field as an equation ="01234"

If you're just using Excel to look at the data then the second or third option might work. If you need to use the data in additional formulas then you might want to go the first route. If you're inspecting the data in Excel but you're going to use it elsewhere I'd recommend going the first route, too.

Chris Haas
  • 53,986
  • 12
  • 141
  • 274
  • I'm using the data somewhere else. When I open the .csv, that filetype is associated to Excel, so it opens in Excel. So option 1 is possible, but is not any better than what I"m doing right now. 2 and 3 aren't possible because I'm not writing to Excel, but to a .csv. – MAW74656 Jan 31 '11 at 17:56
  • #2 and 3 are definitely possible but not what you're looking for. Those two options are ways that you can write your CSV file in a way to trick Excel into treating the field as text. But this would potentially break whatever other process reads the CSV file. Ultimately your problem is with Excel, everything else that you're doing is correct. Depending on your data size I'd recommend just using Notepad to inspect the CSV files. Not as pretty as Excel but it will give you a clearer look at your raw data. – Chris Haas Jan 31 '11 at 18:02
  • Leading zeros don't show up when viewing the .csv as a text file. Hyphenates look like ,00000, in the text file, which is bad. Problem is with Excel, ok. Is there anything I can do about it? – MAW74656 Jan 31 '11 at 18:11
0

I just tested your data and this method posted here worked.

i.e. the cell value kept its value "12345-1234", when put into DataSet. Also leading zeros are sent to the DataSet as well. (zip code of: 00601)

Community
  • 1
  • 1
Brian Wells
  • 1,572
  • 1
  • 14
  • 12