biester wrote: OK, but that means, that the whole thing should NOT depend on a date and we should store the names of the files we read in. And that also means, that even if the newly created file "B" differs from the previous file "B", it should be ignored in future reloads. Is this correct? If yes, we can perhaps make a little demo for that (if no one else has another or better suggestion). Rgds, Joachim<div></div>
yes - everthing correct! A little demo woukld be very nice
this is only a quick trial. Note that you must use partial reload because the table data have to be kept. An option would also be using qvd and incremental load. But as said this is only a quick shot.
/******************************************************* this proggi is supposed to read file-names and check if they were already loaded read only those files, which are "new" add the filenames to existing table
// =========================================================== Variables LET sDir = 'C:\TEMP\'; // Directory to look through LET sFile = '*.xls'; // Searchpattern LET sSearchPattern = sDir & sFile; // ================================================= List of known Files KnownFiles: LOAD * FROM .\KnownFile.qvd (qvd); // =================== Scan through directory and list all possible files FOR EACH vFile in FileList (sSearchPattern) FilesFound: LOAD '$(vFile)' AS FileFound AUTOGENERATE 1; NEXT vFile // ===================================== Find only the unknown file-names IF NoOfRows('FilesFound') > 0 THEN Files2Read: LOAD FileFound AS File2Read RESIDENT FilesFound WHERE NOT EXISTS (FileNameRead, FileFound); DROP TABLE FilesFound; ELSE EXIT SCRIPT; END IF // ==================================================== Load the new data LET j = NoOfRows('Files2Read'); IF LEN(j) > 0 THEN FOR i = 1 TO j LET sFile2Read = PEEK('File2Read', i-1, 'Files2Read'); // 1st record is 0 NewData: // read the new data LOAD *, FileName() AS FileName, FileTime() AS FileTime FROM $(sFile2Read) (biff, embedded labels, table is Sheet1$); KnownFiles: // add into KnownFile-list LOAD '$(sFile2Read)' AS FileNameRead, FileTime('$(sFile2Read)') AS FileTimeRead AUTOGENERATE 1; NEXT i END IF
STORE KnownFiles INTO .\KnownFile.qvd; DROP TABLE KnownFiles; DROP TABLE Files2Read;
if IsPartialReload() then qvdfiles: ADD FIRST 1 LOAD filedir() as QvdPath , filename() as QvdFile , QvdTableName( '$(vPath)'&filename()) as QvdTable FROM [$(vPath)*.qvd] (qvd) WHERE NOT EXISTS(QvdFileLoaded, filename());
qvdfilestoload: REPLACE LOAD QvdFile as QvdFileToLoad RESIDENT qvdfiles WHERE NOT EXISTS(QvdFileLoaded, QvdFile);
qvdfilesloaded: ADD LOAD QvdFileToLoad as QvdFileLoaded RESIDENT qvdfilestoload; else qvdfiles: FIRST 1 LOAD filedir() as QvdPath , filename() as QvdFile , QvdTableName( '$(vPath)'&filename()) as QvdTable FROM [$(vPath)*.qvd] (qvd);
qvdfilestoload: LOAD QvdFile as QvdFileToLoad RESIDENT qvdfiles;
qvdfilesloaded: LOAD QvdFileToLoad as QvdFileLoaded RESIDENT qvdfilestoload; endif
..also see attached file.
- Ralf
Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine
if IsPartialReload() then excelfiles: ADD FIRST 1 LOAD filedir() as ExcelPath , filename() as ExcelFile FROM [$(vPath)*.xls] (biff) WHERE NOT EXISTS(ExcelFileLoaded, filename());
excelfilestoload: REPLACE LOAD ExcelFile as ExcelFileToLoad RESIDENT excelfiles WHERE NOT EXISTS(ExcelFileLoaded, ExcelFile);
qvdfilesloaded: ADD LOAD ExcelFileToLoad as ExcelFileLoaded RESIDENT excelfilestoload; else excelfiles: FIRST 1 LOAD filedir() as ExcelPath , filename() as ExcelFile FROM [$(vPath)*.xls] (biff);
excelfilestoload: LOAD ExcelFile as ExcelFileToLoad RESIDENT excelfiles;
excelfilesloaded: LOAD ExcelFileToLoad as ExcelFileLoaded RESIDENT excelfilestoload; endif
- Ralf
Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine
