dynamic load of excel files

[#1]

Hello,

I want to load excel files which are in different paths.

I tried something like that:

Sources:
LOAD
department,
path
FROM
dep_pathes.txt;
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

for each path in Sources
LOAD * FROM '$(path)';
next

another solution could be, that there's only one xls-file but with different tables. But how can I manage that?:

LOAD
FROM
\\path
(biff, no labels, table is [dept$]

here: dept is e.g. 'production','logistics'

Thanks for help

Peggy

[#2]

Hello,

I used the Wiki to create the following solution:

Quellen:
LOAD
 recNo() as QNR,
 Abteilung,
 Quellverzeichnis
FROM
Include\Quellverzeichnisse_Projektbereiche.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

FOR i = 0 TO NoOfRows('Quellen')-1
 LET varFilename = peek('Quellverzeichnis',i,'Quellen');
 LET varAbteilung = peek('Abteilung',i,'Quellen');

 ODBC CONNECT TO [Excel Files;DBQ=$(varFilename)];

 varTables:
 SQLtables;
 DISCONNECT;
 FOR j = 0 to NoOfRows('varTables')-1
 LET varSheetName = purgeChar(peek('TABLE_NAME', j, 'varTables'), chr(39));
 IF wildmatch('$(varSheetName)', '20*') THEN
 LET varSheetName = mid(varSheetName,1,5);
 Projektplanung:
 LOAD
 RecNo()+1 as Zeile,
 '$(varAbteilung)' as Projektabteilung,
 '$(varAbteilung)'& @1 as %Projekt,
 @1 as Projekt_Planung,
 @2 as Projektsteps,
 @3 as [Aufgaben Termine],
 num(@4,'#.###,00 €') as Kosten,
 date(@5) as Start,
 date(@6) as Ende,
 if(@7='',0,@7) as erl,
 '$(varAbteilung)' & @8 as %MA,
 if(@6 < today() and @7 <100,'überfällig','') as Status,
 '$(varSheetName)' as ReiterName
 FROM
 $(varFilename)
 (biff, no labels, table is [$(varSheetName)], filters(
 Remove(Row, Pos(Top, 1)),
 Replace(1, top, StrCnd(null)),
 Replace(2, top, StrCnd(null)),
 Replace(3, top, StrCnd(null))
 ));
 END IF
 NEXT

 DROP TABLE varTables;
 LET j = 0;

NEXT
[code\]</body>