Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get External Data glitch (XL2k sp3)

    I've used this trick before, and it works. Well, did...
    But on a recent attempt to pull in data from a .csv file (which itself can be opened ok in Excel, but I only want some records from some columns) it trips up with the message, "In the text file specification '[filename].csv', the Col1 option is invalid."
    As far as I know, I made no request for a Col1 option; the whole thing fails.
    Solution?

    Excel 2k fully service-packed and fails on both win2k and xp home
    Thanks
    John Rose, Accounts Clerk
    Handsworth Breakthrough Credit Union Limited

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    Can you provide some details?
    - Are you opening the file from the Data Get External Data | New Database Query menu option? If so, what details do you enter in the wizard?
    - Are you using code? If so, what code?
    - Are you using another method? If so, which?

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    Yes, Hans, I open a new workbook, click into A1 and fire up
    Data Get External Data | New Database Query
    I name a new data source in the first box and choose the Microsoft Text Driver (*.txt, *.csv) in the second box.
    I then navigate to the folder where the data source is, choose it and press the

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    Can you post a small but representative .csv file?

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    A bit sensitive, Hans, as full of personal data, so will the column labels which are the first record do?
    More if you want (or must!)
    Most columns of most records are populated: there are no commas in any cell to complicate things.
    [The list comes from Microsoft Works .wdb and some fields eg fullname and age come from formulas, but appear in the csv as values, and I've had no probs in the past.]

    HA-Ha - Lounge won't let me upload a csv so I'll have to zip it **grin**

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    I have no problems importing your empty file <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Can you replace the real data with dummy data, verify that the problem still occurs, then post the file (zipped)?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    Thanks, Hans, I think I may have found the glitch.
    On delving around I get the encouraging message -
    schema.ini is corrupt. Section cumemreg.csv, Key: col1
    I don't know what a non-corrupt schema.ini looks like. Here's mine. Strikes me that there's a lot of repetition (maybe one repeat per failed attempt?)
    Should I delete the whole thing and start again?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    Are you using Windows XP SP2 or Windows 2003? If so, check out FIX: You receive an "Ini File is corrupt" error message when you try to configure a Text DSN in MDAC 2.8 SP1.
    If not, we would really need to see (a sanitized version of) the data file.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    XP home sp2 on the box at home, Hans, Windows 2k at the day job, and the behaviour is identical with both. Here's a 3-record plus labels lump of the file. Got at.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    Does importing the attached file fail on your machine(s)? I have no problems with it.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    The three-record sample, Hans, is completely ok, the 400-record real one (different drive, different directory) results in the same error message as before. I was sort of expecting to see a 'schema.ini' created on the flash drive from which sample2 came: not there?
    Thanks v much for all your help so far!
    Am tempted to rename schema.ini to get it out of the action and see if it creates a new one?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get External Data glitch (XL2k sp3)

    I would certainly try renaming schema.ini, or moving it to another folder, to see if that helps.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get External Data glitch (XL2k sp3)

    Rubbed out the schema.ini thing [actually zipped it up to keep it from prying eyes of Microsoft] and all works ok, ie as it did before.
    Thank you VERY much Hans, sorry it was a bit of a false alarm, and glad I didn't need to get involved with the impenetrable stuff you found in the Knwledgebase.
    Thanks again!

Posting Permissions

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