Get External Data glitch (XL2k sp3)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

Can you post a small but representative .csv file?

Reply | Quote

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**

Reply | Quote

I have no problems importing your empty file

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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!

Reply | Quote