Excel multi load

[#1]

Hi,

I am loding data from excel month wise and i have month wise excel file with same column on that.

Qualify *;

Table1:

load

Field1,

Field2,

Field3

...

Field7

From January.xls

load

Field1,

Field2,

Field3

...

Field7

From February.xls

upto december and i want to create a single table from this 12 excel file but when i load then 12 single file is created.

Thanks

[Anonymous]

Something along these lines should work. Skip the Qualify part as well:

FOR Each File in filelist ('Folder\*.xls')

LOAD

'$(File)' as Name,

Field1,

Field2,

Field3,

...

Field7

From '$(File)'.xls

NEXT File

[#3]

Hi,

Thanks i provide you 3 sample excel file like wise i have 12 excel files how to make a single file with this 12 excel files.

Thanks,

[#4]

Hi,

Please help me for above files its urgent.

Thanks,

[Anonymous]

Here you go.

1. Put the three Excel files in a folder.

2. Create a new QlikView file in the same folder.

3. Paste this script in the new QlikView file and reload:

FOR Each File in filelist ('*.xls')

LOAD

'$(File)' as Name,

[Txn Date],

     [Value Date],

     Description,

     [Ref No.],

     Debit,

     Credit,

     Balance

FROM

[$(File)]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

NEXT File

[#6]

Hi,

Since all your files seem to have same column names and similar type of data, to make them a single excal file using QlikView you can do this:

1. Load all the twelve excel files into a QVW.

2. Since all the column names are same, these will automatically concatenate to form one single table for all 12 files. (Optionally, you could use the concatenate keyword after each file load.

3. Now add all these columns into a table box and export it to Excel.

This way you will have a table box (similar to your data in Excel) in QlikView which has data for all 12 months in one place.

All_Months_Data:

//Loading January Excel file

Load

[Txn Date],

[Value Date]

Description

Ref No.

Debit

Credit

Balance

from ExcelFileForJanuary;

concatenate

//Loading February Excel file

Load

[Txn Date],

[Value Date]

Description

Ref No.

Debit

Credit

Balance

from ExcelFileForFebruary;

concatenate

//Loading March Excel file

Load

[Txn Date],

[Value Date]

Description

Ref No.

Debit

Credit

Balance

from ExcelFileForMarch;

.

...

......

and so on for all the months (as long as they have same column names).

Hope that helps.

Regards,

-Khaled.

[#7]

Hi,

But qualify *; syntax is put in the above of all this load because i load this table in dependently reason there is another single table with same fields but i dont want to connect with this table.

Thanks

[#8]

Hi,

Your script runs perfectly but there is Qualify *; is written above the for each file thats why it created three tables

Qualify *;

FOR Each File in filelist ('*.xls')

LOAD

'$(File)' as Name,

[Txn Date],

     [Value Date],

     Description,

     [Ref No.],

     Debit,

     Credit,

     Balance

FROM

[$(File)]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

NEXT File

Thanks

[Anonymous]

Remove the Qualify part. No point in having three separate tables when the data structure is the same and all that changes is the month

[#10]

Hi,

Johannes Sunden has a better solution for your previous question

Regarding the Qualify statement, you can do something like this:

First load the Excel files without the Qualify * statement, and then do a resident load of the resultant table (12 excel tables concatenated) and drop the original load. You could use a qualify keyword before Resident load to avoid this table from associating itself with other tables in the script.

I am not sure if this is the best solution, but it will certainly serve the purpose

AllTables_Old:

LOAD [Txn Date],

     [Value Date],

     Description,

     [Ref No.],

     Debit,

     Credit,

     Balance

FROM



(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);



Concatenate



LOAD [Txn Date],

     [Value Date],

     Description,

     [Ref No.],

     Debit,

     Credit,

     Balance

FROM



(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);



QUALIFY *;

New:

Load *

Resident AllTables_Old;

Drop Table AllTables_Old;

Hope that helps.

Regards,

-Khaled.