Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chrisg
Partner - Creator III
Partner - Creator III

Load or Reload from folder

Hi,

i want to load excel-files from a folder - but i only want to load the new files in the folder.

Do you have any hints how to build up the script

many thx

best regards

Chris

Do or Do Not. There is no try!
20 Replies
biester
Specialist
Specialist

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

chrisg
Partner - Creator III
Partner - Creator III
Author


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

thx
Chris

Do or Do Not. There is no try!
biester
Specialist
Specialist

Hi,

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.

Perhaps it helps a little,
Rgds,
Joachim

prieper
Master II
Master II

Hi,

the below is a rough idea, how it might work


/*******************************************************
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;



HTH
Peter

rbecher
MVP
MVP

Hi Chris,

this is an example how we do things like this:

LET vPath = 'c:\test\';

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

Astrato.io Head of R&D
chrisg
Partner - Creator III
Partner - Creator III
Author

Hi Ralf,

many thx - but the script does not run? (Can't open the file: ..*.qvd)

Do you have hind for me

thx

Chris

Do or Do Not. There is no try!
rbecher
MVP
MVP

Did you adjust the variable vPath to your qvd-files path?

The statement

FIRST 1 LOAD
...
FROM [$(vPath)*.qvd] (qvd)


will loop thru all qvd-files in the path.

- Ralf

Astrato.io Head of R&D
rbecher
MVP
MVP

Okay I went wrong, you're looking for Excel files... 😉

I will change the example.

Astrato.io Head of R&D
rbecher
MVP
MVP

..okay here we go:

LET vPath = 'c:\test\';

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

Astrato.io Head of R&D
chrisg
Partner - Creator III
Partner - Creator III
Author

Many thx! perfect

Do or Do Not. There is no try!