Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dseelam
Creator II
Creator II

Importing multiple excel files

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

10 Replies
Not applicable

Capturar.PNG

tamilarasu
Champion
Champion

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
Creator II
Creator II
Author

Nag,

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

tamilarasu
Champion
Champion

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
Creator II
Creator II
Author

Nag,

I replaced your Script in my QVW and replaced folder path

&  attached error above

tamilarasu
Champion
Champion

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

dseelam
Creator II
Creator II
Author

Nag,

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III

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