topic Re: How to upload multiple Excel files in QlikView

How to upload multiple Excel files

Thu, 27 Mar 2014 15:22:28 GMT

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

Re: How to upload multiple files

Thu, 27 Mar 2014 15:26:39 GMT

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

Re: How to upload multiple Excel files

MK_QSL — Thu, 27 Mar 2014 15:31:44 GMT

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

Re: How to upload multiple Excel files

Anonymous — Thu, 27 Mar 2014 15:39:17 GMT

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

Re: How to upload multiple Excel files

Thu, 27 Mar 2014 16:04:04 GMT

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...

Re: How to upload multiple Excel files

aveeeeeee7en — Thu, 27 Mar 2014 16:26:40 GMT

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

Re: How to upload multiple Excel files

arsal_90 — Thu, 27 Mar 2014 16:31:52 GMT

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

Re: How to upload multiple Excel files

Colin-Albert — Thu, 27 Mar 2014 16:42:22 GMT

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.

Re: How to upload multiple Excel files

its_anandrjs — Thu, 27 Mar 2014 17:28:44 GMT

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

Re: How to upload multiple Excel files

Anonymous — Thu, 27 Mar 2014 17:53:40 GMT

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.

Re: How to upload multiple Excel files

Thu, 27 Mar 2014 20:42:54 GMT

Hi,

Thanks for all the responses. After using Concatenate command after each LOAD, the file gets uploaded into one table. But now QlikView treats each record from each spreadsheet separately. As a result, I cannot perform mathematical operation between two columns. I wanted QlikView to "append" columns from row number 1 (based on common column field name) from the first spreadsheet with the corresponding row number (based on common column field name) from the second spredsheet.

ExcelFileName1

ScannerID CheckIn DateAndTime

Scanner A January 1, 2014, 08:00:00 AM

Scanner B January 1, 2014, 08:00:00 AM

ExcelFileName2

ScannerID CheckOut DateAndTime

Scanner A January 1, 2014, 08:30:00 AM

Scanner C January 1, 2014, 08:45:00 AM

Ideally, QlikView must display

ScannerID CheckIn DateAndTime CheckOut DatAndTime

Scanner A January1, 2014, 08:00:00 AM January1, 2014, 08:30:00 AM

Scanner B January1, 2014, 08:00:00 AM ------

Scanner C ------ January1, 2014, 08:45:00 AM

With Concatenate command

ScannerID CheckIn DateAndTime CheckOut DatAndTime

Scanner A January1, 2014, 08:00:00 AM -----------

Scanner A ------------ January1, 2014, 08:30:00 AM

Scanner B January1, 2014, 08:00:00 AM ------

Scanner C ------ January1, 2014, 08:45:00 AM

I want to perform CheckOut DateAndTime minus CheckIn Date and Time. QlikView cannot perform the subtraction command.

Re: How to upload multiple Excel files

its_anandrjs — Thu, 27 Mar 2014 20:53:53 GMT

Hi,

In place of concatenate if you Join this tables then you get below table based on your ScannerID you get single row with in and out date

ScannerID CheckIn DateAndTime CheckOut DatAndTime

Scanner A January1, 2014, 08:00:00 AM January1, 2014, 08:30:00 AM

Scanner B January1, 2014, 08:00:00 AM ------

Scanner C ------ January1, 2014, 08:45:00 AM

then in front end or in load script you subtract this two dates like

(CheckOut DatAndTime - CheckIn DateAndTime) as DateDifference

Hope this helps

Thanks & Regards

Re: How to upload multiple Excel files

aveeeeeee7en — Thu, 27 Mar 2014 20:58:44 GMT

There is an Alternative use Join in place of Concatenate

or,

You can use this 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.

Re: How to upload multiple Excel files

Fri, 28 Mar 2014 19:18:12 GMT

Thanks for your response.

Re: How to upload multiple Excel files

dseelam — Mon, 11 Jul 2016 15:53:55 GMT

Hi Avee,

I have a doubt about your approach in dealing above issue

In your

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.

If I add a new filed in one of the 24 excel files it is unable to find new filed

is there any solution or do I need to add the same filed in all other files ??

Re: How to upload multiple Excel files

dseelam — Mon, 11 Jul 2016 16:00:19 GMT

VAMSHI,

WHAT IF ONE OF THE FILE HAS AN EXTRA COLUMN DOES IT GONNA FIND THAT COLUMN "?>