Importing multiple excel files

[dseelam]

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

[#2]

[tamilarasu]

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;

[dseelam]

Nag,

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

[tamilarasu]

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.

[dseelam]

Nag,

I replaced your Script in my QVW and replaced folder path

&  attached error above

[tamilarasu]

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

[dseelam]

Nag,

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

[rwunderlich · Accepted solution]

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

[vikramv]

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