Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I got a folder with a lot of xlsx ( maybe 200) but they have all same column
My aim is to concatenate all xlsx file
So, I did that :
For Each file in FileList('lib://Sharepoint/Shared Doc/Off/01- ds/01- coll/1.01 preno\*.xlsx')
final_table:
LOAD
*
FROM [$(file)] (ooxml, embedded labels, table is [RENDU]);
Next file;
It returns me no error but no data too.
Someone could explain me if I'm wrong and how I need to do
Thanks for reading me
@Fred12 try below. This approach will work only when you have single sheet in all excel files.
Data:
Load 0 as Temp
AutoGenerate 0;
for Each vFile in FileList('lib://DataFiles/*.xlsx')
Concatenate(Data)
LOAD *
FROM [$(vFile)]
(ooxml, embedded labels, table is []);
Next
Drop Field Temp;
if your excel files multiple sheets with different name then use below approach
https://community.qlik.com/t5/QlikView-App-Dev/Dynamic-File-Load-and-store-it-as-qvd/td-p/1740736
I got multiple sheet, but I just want 1 of them,
I replace
(ooxml, embedded labels, table is []);
by the name of my sheet like that :
(ooxml, embedded labels, table is [RENDU)
but atleast I got again nothing,
no error but no line
@Fred12 not sure but below code works fine for me. May be you can debug for any issue at your end. Check your path is correct in filelist function. I have updated my code which will work if any excel file is not having sheet name you want to load it will not throw error and will load other files
Data:
Load 0 as Temp
AutoGenerate 0;
for Each vFile in FileList('lib://DataFiles/test_*.xlsx')
set ErrorMode=0;
Concatenate(Data)
LOAD
*,
FileBaseName() as File
FROM [$(vFile)]
(ooxml, embedded labels, table is [TEST]);
if ScriptErrorCount>0 then
Trace "Error occurred while loading $(vFile)";
EndIf
set ErrorMode=1;
Next
Drop Field Temp;
Hello,
I unterstood why it didn't work, it's because you can't use sharepoint for that.
I changed my connection, and I wanted to know how I can to load multiple sheet excel without use your solution on other post because it doesn't work.
I can't use connector like you did
thanks