Loading multiple xls files

[#1]

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.

[kushalthakral]

Hi Arjun

please follow the below link

Load multiple excel files from Multiple excel sheetsinto QV

Regards

kushal Thakral

[its_anandrjs]

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

[Anonymous]

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

[Anonymous]

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

[its_anandrjs]

Hi,

And another way is

LOAD *,

  FileName() as Excelnames

FROM

*.xlsx

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

Regards

Anand

[kushalthakral]

Hi Arjun

i am also attaching sample qvw file for your reference.

Regards

Kushal T

[MarcoWedel]

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'));

[prma7799]

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)]);

NEXT

Thanks,

Prashant

[#10]

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