Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Srinivas
Contributor

Extract the multiple excel files with different sheetnames and different hearders

Hi All,

I need to extract the multiple excel file with different sheetNames and different headers.

Excel 1

Headers1,Headers2,Headers3,Headers4,Headers5 and sheetnames is Sheet1

Excel 2

Headers1,Headers2,Headers3,Headers4,Headers5 and sheetnames is Sheet1

Excel 3

Headers1,Headers3,Headers5 and sheetnames is Data

Excel 4

Headers1,Headers2,Headers3,Headers4,Headers5 and sheetnames is Sheet1


For the above scenario Excel 1, Excel 2 and Excel 4, Headers names and sheetNames are same

But Excel 3 doesn't have same headers and sheetName.

I am struck with the below code it is not executable please suggest me,

Let vPath = '.C:\Users\Pol\Desktop\Data\Xlsx';

for each vFile in FileList('$(vPath2)\*.Xlsx')

     TableName:

     load  *,  

            '$(vFile)' as SourceFile

     from [$(vFile)]

     (ooxml, embedded labels);

Next

when running the above code it sows the error like :

Error.PNG

Thanks,
Srinivas
1 Reply
MVP
MVP

Re: Extract the multiple excel files with different sheetnames and different hearders

You can use a routine I developed two and a half years ago.

I have attached a working QlikView application for you along with an Excel workbook.

You can find some more explanation in this article: How to extract Sheet Names from an Excel XLSX-file without ODBC

The attached QlikView application has been modified so it illustrates how you can get the sheet names first and then iterate with a FOR NEXT loop to load the various sheets. I leave it to you to add an extra FOR NEXT loop to iterate through several Excel Workbook files.