topic Re: Importing multiple excel files in QlikView

Importing multiple excel files

dseelam — Wed, 13 Jul 2016 16:13:33 GMT

Hello All,

I have 3 excel files with 3 columns but one of them have one extra column & I can accomplish importing all 4 columns with script in qvw if sheet names are same but if sheet names are diff how to import all 4 columns ?

Thanks in advance

Re: Importing multiple excel files

Wed, 13 Jul 2016 16:24:52 GMT

Re: Importing multiple excel files

tamilarasu — Wed, 13 Jul 2016 17:14:58 GMT

Hi Dinesh,

Try this,

Sub ScanFolder(Root) 

For each FileExtension in 'xlsx' 

For each FoundFile in filelist( Root & '\*.' & FileExtension) 

ODBC CONNECT32 TO [Excel Files;DBQ=$(FoundFile)]; 
Temp: 
LOAD *; 
SQLtables; 
DISCONNECT; 

Data: 
Load '' as Temp AutoGenerate 0; 

FOR i = 0 TO NoOfRows('Temp')-1 

LET vSheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', i, 'Temp'), Chr(39)), Chr(36)); 

Concatenate(Data) 
LOAD  *,
      FileBaseName() AS FileName,  
      '$(vSheetName)' AS Sheet_name 
FROM [$(FoundFile)]
(ooxml, embedded labels, table is [$(vSheetName)]); 

NEXT i 

Drop Table Temp;

Next FoundFile 
Next FileExtension

end sub    

Call ScanFolder('C:\Users\Tamil\Desktop\New folder (3)') ; 


Drop Field Temp;

Re: Importing multiple excel files

dseelam — Wed, 13 Jul 2016 17:56:51 GMT

Nag,

It's Not working & I am not bringing data from SQL these are flat files in a local system

Re: Importing multiple excel files

tamilarasu — Thu, 14 Jul 2016 02:16:40 GMT

Hi Dinesh,

Did you try the code.? Can you post a screen shot of the error.? The above code is to load only flat files (Excel files) from your local system not from SQL database. We have to use ODBC connection to fetch all the sheet names into qlikview. So that we can loop through each sheets in a excel. I have attached a sample qvw file for your reference.

Re: Importing multiple excel files

dseelam — Thu, 14 Jul 2016 13:12:05 GMT

Nag,

I replaced your Script in my QVW and replaced folder path

& attached error above

Re: Importing multiple excel files

tamilarasu — Thu, 14 Jul 2016 13:23:31 GMT

Fine. I have corrected the code in above post. Please check the attachment.

Re: Importing multiple excel files

dseelam — Thu, 14 Jul 2016 17:51:12 GMT

Nag,

I think you forgot to change, I see same code when import and reload I got the same error mentioned earlier

Re: Importing multiple excel files

rwunderlich — Thu, 14 Jul 2016 23:13:11 GMT

If it's always the first sheet of each file, you can leave the table name off the load and the default will be the first sheet.

Data:

// Dummy load so we can use concatenate below

LOAD 0 as dummy AutoGenerate 0;

Concatenate (Data)

LOAD *

FROM

[foo\test*.xlsx]

(ooxml, embedded labels);

DROP FIELD dummy; // Drop dummy field

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Importing multiple excel files

vikramv — Fri, 15 Jul 2016 00:32:25 GMT

Error Part of the script:

=============

LOAD *

FROM $(File)

(ooxml, embedded labels, table is Sheet1);;

=============

Sheet name should also be variable with a loop for the sheets you are loading (Ex: Your test1 xlsx sheet contains "Sheet1" as "Apple" )

So you need to replace "Sheet1" with "apple" instead in above code.

Else Remove the "able is Sheet1" as Rob suggested,that would be a very simple solution

Re: Importing multiple excel files

dseelam — Mon, 18 Jul 2016 14:22:12 GMT

Thanks Rob!

This is what I am looking for you are a pro