for each dir

[#1]

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:/') ;

[#2]

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);

[#3]

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

[sasiparupudi1]

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:') ;

[#5]

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);

[jonathandienst]

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.

[sasiparupudi1]

oh that is to just to get the file name without the path..I thought you needed that in your logic

[#8]

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

[sasiparupudi1]

You could try after your method call

Call GetCSVFIleNames('H:') ;

Store Products into Products.qvd (qvd);

Store Customers into Customers.qvd (qvd);

hth

Sasi

[#10]

Hi sasi,

I loaded all the files and is perfectly looping through.

[sasiparupudi1 · Accepted solution]

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