topic Loading Excel files in QlikView

Loading Excel files

qw_johan — Mon, 28 Mar 2011 16:06:29 GMT

Hi!

I am loading multiple excel files from a specific folder using a for each loop. This works fine.
My problem is that the structure of some excel files isn't what I am expecting it to be (there are two different versions / structures). The number of columns change from 7 to 10 and the fieldnames (column lables) are different. I know I could manually edit the files but i wish to avoid that.
How do I deal with this situation? Can qlikview help me?

I read about SQLColumns and with that I should be able to count the number of columns in an excel file before it is loaded, but I can't get it to work. My odbc connection keeps failing, Message...SQL##f - SqlState: 42000, ErrorCode: 4294965487 ..

ODBC CONNECT TO [Excel Files;DBQ='C:\Lists\9786438.xls];
columns:
SQLColumns;

DISCONNECT;

Is there other techniques or ways to get around the problem with loading multiple excelfiles into Qlikview when u have files with different number of columns and/or different column names ?

I would really appreciate some help on this.
I attached an example qvw file of how I load excel files.

Thanks

Loading Excel files

prieper — Mon, 28 Mar 2011 19:20:49 GMT

The script should work - have you checked to load the file without the SQLCOLUMNS? Probably you are missing some drivers during last installation?
Have tried it under vers 10 - works. You may then add an inspection, like IF NOOFROWS('Columns') = 7 THEN ... ELSE ... END IF

HTH
Peter

Loading Excel files

qw_johan — Wed, 30 Mar 2011 04:32:14 GMT

Hi,

Thanks for your reply. I got it to work now. I changed my connect string (also had to remove ODBC)...
CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source='$(vFile)';Extended Properties="Excel 8.0;"];

Thanks

Loading Excel files

qw_johan — Thu, 31 Mar 2011 18:59:48 GMT

Loading Excel files

qw_johan — Thu, 31 Mar 2011 19:01:11 GMT

Hi Peter,

It doesn't work. I thought it did work but it doesn't.
Problem:If the excel documents that i am loading contains more than one sheet, and all sheets have some information, i get an error saying that it can't find the fields that i want to load. If the excel documents only has one sheet, one sheet only, it works ok. Why is this? I attach my qvw if it helps.
I only want to load the first sheet, always the first sheet

Thanks