Problems opening CSV Files

[gsmith-plm]

I've been doing this a very, very long time, and I believe the problem has pretty much always been around. But every once in a while it becomes a real PITA and I troll the information superhighway looking to see if an answer has been found.

I exchange a lot of CSV files with all kinds of people, both computer literate and computer ignorant. Sometimes the data includes account numbers that begin with a 0 and when the file is opened by Excel, it will change those accounts to number fields and strip off the leading 0.

Now, I know that I can make the field text and do a data import and make those fields Text, but when a CSV file with 50 columns is going to Bob "I don't know anything about computers and get confused real easy" Smith, that's not an acceptable answer because he's invariably going to complain, "This is too complicated. Why can't you send me one of those CSV files?"

So, here we are in the Century of the Fruit Bat and MS still doesn't seem to have a simple solution to a common problem. Is there any way to gimmick a CSV file so that Excel will open it without stripping out the leading zeros without messing the file up for import into something like SQL server?

[RetiredGeek]

Graham,

Where are the CSV files originating from? If you export the file with those fields as text they'll import as text.

I wouldn't lay this problem at MS's feet. This problem has it's roots in old manual systems that when computerized insisted on computerizing the solution exactly as handled manually {"That's the way we've always done it!"}. This problem should have been handled when the system was computerized and either handle the account numbers as Text or Numbers, problem solved. You make people adjust once and it's fixed for all time. I know this doesn't help you solve your problem but I've been fighting this problem my whole career, at least until I retired {Ho-rah for that.} and just need to vent...thanks for reading.

BTW: There was another thread in here where someone was using a # sign in a field name causing problems for CSV files and an ingenious solution was posted, you might take a look at that for a possible solution.

[AndrewKKWalker]

This is a pain in Excel.
One way round it (manual)
Is to Copy and Paste the cells from the CSV to the sheet
Then use the Text to Columns option on the Data Menu/Ribbon to specify that the Column is text.

An alternative would be to read the text file line by line and parse the text that way.

Neither is ideal, and it ought to be fixed
The same behaviour occurs in Excel 2003, 2007 and 2010

I would like someone to correct me on this though.

[tfspry]

We all have our Mr. Bob's. In those 50 column imports, I am guessing that very few of the Columns need to be converted as "text" due to the need to preserve the leading zero (0). For Bob "I don't know anything about computers and get confused real easy" Smith, try making easy to follow pics of the column(s) that need to be converted to Text.

[attachment=89512:Import text from a CSV file.jpg]

[RussB]

Maybe I am missing something, but I know that Excel used to have an option "Allow (or use) Leading Zeros" is this gone? I use OpenOffice now.

[rory]

I can't say I've ever seen such an option in Excel.

If these csv files are for import into SQL server, then where does Excel come into it?

[RetiredGeek]

For display purposes only you can Custom Format to display leading zeros.

[gsmith-plm]

If you export the file with those fields as text they'll import as text.

If you mean by putting quotes around it, Excel ignores them when opening the file.

The data files are generated by one database and are sent to a number of different places, some places import it directly into a database, some open it with Excel and work with it like that, some open it with Excel to "check it" then import it (don't ask).

I recall a trick that involves appending a character to the beginning of the field, but that will screw up the data if it's imported into a database.

try making easy to follow pics of the column(s) that need to be converted to Text.

Tried that. Created a nice little instruction sheet. The general response was the proverbial blank stare followed by, "Why don't you just fix the file you're sending me so that it works." Unfortunately, I have no control or influence over the people at the other end of the data.

For display purposes only you can Custom Format to display leading zeros.

You know that. I know that. But to the people receiving the file, this is just another "the file is wrong" problem that they should not be required to have to deal with. I'm serious when I say that there are people out there using computers in their jobs on a regular basis that would freeze up like a deer in the headlights if you even suggest they try something new. There is also a problem that some of the people on the other end are in non-English speaking countries.

Part of the problem is that the files are generated and distributed automatically. They have to go out as a CSV file because there are databases that are set up to automatically import them that way. If I could, I would change the format to [Tab] delimited TXT files which would force people to use the data wizard to import them, but that's not an option.

[RetiredGeek]

Graham,

It seems like you have a major software problem...that's carbon based software!

The only possible solution I see to your problem would be to send out 2 files {.csv & .xls} and hopefully your 21st Century challenged users can at least figure out which file they need to use.

Good Luck, sounds like you'll need it.