2

I have code that creates a csv mailing list for use in Excel 2013. My problem is that when the csv is clicked to open in Excel, the leading zero on zip codes is removed.

In older versions of Excel, you could add a single quote to denote that the cell should be treated as a string, but in the newer versions, the single quote is included. I've also tried double quotes and enclosing the zip code in single and double quotes with no luck.

I know that you can change the csv to a text file, and then manually import it into Excel, setting the zip column as a text column, but I would prefer make it easier on the end user.

Is there a way to import a number as a string from a csv in Excel 2013?

Psychemaster
  • 876
  • 10
  • 20
Tim
  • 2,731
  • 9
  • 35
  • 72
  • 1
    a csv file *is* a text file. as a number, `04441` is the same as `4441`. If you want excel to know about the leading zeds, you'll have to be sure they exist in the file. That means formatting it that way in the code you didnt show. – Ňɏssa Pøngjǣrdenlarp Jun 12 '15 at 15:24
  • 2
    There's a solution here http://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates – Ceres Jun 12 '15 at 15:31
  • @Ceres - I had to hunt for it as the =" and "="" didn't work. However, if I add a tab to the front of the number, then it is recognized as text without printing a visible character. Hopefully this won't screw up the spacing in the mail merge, but it gets me one step closer. – Tim Jun 12 '15 at 18:15
  • 1
    @rheitzman we have absolutely no idea if the OP is even putting the leading zero in the csv (no code, no sample). If the code creating the CSV is using a numeric, that could cause the loss of the leading zero. How they are imported depends entirely on the app importing. – Ňɏssa Pøngjǣrdenlarp Jun 12 '15 at 20:04
  • @Plutoix - sorry I deleted my comment... I tried the '0001 import trick in Excel 2010 and it failed to treat the data as a string so my first reaction was that I was wrong. But I'm pretty sure Excel 2010 did handle that case "properly." This leads me to believe that a lower level chunk of shared code has the bug. The bug being the change in how '0001 is handled on CSV open from Excel. – rheitzman Jun 12 '15 at 20:52
  • @rheitzman - The code is creating the csv with leading zeros; that was the first thing I checked. Most of the testing I have been doing is from manually changing the csv and then opening in Excel, figuring that once I found the solution, I could easily modify the code to mimic it. Since my question was about what character was needed to import a zip code as a string rather than a number into Excel, I figured that what was done in code didn't matter. The answer, as Ceres pointed out, was adding a tab to the end of the zip code. This caused Excel to import as a string rather than a number. – Tim Jun 12 '15 at 22:16
  • 1
    The trailing tab may be ok for your particular use but note that if the zip data is used it will still contain the tab. You might think about writing a "loader" template/macro that deals with the leading zero issues. You users would always run the same loader "program." All said and done the answer to your post is no if you do not use the text importer but depending on the default Excel handling of files with the extension .csv. – rheitzman Jun 15 '15 at 14:57

1 Answers1

2

EDIT: As stated in the comments above, there is also a solution on the exporting side.

Instead of just opening the .csv-file with excel, have you tried a import, using Data > Import external Data > From Text?

Data Import Ribbon

There you will have to option to select a dedicated text qualifier, that can be your single quote '

Data Import Options 1

Just make sure to choose Text as column type for the column, you need leading zeros in.

Data Import Options 2

This would be the result:

Data Import Result

Sorry for the german screenshots, but you live where you live. ;-)

Community
  • 1
  • 1
M463
  • 2,003
  • 3
  • 23
  • 39
  • "I know that you can change the csv to a text file, and then manually import it into Excel, setting the zip column as a text column, but I would prefer make it easier on the end user." - I think you missed that bit... – Psychemaster Jun 12 '15 at 15:21
  • I understood "when the csv is clicked to open in Excel" as an attempt to just open an .csv-file in Excel, what will lead to the described issue, if Excel is the default program associated with .csv-files. If the OP is looking for a programmatic way to transfer data into an Excel Worksheet via code, I would suggest to set the column/field datatype to "Text" before parsing the content. – M463 Jun 12 '15 at 15:28
  • I know this, and so does the questioner. The last part is more important - **I would prefer to make it easier on the end user** - they're looking for a way in their code to create CSV files so they don't have to do a clumsy import. – Psychemaster Jun 12 '15 at 15:30
  • Processing of the .csv-File is the responsibility of the processing program (in this case: Excel). I doubt that you can create a .csv in a way that will make excel think "Aha! A someway special .csv-file! I won't react to this one as I would normally do to .csv-files!", considering the fact that .csv-files are missing a header that could possibly tell the processing program how to do the parsing... Another solution would be to create a VBA-import-process as a plugin/macro for Excel, that would handle the specific logic needed to process your .csv-files. – M463 Jun 12 '15 at 15:36
  • 1
    I believe in past versions of Excel, you could add a ' to the beginning of a number, and it would import as text, dropping the single quote. I was simply hoping that there was something like that in the newer versions of Excel, which it sounds like there is not. – Tim Jun 12 '15 at 18:05
  • I think Microsoft introduce a bug in some shared code. 2010 no longer handles the '001 string properly. I am pretty sure 2013 handled in correctly as well once upon a time. I expect some of my users will be complaining soon about the disappearance of leading zeros. – rheitzman Jun 12 '15 at 20:56