topic Re: Add Multiple Excel Files in QlikView

Add Multiple Excel Files

Thu, 20 Oct 2016 15:32:09 GMT

How do I add multiple excel files. I first clicked File, New and then completed a wizard that added my first excel file in the Main tab. I clicked File, Edit Script, Table Files and completed the next wizard. I don't see two separate tables.

Re: Add Multiple Excel Files

el_aprendiz111 — Thu, 20 Oct 2016 19:18:10 GMT

Hi David

LET MODELED_Folder = 'C:\Excel_Varios_QV\';

For Each vFile in FileList('$(MODELED_Folder)*.xlsx')

ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

Tabla_Excel:

SQLTABLES;

DISCONNECT;

LET vRows = NoOfRows('Tabla_Excel');

FOR i = 0 TO $(vRows)-1;

LET vSheet = SubField(Peek('TABLE_NAME',i, 'Tabla_Excel'),'$',1);

[sumary]: LOAD

'$(vSheet)' AS [Tab Name],

'$(vFile)' AS Ruta_Archivo

From [$(vFile)]

(ooxml, embedded labels, table is '$(vSheet)');

Next;

DROP Table Tabla_Excel;

Next;

Re: Add Multiple Excel Files

Thu, 20 Oct 2016 20:22:34 GMT

I am a new qlikview user, so I tried the following script which I took directly from a qlikview tutorial. The script imports the first excel file, but doesn't seem to recognize the second excel file in the table viewer.

T1_Active:
LOAD [Entity Status],
[Policy Type],
[Profession Flag],
[Customer No],
[Account No],
[Original Eff Date],
[Policy Eff Date],
[Termin Date],
[Student Flag],
[Source Code],
[State Code],
[Base Premium]
FROM
Active.xlsx
(ooxml, embedded labels, table is Active);

T2_Term:
LOAD [Entity Status],
[Policy Type],
[Profession Flag],
[Customer No],
[Account No],
[Original Eff Date],
[Policy Eff Date],
[Termin Date],
[Student Flag],
[Source Code],
[State Code],
[Base Premium]
FROM
Term.xlsx
(ooxml, embedded labels, table is Term);

Re: Add Multiple Excel Files

el_aprendiz111 — Thu, 20 Oct 2016 22:25:11 GMT

David:

1 example

Directory;

LOAD * FROM

[..\..\..\..\..\..\Desktop\web_Qlik\t12.xlsx] //file path

(ooxml, embedded labels, table is Active);

LOAD * FROM

[..\..\..\..\..\..\Desktop\web_Qlik\t12.xlsx] //file path

(ooxml, embedded labels, table is Term);

Re: Add Multiple Excel Files

rwunderlich — Fri, 21 Oct 2016 00:50:35 GMT

What you are seeing is automatic concatenation. When LOAD has the same field list as an existing table, the rows are concatenated onto the existing table. The rows from both files are there, they are stored in T1_Active table.

Also note that if you want to load a group of files having the same fields, you can use "*.xlsx" as the filename in the LOAD statement.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Add Multiple Excel Files

Fri, 21 Oct 2016 13:02:23 GMT

Thanks so much, this resolved the issue !!