Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
The problem iam facing with the script is .. it always takes the source files only from where the qvw is placed. Even changing the path to the different drive it does take the file from their..
All my files are placed in a folders like 2014.csv /2015.csv etc.. so the csv file names are like file1-2014. so here file1-2014 needs to be into product. similarly I have file2-2014...etc
sub GetCSVFIleNames(Root)
for each FoundFile in filelist( Root & '\*.csv')
FoundFile = right('$(FoundFile)', 8);
Product:
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetCSVFIleNames(SubDirectory)
next SubDirectory
end sub
Call GetCSVFIleNames('H:/') ;
Hi Sridhar,
Their is a mistake in the load here.
My requirement is each file is dedicated to each load
so if put just as from ['$(FoundFile)'] .it also tries to load the same file as below and gives an error field not found
Product:
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Customer:
LOAD
Customer Name,
First Name,
Address] as [Address]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
so actually it should be as below.. so this case it is not working
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Customer:
LOAD
Customer Name,
First Name,
Address] as [Address]
FROM
['$(FoundFile)'\file2-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Hi sasi,
I can try this '$(FoundFile)' as SourceFile - but how can I give the filenames for as SourceFile.?
for example - file1 is common name .. only the year goes on changing --based on the folder my file looks like this as below
2014.csv folder >> has the following below files
file1-2014.csv
file2-2014.csv
file3-2014.csv
Try like this
sub GetCSVFIleNames(Root)
for each FoundFile in filelist( Root & '\*.csv')
FoundFile = right('$(FoundFile)', 8);
let vTmpFoundFile=SubField(FoundFile,'\',-1);
//if filename contains file1
if Index('$(FoundFile)','file1-')>0 then
LOAD
[Customer Number],
[Customer Name],
[Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Elseif Index('$(FoundFile)','file2-')>0 then
Customer:
LOAD
[Customer Name],
[First Name],
[Address] as [Address]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
end if
next FoundFile;
for each SubDirectory in dirlist( Root & '\*' );
call GetCSVFIleNames(SubDirectory);
next SubDirectory;
end sub
Call GetCSVFIleNames('C:') ;
hi sasi,
it looks perfect so far..iam trying to put across all files and do the test..can you tell me what this corresponds to
let vTmpFoundFile=SubField(FoundFile,'\',-1);
My post is just the correct way to do the inner part of the loop. You still need the rest to navigate to the other folders.
oh that is to just to get the file name without the path..I thought you needed that in your logic
hi sasi,
I have just started the load. it has around 600 files so it would take few mnts.. can you also let me know can I load this all the csv data into a qvd ----means ..
can I include store into statement at the end after the below statement
Call GetCSVFIleNames('H:') ;
Store into H:\...test.qvd ..after storing how could I load the data
You could try after your method call
Call GetCSVFIleNames('H:') ;
Store Products into Products.qvd (qvd);
Store Customers into Customers.qvd (qvd);
hth
Sasi
Hi sasi,
I loaded all the files and is perfectly looping through.
Great,glad that your problem is solved.
iMay I suggest that you close this thread by marking any helpful and a correct answer so that It can be helpful to other who might be looking for a similar solution?
Thanks
Sasi