Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Iam currently facing a problem.Iam using the following script to extract multiple excel files
idx=1
for each subdir in dirlist(*.csv)
Load *
....from $(subdir)
idx=idx+1
next subdir
The script runs and extract all the files.The problem here is it considers only the path where the qvw file is there.
i.e the source files and the qvw file should exist in the same path. If the source files are moved to another directory it gives me the error source file not found..iam not sure from how the path takes by itself.. also my relative path is unchecked..
But when you do:
FoundFile = right('$(FoundFile)', 8);
you strip away the search path, so it becomes like 2014.csv.
That means this won't work:
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
You don't have the path anymore in FoundFile.
I think this is easier, using the filelist filter mask:
sub GetFiles(Root)
for each FoundFile in filelist( Root & '\file1*.csv')
Product:
LOAD [Customer Number],
[Customer Name],
[Product name],
[Product number]
FROM '$(FoundFile)'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each FoundFile in filelist( Root & '\file2*.csv')
Customer:
LOAD
[Customer Name],
[First Name],
[Address]
FROM '$(FoundFile)'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetFiles(SubDirectory);
next SubDirectory
end sub
Call GetFiles('C:\Temp');
Search QV Help for the word "DoDir", and you'll see an example that does what you need.
Hi ,
Can you let me know how can I call subroutine in multiple load statements.
For eg.
sub Folder(Root)
for each FileExtension in 'csv'
for each FoundFile in dirlist( Root & '\*.' & FileExtension)
LOAD , from $foundfile
.....
next FoundFile
next FileExtension
for each FoundFileectory in dirlist( Root & '\*' )
call ScanFolder(FoundFileectory)
next FoundFileectory
end sub
Call Sub ( ...........path);
LOAD , from .....
Should I end the sub routine after all the load statements or should I need to call sub routine in each and every load statements.
Hi Oleg,
I tested with DoDir as well but it does not work. both the qvw and the files should remain in the same directory. If I maintain the qvw in one dir and the files in other . it would raise the script error. file not found...
You don't call the subroutine in a load statement.
The subroutine can contain one or more load statements in itself.
If you want to, you can call the subroutine more than once, and even itself recursively.
As you see in the DoDir example below, it calls itself within the For Each Dir, and will thus continue through the structure of sub-directories.
// Example 2 - List all QV related files on disk
sub DoDir (Root)
For Each Ext in 'qvw', 'qvo', 'qvs', 'qvt', 'qvd', 'qvc', 'qvf'
For Each File in filelist (Root&'\*.' &Ext)
LOAD
'$(File)' as Name, FileSize( '$(File)' ) as
Size, FileTime( '$(File)' ) as FileTime
autogenerate 1;
Next File
Next Ext
For Each Dir in dirlist (Root&'\*' )
Call DoDir (Dir)
Next Dir
End Sub
Call DoDir ('C:')
In dirlist and filelist you need to specify the path like filelist ('c:\mydata\*.csv' ) if they are in another folder
Hi Ralf,
My Subroutine is good enough.
the path issue still exists.
I tried to change the path in both dirlist and filelist. But it gives me the same error. Its again looking for the source documents where the QVW is placed.
Both in dirlist and file list I changed the path.
filelist ('d:\source\*.csv' ) and dirlist('c:\mydata\*.csv' )
filelist ('d:\source\*.csv' ) and dirlist(''d:\source\*.csv' ' ) - Sorry its the same.
So if you try this, does it load all CSVs in d:\source?
Sub LoadCSV (folder)
For Each File in filelist (folder&'\*.csv')
Load * from '$(File)';
Next File
End Sub
Call LoadCSV ('d:\source')
is this needs to be with in the sub routine?