Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save-As csv file problem (Excel 97 SR2)

    Excel 97 Compaq Evo W40000 with 512 RAM (running NT OS)

    A coworker ran into a problem. She needs an Excel File converted to a CSV file.
    The Excel file was created as an export from one of our systems. She needs the CSV file to upload into a different
    system. I was able to create the CSV using Access, but I am curious as to why Excel failed to do it.
    When we do the Save-As (CSV) in Excel, it works fine for the first 2080 rows, but from that point on, it loses the formating.
    The original Excel File is 13,138 rows long. I deleted rows from the bottom to make the file smaller. I am attaching a zipped version of
    the smaller Excel File and the CSV file it created, so you can check it out.

    There is no hurry, the work is done, but I am curious as to why Excel failed.

    Chuck Reimer
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    A bigger (Potential) problem I see is if you ever open the file in excel, it will insist on converting the TEXT string of numbers to VALUES so it only has the first 15 digits and loses the rest of the values!

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    Steve,

    I know. That has bothered me for some time. In trying to get the Save-As to work, I even converted the entries to pure text first, then did the Save-As but the result was the same.
    I have had trouble converting Excel files to CSV files for my imports, but for different reasons. That was how I knew to use Access and save the query as a CSV.
    Excel is Very powerful, but can also be Very frustrating.
    It certainly helped when I found this forum! I have learned more for reading this forum than all the Excel classes I have taken.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    I would be amazed if I have stumped the Lounge on this. grins

    chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    The same thing happens in XL2K. I think that this is a bug in XL. The only way that I see around this would be to write a VBA macro to write the file yourself. If you would like some help doing that, then I would need a little more information:

    1- Will there ever be any data in columns C:J or just the headers?

    2- If there could be data in those columns, would you describe each (i.e. numeric or text, and if numeric what format do you want for it).

    3- Do you want the "Account String" enclosed in quotes to avoid possible problems importing it?
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    Chuck

    OK - I'll bite. It certainly stumped me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Using XP I found that I could 'fix' it by adding a dummy comment field out to the right.
    Interestingly, if I then deleted that dummy field I'd just added it remained fixed <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I decided that your generating program had probably done something wierd with the internal file format so did a cut and paste of your A and B columns to a new sheet, added some more heading lines and retried. That one failed at about line 25 - a totally new place, but same failure.

    In another experiment I found that if a put a dummy comment into row 2080 and 2081 it then worked for the next twenty or so rows before failing.

    I don't think even caffiene will fix this one.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Save-As csv file problem (Excel 97 SR2)

    I used Excel2000 and before saving the file in CSv format I copied the account number column and pasted back as values. If you then open the saved csv file in say, Word, you will see the data is saved correctly (all 2,500 sample rows).
    The problem is in openig the csv file directly into Excel, which then interprets the account numbers as numeric.
    However, instead of opening directly you can use the menu option Data-Get External Data-Import Text file option.
    Change the Files of type dropdown to All Files (*.*) , then point and select the csv file, click the import button.
    In the text Import wizard, select the comma delimiter option in step 2.
    In step 3 of the text import wizard, highight the Account string column and select the Text option button.
    Click Finish, OK, and voilla - 2,500 rows of data as expected with Account numbers as before.

    zeddy

Posting Permissions

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