Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tamilarasu
Champion
Champion

Consolidate multiple sheets from multiple excel files.!!!

graffiti-message-hello-smiley-emoticon.gif 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):                                                                                        

2013.PNG  

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

2015.PNG


My out put should be like below:

Output.PNG

Could anyone guide me or provide me the code?

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

animated-thank-you-smiley-emoticon.gif

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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

View solution in original post

11 Replies
Siva_Sankar
Master II
Master II

tAMIL,

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

tamilarasu
Champion
Champion
Author

hi.gifSiva,

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. facing-problems-smiley-emoticon.gif Could you provide the code for that.?

Siva_Sankar
Master II
Master II


PFA

tamilarasu
Champion
Champion
Author

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

* I just tried to consolidate the sheets.

Capture.PNG

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

Tamil,

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

tamilarasu
Champion
Champion
Author

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

Hi Siva,

are-you-there-smiley-emoticon.gif

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

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

Hello Settu,

Fantastic !!banana-with-guitar-smiley-emoticon.gif. My whole day gone for the above script.dead-computer-geek-smiley-emoticon.gif  Thanks a lot...!!!

Siva Sankar: friendship-handshake-smiley-emoticon.gifThanks a lot for the start!!

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