topic AW:Load or Reload from folder in QlikView

Load or Reload from folder

chrisg — Mon, 14 Sep 2009 21:47:52 GMT

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

AW:Load or Reload from folder

biester — Tue, 15 Sep 2009 00:13:37 GMT

I'd say: the major issue is how you define "new" ??

Rgds,
Joachim

AW:Load or Reload from folder

chrisg — Tue, 15 Sep 2009 01:42:14 GMT


biester wrote:
I'd say: the major issue is how you define "new" ??
Rgds,
Joachim

sorry and thx: i am talking about additional files in the folder

Rgds

Chris

AW:Load or Reload from folder

prieper — Tue, 15 Sep 2009 02:37:42 GMT

Hi,

as basic approach would suggest:

Read all files in the folder
Compare with a list of the previously loaded files
Load "new" files
Add them to the list of the loaded files (for the next run)

HTH
Peter

AW:Load or Reload from folder

chrisg — Tue, 15 Sep 2009 13:15:08 GMT


Peter Rieper wrote:
Compare with a list of the previously loaded files
Load "new" files
Add them to the list of the loaded files (for the next run)
 <div></div>

can you give me a hind how to do this?

thx

Chris

AW:Load or Reload from folder

Tue, 15 Sep 2009 16:07:51 GMT

If QV ischeduled to reload everyday, the script can be set to load "new" file by using file date = system date. This will pick up the file in the folder on the date of reloading.

Load or Reload from folder

Tue, 15 Sep 2009 16:12:45 GMT

Just think of another option, a flag(date) can be included within the file (in a field perhaps) for comparison purpose. If the flag(date) for file(s) in the folder > max flag(date) in existing data, then load the data into QV from the "new" file.

Hope this help.

Load or Reload from folder

chrisg — Tue, 15 Sep 2009 16:38:30 GMT


jong wrote:
Compare with a list of the previously loaded files
Load "new" files
Add them to the list of the loaded files (for the next run)<div></div>

a code sniped for this would be very helpfullthx

Chris

AW:Re: Load or Reload from folder

biester — Tue, 15 Sep 2009 16:49:50 GMT

Hi,

I just wonder what should happen in a special case:

- You have Files A, B, C in your directory
- You read A,B and C in the first reload
- In the second reload you still have A,B and C - nothing should happen.
- File B is being deleted
- In the third reload also nothing happens
- File B is created again in the directory: What should happen ?

Rgds,
Joachim

P.S.: You look like Derrick 😉

AW:Re: Load or Reload from folder

chrisg — Tue, 15 Sep 2009 17:00:43 GMT


biester wrote:
I just wonder what should happen in a special case:
- You have Files A, B, C in your directory
- You read A,B and C in the first reload

yes!


biester wrote:
- In the second reload you still have A,B and C - nothing should happen.

perfect


biester wrote:
- File B is being deleted
- In the third reload also nothing happens
- File B is created again in the directory: What should happen ?

nothing should happen!

Thx

Chris

AW:Re: AW:Re: Load or Reload from folder

biester — Tue, 15 Sep 2009 17:10:44 GMT

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

AW:Re: AW:Re: Load or Reload from folder

chrisg — Tue, 15 Sep 2009 17:34:04 GMT


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

AW:Load or Reload from folder

biester — Tue, 15 Sep 2009 18:36:04 GMT

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

AW:Re: AW:Re: Load or Reload from folder

prieper — Tue, 15 Sep 2009 19:41:11 GMT

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

AW:Load or Reload from folder

rbecher — Tue, 15 Sep 2009 19:55:14 GMT

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

AW:Load or Reload from folder

chrisg — Tue, 15 Sep 2009 22:33:52 GMT

Hi Ralf,

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

Do you have hind for me

thx

Chris

AW:Re: AW:Load or Reload from folder

rbecher — Tue, 15 Sep 2009 22:47:54 GMT

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

AW:Re: AW:Load or Reload from folder

rbecher — Tue, 15 Sep 2009 22:56:35 GMT

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

I will change the example.

AW:Load or Reload from folder

rbecher — Tue, 15 Sep 2009 23:12:53 GMT

..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

AW:Load or Reload from folder

chrisg — Wed, 16 Sep 2009 19:41:49 GMT

Many thx! perfect