topic Re: Consolidate multiple sheets from multiple excel files.!!! in QlikView

Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 03:23:20 GMT

Community,

I have requirement to consolidate all excel sheets from multiple workbooks. I searched in forum and got some ideas but I have some problem in getting the desired result.

* My data is excel files and each excel consists 12 months of data and additional two sheets are for some analysis (sometimes they add additional sheets for analysis). I need to consolidate all months data (Eg; Jan'15 to Dec'15) from all excel files but should exclude the sheets.

* They added two columns in the year 2015 and they will add more based on the future needs . So it should pick the new headers too.

Snapshots:

2013 (Jan month):

2015 (Jan month) (Additional fields added)):

My out put should be like below:

Could anyone guide me or provide me the code?

The above screenshots are just mock data and attached the same.

Re: Consolidate multiple sheets from multiple excel files.!!!

Siva_Sankar — Wed, 10 Jun 2015 05:36:27 GMT

tAMIL,

Find the attached. Guess you missed the other sheets in the excel. Let me know this is what you are looking for.

Re: Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 05:51:26 GMT

Siva,

Yes, The output is correct. But i have many excel files and need to loop through all the excel as well as sheets. In that case we need a for loop. Could you provide the code for that.?

Re: Consolidate multiple sheets from multiple excel files.!!!

Siva_Sankar — Wed, 10 Jun 2015 06:02:41 GMT

PFA

Re: Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 07:22:18 GMT

I have tried the below code but it just showing script error the load statement.

* I just tried to consolidate the sheets.

let vDataFolder = 'C:\Users\Tamil\Desktop\d\';

for each vFile in filelist('$(vDataFolder)*.xlsx')

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

Temp_Tables:

sqltables;

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = Replace(replace(vSheetName, '$', ''),Chr(39)&Chr(39),Chr(39)); // sqltables seems to add a random $ sign and single quotes

If (Wildmatch('$(vSheetName)','*'&Chr(39)&'*')) then

// Load the data

SampleData:

LOAD *,

'$(vFileName)' as [File Name], \\script error

'$(vSheetName)' as [Sheet Name]

FROM [$(vFile)]

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

ELSE

ENDIF

DROP TABLE Temp_Tables;

Re: Consolidate multiple sheets from multiple excel files.!!!

Siva_Sankar — Wed, 10 Jun 2015 08:22:23 GMT

Tamil,

There is a working example with source and qlikviewfile here https://community.qlik.com/docs/DOC-7860
Try checking it.

Re: Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 08:36:16 GMT

Siva,

Thank you for your reply. I have tried and the script working till the line "LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));"

after that it goes directly to the line NEXT i.

It's not taking the fields. Can you have a look (In debug mode). Just check with the attached file.

FOR EACH file in FileList('C:\Users\Tamilarasu.Nagaraj\Desktop\d\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format

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

FileBaseName()as FIle,

FileDir() as Dir, \\Skipped

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Re: Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 09:55:18 GMT

Hi Siva,

Finally its working. But I want to concatenate the sheets (in future they will add some fields) instead of joining all the sheet. I have tried but not able to find the way. Could you tell me where to adjust the code.

I have attached sample files !.

Re: Consolidate multiple sheets from multiple excel files.!!!

settu_periasamy — Wed, 10 Jun 2015 11:59:54 GMT

Hi Tamil,

if you want to combine all the tables, See the blue color script (in the end)

FOR EACH file in FileList('C:\Users\Settu.Periasamy\Desktop\QlikComm\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'Temp'), chr(36));

LET sheetName=replace(sheetName,chr(39)&chr(39),chr(39));

If (Wildmatch(sheetName,'*'&Chr(39)&'*')) then

Table:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)], filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));

ELSE

ENDIF

NEXT i

Drop table Temp;

Final:

Concatenate

LOAD *

Resident Table;

DROP Table Table;

I checked. it is working fine. Let us know, if it not working?

Regards,

Settu P

Re: Consolidate multiple sheets from multiple excel files.!!!

tamilarasu — Wed, 10 Jun 2015 12:16:31 GMT

Hello Settu,

Fantastic !!. My whole day gone for the above script. Thanks a lot...!!!

Siva Sankar: Thanks a lot for the start!!

Have a nice evening!! Attached the QV file and I hope it will be useful for someone!

Re: Consolidate multiple sheets from multiple excel files.!!!

help4qv123 — Wed, 10 Jun 2015 12:46:09 GMT

Hey Tamil,

How r posting these images...its quite funny...can u tell me bro

Re: Consolidate multiple sheets from multiple excel files.!!!

N_1 — Thu, 17 Mar 2022 03:21:17 GMT

Hi Tamil. Can you provide the code in some other format like text?? I am not able to view the file. Thanks!