ADO connection to Excel

[AlexanderBB]

Yikes ! I've just found my ADO connection in Access is not getting all the data. Some fields are blank. After a bit of googling it looks like I might need a Schema.ini File.

Has anyone any knowledge of this? Or had a similar issue and fixed it. I have made a mockup demo of just the problem. It's the same in Access 97 and 2003.

I have added dummy text to Excels first 8 rows to 'lock' the datatype. The problen doesn't occur until line 58 when a column contains just a dash. Maybe a clue ?

Thanks, ABB

 

[AlexanderBB]

Thanks rorya, explains why one works (thank goodness!) and one doesn't.

I've got 4 sheets so it'd be 4 csv files. Dunno what's quicker or easier now... probably a macro to write the files and import code in Access.

I'm moving a LOT of data, and only 2 columns seems to have a problem.... maybe worth a verify routine for safety.

 

[Kreszch68]

did you try to set the regkey to 0(zero) yet?

 

[AlexanderBB]

Hi Kreszch68, rorya

No I didn't regedit - read somewhere the IMEX setting did that. Is it not so ?

Since then have been playing around with csv files. It took too long to write a text file of all the Excel cells and the csv save is much faster.

However reading it back in is not so good. I can get it done manually but with

DoCmd.TransferText acImportDelim, "", "ep333", "E:\Book1.csv", True, ""

there are errors. First it said there was no field called 'NoName' so I made one and then got 'Duplicate output destination 'NoName'.

I'm assuming the field names in Excel must be the same in Access. And I'm hoping the datatypes set in the table will accept/match the csv data correctly.

But I have some hidden columns in Excel with no data. Are there perhaps called No Name ? Can I exclude them from the import ? Do the fields all need to be in the same order?

Many thanks, appreciate any help. This may end up better than the ADO method.

Regards, ABB

 

[Kreszch68]

About the IMEX, this is a common misconception.

If you set IMEX=1, the JET engine will scan the number of rows set in the registry to determine if the row is 'mixed' and if so, it sets the value to the default, set under the registry, which is normally text.
But, if the first rows are for example number, even with IMEX=1, the returntype will be numeric, causing the problem you have with missing values from that field.
Setting the regkey to 0, will tell the JET enigine to scan all rows, and depending if you have HDR set to YES or NO, it will return the correct data type. HDR is an important issue here, because if you have headers, but set HDR=No, the JET enigine will return text as datatype (assuming the headers are text).
As said before, setting MAXSCANROWS=0 can give a slight performance issue, but we are not talking about hours to complete, you have to see this as a difference of about a few seconds. (maybe not even a second, depending on the number of rows).

 

[Colin Legg]

Setting IMEX=1 in the connection string ensures that the registry ImportMixedTypes setting is honoured. Note that you cannot override this registry setting within the connection string itself.

To allow for mixed data types, in the registry set ImportMixedTypes to Text.
To ensure every row is scanned, in the registry set TypeGuessRows to 0.

And then, in the connection string, yes, set IMEX=1.

 

[RoryA]

If you are going to use DoCmd, why not use DoCmd.Transferspreadsheet and skip the CSV step?

 

[Colin Legg]

As said before, setting MAXSCANROWS=0 can give a slight performance issue, but we are not talking about hours to complete, you have to see this as a difference of about a few seconds. (maybe not even a second, depending on the number of rows).

MAXSCANROWS is completely ignored when using ADO to query Excel files.

http://www.xtremevbtalk.com/showpost.php?p=1156340&postcount=12

 

[AlexanderBB]

Thank you both for setting me straight on IMEX and reg setting. Just changed and YES IT WORKS !

Sorry for boung a bit slow there, and the speed is no issue

Fabulous... but still keen to solve the csv issue too, just for a bit more knowledge.

Regards, ABB

 

[Colin Legg]

Thank you both for setting me straight on IMEX and reg setting. Just changed and YES IT WORKS !

Sorry for boung a bit slow there, and the speed is no issue

Fabulous... but still keen to solve the csv issue too, just for a bit more knowledge.

Regards, ABB

Great stuff! Yes, Rory's suggestion of using a csv with a schema is a very good one, so well worth completing. I don't want to step on any toes, so I'll leave you in Rory's capable hands.

 

[AlexanderBB]

Rorya, there *is* a DoCmd.Transferspreadsheet comamnd.... I never knew that. CAn you tall me more or suggest a good link ? Thanks .

Very odd... I read Colins link, all I can say is setting TypeGuessRows to 0 made a huge difference here... all the data that was blank are now the correct values.