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]

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.

[its_anandrjs]

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

[aveeeeeee7en]

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.

[#5]

Thanks for your response.

[dseelam]

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

[dseelam]

VAMSHI,

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