How to upload multiple Excel files

[#1]

I am uploading 24 excel files. Each file has 4 or 5 columns and 540 rows. Each file is approximately 45 KB size. The computer has 8GB RAM memory. Each file has common column names so they are all linked to one another. I uploaded each file by clicking on the "Edit Script" icon -> click "Table Files" button", and Reloading files individually. After the 5th is loaded, QlikView takes 45 minutes to one hours to upload the 6th file. It displays a message "Executing Script". There are no calculation or expression in the script. The applications hangs up while uploading the 6th file. I tried clicking on "End" button and "Abort". No response. Do we need to free up any memory space? Do I need to type any special function or command in the script between individual file uploads. I do not believe QlikView will take so much time to upload small files. Can someone suggest or guide in what I am doing? I appreciate. Thanks

[#2]

It sounds like you could have some kind of loop in your data model.  I would suggest instead of just reloading, try clicking on the debug button (from the edit script screen) and tick limited load.  Then you can restrict the amount of rows to a small number.  Hopeful the result will allow you to complete a load and look at the table view.

Oli

[MK_QSL]

This problem could be due to Synthetic Key or Circular References being created during your script load.

[Anonymous]

May it be possibble that you read every file in a seperate load command and if yes, do you use the QUALIFY command? If not QlikView will automaticly create synthetic kes to get the "connection" between the different tables. And the more tables you have the more synthetic keys will be generated between all the loaded tables. You can use QUALIFY to set a prefix for each load. Or you can use a CONCATENATE to have all the results in one table (for that you can use a additional filed to know the source; for example: "LOAD ...., FileName() as SourceFile FROM <file>".

It's only an idea. Perhaps it would help.

Greetings

Peter

[#5]

Just to be sure - try loading your xls in loop and save as qvds & drop table.  And check if loading from qvds is a issue too...

[aveeeeeee7en · Accepted solution]

Hi Rao

Your system is Taking Time Because you have done joining of Tables based on Multiple Fields.

A Table has one Unique Key or there is concatenated Keys.

In your case, the a lot of Synthetic Keys have been Formed which is causing Problem.

See the Workaround:

First Approach:

If you have 24 files with different names than do concatenate and create Flag field in your Edit Script.

See the Script Below:

Excel File 1:

'1' AS Flag

Concatenate

Excel File 2:

'2' AS Flag

Concatenate

---

---

---

Concatenate

Excel File 24:

'24' AS Flag

After writing the script do the Reload your single table is generated.

Create Table Box in your Sheet and drag all fields in it.

Take Flag field in your List Box, you'll see the Flag field contains 1,2,....24 values.

When you click on certain value than that Excel Data is Filtered out in the Table Box.

Second Approach:

If you have 24 Excel Files named as Eg. Excelfile1, Excelfile2, Excelfile3,..........,Excelfile24

Than you can use Script as follows:

Load *,

From Excelfile*.xls

After doing Reload your single table is generated.

Regards

Aviral Nag

[arsal_90]

Hi

This problem is due to Synthetic Keys and Loop Creations.

Can u provide the structure of your 8 tables with 5 rows of data in each table so i can help u

Thanks

[Colin-Albert]

When loading data from multiple tables, it is much easier to add each table to your script one at a time.

Use the Debug Limited Load option to only load 100 or even 10 rows.

Resolve any issues with loops and synthetic keys before adding the next table to your script.

This is much easier than loading a bunch of tables in one go, and then trying to work out how to fix the QlikView data structure.

[its_anandrjs]

Hi,

Try to create a QVD of the excel file in before stage and use that QVDs in the place of direct use of excel and make the model. Also there may be synthetic tables is created or may be circular loop so before making perfect data model remove the synthetic keys and the circular loops and then use the application. To check your load yes load limited rows and check the circular loop and data models.

QVDs load is faster then any another loads.

Hope this helps

Thanks & Regards

[Anonymous]

If all the excels have same column names and same format (the header size etc) and same number of columns,

just use Load * from eacelpath\*.xlsx;

But before trying this, check the format of each individual excel by opening either qlikview or manual.