Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can any one please let me know option to load multiple excel sheets(Having multiple sheets) with out using ODBC connection
Hi Peter,
1. All work books will be one single directory
2. Workbook names:(every month new work book will be added)
a. Sales.xlsx
b. Sales1.xlsx
c. Sales3.xlsx
3.Each workbook will have multiple sheets and they are dynamic but will have same set of columns
Can you please share the details for below
-> How your excel file names look like? All excel files resides in same folder?
-> How your each excel file sheet names looks like? All sheet names must have uniformity for eg. SalesData_*.xls (Where * could be numbers, dates and SalesData should be constant)
-> How your excel file names look like? All excel files resides in same folder?
Yes, all excel files resides in same folder
-> How your each excel file sheet names looks like? All sheet names must have uniformity for eg. SalesData_*.xls (Where * could be numbers, dates and SalesData should be constant)
Yes, All sheets will have constant name some thing like
10022015-15022015
30022015-30032015
Hi As a n alternative solution tried some thing;
SHT:
LOAD Distinct Concat(chr(39)&Sheetnames &chr(39),chr(44)) as SH
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);
let vSH=peek('SH',0,'SHT');
For each table in $(vSH)
LOAD *
FROM
[Sales.xlsx]
(ooxml, embedded labels, table is '$(table)');
next
**Used separate excel sheet which will have all sheet names.
**Able to load all sheets from one single excel workbook
But my goal is to load multiple excel workbooks as well.
can please help me out here as well
QV recognizes "*" in filenames,
so
For each table in $(vSH)
LOAD *
FROM
[Sales*.xlsx]
(ooxml, embedded labels, table is '$(table)');
should work.
Else you may read the filenames via FILELIST and create a separate loop,e.g.
FOR EACH sFile IN FILELIST('myDirectory\Sales*.xlsx')
For each table in $(vSH)
LOAD *
FROM
$(sFile)
(ooxml, embedded labels, table is '$(table)');
NEXT sFile
HTH Peter
Hi Peter,
thank you, if possible can please send me sample QVW for my reference please
see this if it helps
LEt vFilePath='F:\Data';
SheetName:
LOAD Name
FROM
[SheetNames.xls]
(biff, embedded labels, table is [Sheet 1$]);
for Each vFile in FileList('$(vFilePath)\Sales*.xls')
Data:
LOAD * Inline [
test ];
for i=1 to FieldValueCount('Name')
let vSheetName = FieldValue('Name',$(i));
Concatenate(Data)
LOAD *
FROM
[$(vFile)]
(biff, embedded labels, table is [$(vSheetName)$]);
NEXT
DROP Field test;
NEXT
DROP Table SheetName;
Note:
Please check your file format and update the thing which I have highlighted in bold.
Hi Kushal,
thank you so much for your support.
Your script works fine but my requirement is that the sheet names in each workbooks is not one and the same.
For e.g:
in Sales.xls we will have data for one year
20012015-20022015
20032015-20042015
Similarly in Sales2.xls will have next for next year data
20012016-20022016
20032016-20042016
If possible can please let me know any alternative for the same
Which file-format: xls or xlsx?
Will you always have the same number of sheets?
If xls you may refer to the first sheet with @1, to the second sheet with @2 etc.
If you do not know the number of sheets, but still xls-format, you may put the errormode to 0 and let some statements run into error.
Peter
add another column which is Excel file name in sheetnames excel.
Note : Excel file name and sheet names should be identical in Sheetname excel.
LEt vFilePath='C:\Users\kush\Downloads';
SheetName:
LOAD Name,
ExcelName
FROM
[SheetNames.xls]
(biff, embedded labels, table is [Sheet 1$]);
for Each vFile in FileList('$(vFilePath)\Sales*.xls')
let vExcelFileName = SubField(SubField('$(vFile)','\',-1),'.',1);
SheetNames:
LOAD Name as SheetNames
Resident SheetName
where ExcelName='$(vExcelFileName)';
Data:
LOAD * Inline [
test ];
for i=1 to FieldValueCount('SheetNames')
let vSheetName = FieldValue('SheetNames',$(i));
Concatenate(Data)
LOAD *
FROM
[$(vFile)]
(biff, embedded labels, table is [$(vSheetName)$]);
NEXT
DROP Field test;
DROP Table SheetNames;
NEXT
DROP Table SheetName;