Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,161
    Thanks
    19
    Thanked 99 Times in 88 Posts
    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?
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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]
    Attached Images Attached Images

  5. #5
    5 Star Lounger RussB's Avatar
    Join Date
    Dec 2009
    Location
    Grand Rapids, Michigan
    Posts
    803
    Thanks
    10
    Thanked 50 Times in 49 Posts
    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.
    Do you "Believe"? Do you vote? Please Read:
    LEARN something today so you can TEACH something tomorrow.
    DETAIL in your question promotes DETAIL in my answer.
    Dominus Vobiscum <))>(

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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?

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    For display purposes only you can Custom Format to display leading zeros.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,161
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by RetiredGeek View Post
    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.

    Quote Originally Posted by Tim Sullivan View Post
    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.


    Quote Originally Posted by RetiredGeek View Post
    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.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •