ODBC Connection with Excel Files

[peschu123]

Hi,

I need to scan about 200 xlsx Files on a network share. I read the sheetnames and save them in a table for later steps in load process.

So I use ODBC CONNECT32 [Excel Files;DBQ=$(FoundFile)]. Besides a little trouble with 32/64 bit it works in general.

Are there any resources for more informations? The following problems occur:

-     32/64 Bit - Depending on the office installation of the pc I run the script,  I have to change the "ODBC CONNECT" String...

     Is there a function which can handle this for me or do I need too build some "errorhandling" manually?

-     1 File won't load with an unspecific ODBC Error Message, I think this happens because this file is opened by somebody else(I get            the message when I open the file manually). All other files load correct. If I copy the files locally everything works fine too. Even if I          open a file it has no influence on ODBC read at all. 

-    ODBC Error interrupts script run even with "SET ErrorMode = 0;". Is it possible to avoid this? Instead I want the script to finish      and      concat() the filenames in a variable...

-    I tried a connection string like:

     [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$(File);Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";]

     But it doesn't seem to work. Script won't run or runs just to first execution of this statement and exits without any Errormessage.

In general everything works ok for development...but is not "(end) user" safe.

I would appreciate if someone can help me out.

Best Regards,

Peter

[kji]

QlikView can read Excel files directly without the use of ODBC, so unless you need some feature only available in ODBC I would recommend that.

[peschu123]

Hi Johan,

thanks for your reply.

I'm not aware of another way to get all sheets of an Excel File.

In short:

The sheetnames I get here, will be used later to load the sheet data with normal biff load (ooxml).

For example if a sheet does not exist in an Excel file, QV would give you an error. Another usecase would be to work around mistakes in Sheetnames.

Best Regards,

Peter

[engishfaque]

Dear Peschu82,

Please use For Each loop for importing multiple xls files.

Kind regards,

Ishfaque Ahmed

[peschu123]

Hi Thank you for the reply,

I use for each loop, thats not the point of my question.

My Problems are a bit more specific related to odbc connection to excel...

[qv_jagan]

Hi Peschu82

If all the excel files contains same number of columns.

Try the following.

Load * From *.xls (biff, embedded labels, table is Sheet1$);

Regards,

Jagan. V

[jonathandienst]

Hi

What connection string are you using? I have used the following without too many problems:

ODBC CONNECT32 TO [Excel Files;DBQ=$(vReportDefinitions)];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

Where vReportDefinitions is a relative path to the xls file. If you on a 64 bit machine, this script will attempt to use the 32 bit driver. If you want it to use a 64 bit driver on a  64 bit machine, the just use CONNECT rather than CONNECT32. This form will use 64 bit driver on a 64 bit machine and the 32 bit driver on a 32 bit machine.

I dont know if this is helpful for you...

Jonathan

[peschu123]

Hi Jonathan,

I do it the same way. (pls attached example file)

Connection works only with "ODBC CONNECT32" or "ODBC CONNECT64". Which is not (yet) a big problem.

Here I have to use CONNECT32. I'm not 100% sure but I think it depends on the QV/office version used not on the machine(windows) version?

As I wrote earlier ...  "in general everything works ok" ... (people seem not to read this or I express myself not clearly)

Call me complicated or too much detail oriented, but I like things to work as smooth as possible. So if there are questions from target group, I want those questions to be content related not nerdy technical issues.

For example: Try to explain the issue about 32/64 bit to end users, it stops exactly at the point when you say something about "32/64bit" ---> ??? ..

Anyway the most annoying thing is the error interrups script execution even when ErrorMode = 0. Which is the opposite behavior to what is written in the qv manual?