Concatenate Issue

[tamilarasu]

I am trying to concatenate multiple excel sheets from workbooks. So I am using loop to consolidate the data. But I am getting an error and I am not sure where I a wrong. Can any one check and correct the code?

SET Counter = 0;
FOR EACH file in FileList('C:\Users\Tamilarasu.Nagaraj\Desktop\RPC n\Sample\Sample - Copy\*.xlsx');


Let Counter = Counter +1;
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));


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)))));


If (Counter =1 and i =0) then


NoConcatenate
Consolidated:
Load *
Resident Table;


Drop Table Table;


Else


Concatenate (Consolidated)
Rem:
Load * 
Resident Table; 


Drop Table Table;


End If


NEXT i
Drop table Temp;

Thank you.

[Gysbert_Wassenaar]

Either the ODBC driver is 32-bit and your Qlikview is 64-bit or the other way around. You need to make sure they're the same or you need to use the CONNECT32 keyword to force use of the 32-bit driver.

[tamilarasu]

Gysbert: Working fine . But I have two additional columns in 2nd workbook and I want to add them in the table. Any suggestion.?

[Gysbert_Wassenaar]

The number of fields does not need to be the same. Using the Concatenate keyword will force concatenation.

[tamilarasu]

Sorry, I could not get the exact result (Final output doesn't contains the below two fields). I have below two fields in Book3 (all three sheets) but the loop didn't take this fields. Could you advice.

Thanks for you help on this.

[tamilarasu]

Also, first excel data only loaded (i.e. only 4 rows but it should be 8 rows). Seems the second excel not concatenated into the table.

[tamilarasu]

Hi Gysbert:

I have found the problem and modified your code (Just two lines) and I got the desired output.

$(vConcatenate)

    //[$(vSheetName)]:     // I have comment this line

    Consolidated:

    LOAD *, '$(vSheetName)' as Sheet, '$(vFile)' as FileName 

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

  SET vConcatenate = Concatenate (Consolidated);

NEXT i 

Thanks a lot.

[tamilarasu]

I have one more doubt. Can you check the below attached file. Its weird and I don't know the reason. I guess the problem should be in excel format (!!). Please advice.