Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
tAMIL,
Find the attached. Guess you missed the other sheets in the excel. Let me know this is what you are looking for.
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.?
PFA
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
Tamil,
There is a working example with source and qlikviewfile here https://community.qlik.com/docs/DOC-7860
Try checking it.
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
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 !.
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
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!