20 Replies Latest reply: Jan 29, 2013 10:55 AM by Pramod Dontham RSS

    Load or Reload from folder

    Christoph Goertz

      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
          Joachim Rogginer

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

          Rgds,
          Joachim

          • Load or Reload from folder
            jong

            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
                Christoph Goertz

                 


                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
                    Joachim Rogginer

                    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
                        Christoph Goertz

                         


                        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
                            Joachim Rogginer

                            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
                                Christoph Goertz

                                 


                                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:Re: AW:Re: Load or Reload from folder
                                    Peter Rieper

                                    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
                          Joachim Rogginer

                          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:Load or Reload from folder
                            Ralf Becher

                            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
                              Ralf Becher

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