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: 
mattphillip
Creator II
Creator II

Load script for multiple tabs in single excel document

Hope someone can help with this.

I have very large data file to process shortly with coded field values. Accompanying this file is the field value definitions document. This document consists of a tab per field in a single excel document. My problem is that there are 200+ fields, hence 200+ tabs. I've seen some examples where there is a load script solution but these seem to be based on the same field names (each table has distinct field names). When I've tried replacing the field names with '*' it doesn't work either.

Can someone help? I really don't want to have to add 200+ pieces of load script...

Appreciated.

Matt

28 Replies
mattphillip
Creator II
Creator II
Author

Error 2.jpg

Right I've tried splitting them and it got through the first file which had 60 tables. Then it connected to the second table and I got this error.

Sorry for all the hassle this is causing.

mattphillip
Creator II
Creator II
Author

And here's the code:

FOR EACH file in FileList('V:\Qlikview\Purchased HESA dataset\New folder\Labels\Beta Files\*.xlsx');

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

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

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'Temp'), chr(39)), chr(36));

Table:

Load *,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

//Drop table tables;

Next

tamilarasu
Champion
Champion

Hi Matt,

Could you attach a sample file. It will be really helpful for us to provide you the solution quickly and also we can avoid back and forth replies.

mattphillip
Creator II
Creator II
Author

Attached as requested.

tamilarasu
Champion
Champion

Hi Matt,

What will be the output / expected table format. Do you want to concatenate all the tabs one below one?

mattphillip
Creator II
Creator II
Author

I'd like to load in all the tables. The data isn't linked so I don't want them combined into a single table. Hope this makes sense.

tamilarasu
Champion
Champion

If we load all the tabs as tables then we will end up creating 200+ tables. I think this is not a good idea. I have analysed your excel file and created a singe table but with all the required columns. I think this should link with your data. Note that I have faced some issues while loading your excel as it loads empty columns (AA, AB etc..) as well. So I have modified the script to load only the required columns. Kindly check and let me know your thoughts on this.

mattphillip
Creator II
Creator II
Author

That's awesome thank you! This is the kind of area I need to work on in the future. I can build reasonably complex dashboards but I still have a weakness when it comes to optimal table import format. Any suggestions on some good reads or articles which might help my accomplish this?

Thanks!

P.S. Did you amend the excel file itself or does the load script handle all the transformation steps?

tamilarasu
Champion
Champion

No Problem Matt .

Matt Phillip wrote:

This is the kind of area I need to work on in the future. I can build reasonably complex dashboards but I still have a weakness when it comes to optimal table import format. Any suggestions on some good reads or articles which might help my accomplish this?

You can learn everything by practicing. I would suggest you to check some answered questions in the community everyday and try to do it yourself. This will give you a good start and some idea. Next step is try to answer some scripting questions in the community like I did . Also there are lot of good people who can post some good stuffs. Please try to check their replies as well. That's more than enough.

Matt Phillip wrote:

P.S. Did you amend the excel file itself or does the load script handle all the transformation steps?

I did all the transformations in the load script itself. Kindly confirm whether you are loading data from single excel file or multiple excel files. Because the code that I have provided will load only data from a single file (I just commented the for loop). If you want to load from multiple files, kindly let me know. I will provide the code for that as well.

mattphillip
Creator II
Creator II
Author

On this occasion its a single file, but I'm anticipating having more than one file going forward. Could you send through example script which could cater for multiple files too?

Thanks for the tips! I'll definitely do that.