topic Re: Loading Multiple Excel files in QlikView

Loading Multiple Excel files

Tue, 25 Nov 2014 10:29:42 GMT

I have result set in Multiple Excels ,How Can i Load it in Qlik View

Load *

From

[Path\ * .xlsx]

is not working

Re: Loading Multiple Excel files

Tue, 25 Nov 2014 10:35:17 GMT

You can try to load one by one in qlik as usual : in qlik go to Edit script and then table files. and then you can choose the files one at time.

Re: Loading Multiple Excel files

its_anandrjs — Tue, 25 Nov 2014 10:45:59 GMT

Hi Shruti,

Try this ways

LET Path = 'E:\';

Load *, FileName() as FileName

From

[$(Path)\*.xlsx]

(ooxml, embedded labels, table is [$(*)]);

Regards,

Anand

Re: Loading Multiple Excel files

Anonymous — Tue, 25 Nov 2014 10:46:06 GMT

You can upload multiple excel files or multiple sheets for an excel files. Both is possible in the script.

Here you have an example for files '.qv'

DIRECTORY C:\Users\KonoGes\Documents;

Set Root='C:\Users\KonoGes\Documents';

for each Ext in 'qvw', 'qva', 'qvo', 'qvs'

for each Dir in dirlist ('$(Root)'&'\*')

for each File in filelist (Dir&'\*.' &Ext)

Load

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime,

Subfield('$(File)', '\', 1+SubStringCount('$(File)', '\')) as Fichero,

left('$(File)', index('$(File)', '\', -1)) as Directorio

autogenerate 1;

next File

next Dir

next Ext

// here $(File)' is list of files in directory.

// You can modify as required.

Re: Loading Multiple Excel files

Anonymous — Tue, 25 Nov 2014 10:48:30 GMT

Load Multiple excel sheets using For loop

it will help you.

Re: Loading Multiple Excel files

Anonymous — Tue, 25 Nov 2014 10:50:37 GMT

i think your code is missing (ooxml, embedded labels, table is [$(sheetName)])

[$(sheetName)] is the sheet name

try this code if you have multiple sheets in each excel file

for each file in FileList('C:\test\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:

Load *,'$(sheetName)' as SheetName

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Drop table tables;

//set tables=Null;

Next file

Regards

Harsha

Re: Loading Multiple Excel files

Tue, 25 Nov 2014 18:13:35 GMT

Thanks Harsha ur code really does work

Re: Loading Multiple Excel files

Anonymous — Tue, 25 Nov 2014 18:21:47 GMT

Hi Shruti

please close the post which so that it will be helpful to others.

Regards

Harsha