Consolidate multiple sheets from multiple excel files.!!!

[tamilarasu]

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.

[Siva_Sankar]

tAMIL,

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

[tamilarasu]

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

[Siva_Sankar]

PFA

[tamilarasu]

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

  next

DROP TABLE Temp_Tables;

next

[Siva_Sankar]

Tamil,

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

[tamilarasu]

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

[tamilarasu]

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

[settu_periasamy · Accepted solution]

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;

Next

Final:

Concatenate

LOAD *

Resident Table;

DROP Table Table;

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

Regards,

Settu P

[tamilarasu]

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!