Conditional concatenate

[Anonymous]

Hi,

     I remember I saw a QV file with a conditional concatenate once, but I cannot find it can I cn't remember how it was used it was for a loop to caoncatenate all tabs in an excel file, it was something like the below code, but I can't make it work, anyone knows how to do it?

(some excel tabs have some extra columns that's why I need to use concatenate)

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)&'$');

if $(i)>0 then

    $(=Concatenate);

ENDIF

Weeks:

LOAD *

FROM $(FN) (ooxml, embedded labels, table is [$(sheetName)]);

NEXT

Thanks!

[stigchel · Accepted solution]

This what I once found:

//Load all sheets in an excel file

DIRECTORY;

Table:

load '' as field autogenerate 0;

For Each vFile in FileList('*.xlsx')

  trace file=$(vFile);

    ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

    Sheets:

    SQLTABLES;

    DISCONNECT;

    For i = 0 To NoOfRows('Sheets')-1        

    Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

        trace $(vSheet);

        concatenate(Table)

        LOAD '$(vSheet)' as [Tab Name],

        *

        From [$(vFile)]

        (ooxml, embedded labels, table is [$(vSheet)]);

  Next;

NEXT;

[m_woolf]

I searched the forum:

Load multiple sheets and excel files

[Anonymous]

Thanks Piet, this did the trick

Table:

load '' as field autogenerate 0;

concatenate(Table)