Load script for multiple tabs in single excel document

[mattphillip]

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

[mattphillip]

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]

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]

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]

Attached as requested.

[tamilarasu]

Hi Matt,

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

[mattphillip]

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 · Accepted solution]

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]

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]

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]

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.