topic Re: Loading multiple xls files in QlikView

Loading multiple xls files

Fri, 05 Dec 2014 05:48:18 GMT

Hi All,

I need to load multiple xls file which will generated daily.

How to achieve this?

Xls file names as below:

ProductSales_25-Nov-2014.xls

ProductSales_26-Nov-2014.xls

ProductSales_27-Nov-2014.xls

ProductSales_28-Nov-2014.xls

ProductSales_29-Nov-2014.xls

ProductSales_5-Dec-2014.xls

ProductSales_30-Nov-2014.xls

....

Sample Data:

Date	Product_Id	Sales
12/5/2014	P1	100
12/5/2014	P2	200
12/5/2014	P3	300
12/5/2014	P4	400
12/5/2014	P5	500
12/5/2014	P6	600
12/5/2014	P7	700

Thanks in advance.

Re: Loading multiple xls files

kushalthakral — Fri, 05 Dec 2014 05:51:44 GMT

Hi Arjun

please follow the below link

Load multiple excel files from Multiple excel sheetsinto QV

Regards

kushal Thakral

Re: Loading multiple xls files

its_anandrjs — Fri, 05 Dec 2014 05:53:52 GMT

Hi,

Try with loading

LOAD *,

FileName() as FileName,

FileBaseName() as FileBaseName

FROM

(biff, embedded labels, table is Sheet1$);

Note:- Here where Date field come from if it is in filename then use FileBaseName()

Regards

Anand

Re: Loading multiple xls files

Anonymous — Fri, 05 Dec 2014 05:57:35 GMT

hi try this

for each file in FileList('R:\Harsha\qlikview2nd\*.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

Re: Loading multiple xls files

Anonymous — Fri, 05 Dec 2014 05:58:52 GMT

Hey,

Test:

Load Date, Product_ID, Sales

from

(ooxml, embedded labels, table is Sheet1);

This wil work if your multiple files are having similar naming structure and all in single folder.

BR,

Chinna

Re: Loading multiple xls files

its_anandrjs — Fri, 05 Dec 2014 06:00:18 GMT

Hi,

And another way is

LOAD *,

FileName() as Excelnames

FROM

*.xlsx

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

Regards

Anand

Re: Loading multiple xls files

kushalthakral — Fri, 05 Dec 2014 06:00:33 GMT

Hi Arjun

i am also attaching sample qvw file for your reference.

Regards

Kushal T

Re: Loading multiple xls files

MarcoWedel — Fri, 05 Dec 2014 06:48:38 GMT

Maybe also like

LOAD *,

FileName() as FileName,

Date#(Mid(FileBaseName(), 14),'DD-MMM-YYYY') as FileDate

FROM

(biff, embedded labels, table is Sheet1$)

Where FileBaseName() like 'ProductSales_*' and IsNum(Date#(Mid(FileBaseName(), 14),'DD-MMM-YYYY'));

Re: Loading multiple xls files

prma7799 — Fri, 05 Dec 2014 06:57:17 GMT

Hi Arjun,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements.

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

'' AS Data.Field1,

'' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

Concatenate(Data)

LOAD

FROM [$(vExcelFileName).xlsx]

(ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

Thanks,

Prashant

Re: Loading multiple xls files

Fri, 05 Dec 2014 07:52:43 GMT

Put all the relevant files in a particular folder and while loading one of these files using Table Files button, just change the name of the file to ../*.xls