Multiple Excel sheets without ODBC/OLEDB connection

[vardhancse]

Hi,

Can any one please let me know option to load multiple excel sheets(Having multiple sheets) with out using ODBC connection

[vardhancse]

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

[Kushal_Chawda]

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)

[vardhancse]

-> 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

[vardhancse]

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

[prieper]

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

[vardhancse]

Hi Peter,

thank you, if possible can please send me sample QVW for my reference please

[Kushal_Chawda]

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.

[vardhancse]

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

[prieper]

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

[Kushal_Chawda]

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;