topic Re: Excel multi load in QlikView

Excel multi load

Mon, 19 Nov 2012 05:04:56 GMT

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

Re: Excel multi load

Anonymous — Mon, 19 Nov 2012 05:20:19 GMT

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

Re: Excel multi load

Mon, 19 Nov 2012 06:09:13 GMT

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,

Re: Excel multi load

Mon, 19 Nov 2012 06:28:49 GMT

Hi,

Please help me for above files its urgent.

Thanks,

Re: Excel multi load

Anonymous — Mon, 19 Nov 2012 06:35:13 GMT

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

Re: Excel multi load

Mon, 19 Nov 2012 06:43:38 GMT

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.

Re: Excel multi load

Mon, 19 Nov 2012 06:50:40 GMT

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

Re: Excel multi load

Mon, 19 Nov 2012 06:52:52 GMT

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

Re: Excel multi load

Anonymous — Mon, 19 Nov 2012 06:57:58 GMT

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

Re: Excel multi load

Mon, 19 Nov 2012 07:02:13 GMT

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.

Re: Excel multi load

Mon, 19 Nov 2012 08:31:22 GMT

Hi,

You are great, But Qualify *; syntax is put in the above of all this load because i load this table independently reason there is another single table with same fields but i dont want to connect with this table. If i remove Qualify synthetic key table is created please help for this.

Thanks

Re: Excel multi load

Anonymous — Mon, 19 Nov 2012 08:36:05 GMT

Then just rename the fields. Example below with a few fields renamed:

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

LOAD

'$(File)' as Name,

[Txn Date] as TransactionDate,

[Value Date] as NewValueDate,

Description as SomeOtherDescription,

[Ref No.],

Debit,

Credit,

Balance

FROM

[$(File)]

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

NEXT File