load a set of excel file in a directory

[#1]

Hello :);

I want to load a set files excel with same structure in a directory. for this i use this scrpit:

Table1:

LOAD

Field

FROM (biff, embedded labels, table is Sheet1$); // Load xls format files

Concatenate

LOAD

Field

FROM (ooxml, embedded labels, table is Sheet1); // load xlsx format files.

My problem is if there are no files in ma directory (xls or xlsx) i have an error, how can i check the presence of a file before start loading.

Best regards.

[#2]

Sorry the xls and xlsx files are in the same directory c:\Mydirectory\

[#3]

Hello,

you can use one of the file-funcions. I tested it like that:

LET vMyFS = filesize( 'xyz.xls' );

if ( vMyFS > 0) then
 trace --> $(vMyFS);
end if;

Regards, Roland

[prieper]

Think that - for various reasons - it makes more sense to work with

FOR EACH sFileName IN FILELIST('directory & filepattern') ..... NEXT sFileName

rather than with the *.
With such construction you might be able to count number of files matching your specification, IF <1 ....
Filefunctions like FILENAME() etc require an exact name, wildcards are not allowed.

Script might be like:

FOR EACH sFileName IN FILELIST ('c:\directory\export_*.xls')
Data:
LOAD * FROM $(sFileName);
NEXT sFileName
IF NOOFROWS('DATA') = 0 THEN
EXIT SCRIPT; // or any other error-action
END IF

HTH
Peter

[#5]

Thanks all for the suggestions! the both solutions worj but i think i will use a file-function (2nd solution).

Thank u very much Roland and Peter.

[#6]

Sorry i have another qestion,

is it possible after loading the file to move it in another directory ( cut-past)? have i to use windows commandes

[prieper]

Think that's not possible in the script, but you may compare the filenames derived from FILELIST with the already loaded list filenames, and load only those, which are new.

HTH
Peter

[#8]

Why this commande don't work:

execute copy C:\SrcDirectory\Export_ABM_1.xls C:\TrgDirectory;

[prieper]

Because these are no commands available in QV. Am not sure, whether you may call some external routines out of a script.

++
Peter

[#10]

This commande work now because i added CMD /C befor 'move':

execute cmd.exe /c C:\Src\export.xls c:\target