Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Arjun
please follow the below link
Load multiple excel files from Multiple excel sheetsinto QV
Regards
kushal Thakral
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
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
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
Hi,
And another way is
LOAD *,
FileName() as Excelnames
FROM
*.xlsx
(ooxml, embedded labels, table is [$(*)]);
Regards
Anand
Hi Arjun
i am also attaching sample qvw file for your reference.
Regards
Kushal T
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'));
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
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