Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Excel multi load

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

11 Replies
jsn
Honored Contributor

Re: Excel multi load

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

Not applicable

Re: Excel multi load

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,

Not applicable

Re: Excel multi load

Hi,

Please help me for above files its urgent.

Thanks,

jsn
Honored Contributor

Re: Excel multi load

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

Not applicable

Re: Excel multi load

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.

Not applicable

Re: Excel multi load

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

Not applicable

Re: Excel multi load

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

jsn
Honored Contributor

Re: Excel multi load

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

Not applicable

Re: Excel multi load

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.