Extract csv files from multiple folders

[#1]

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

[Anonymous]

Hi Rathish,

May be use UNC path.

UNC paths

UNC stands for Universal (or Uniform or Unified) Naming Convention and is a syntax for accessing folders and files on a network of computers. The syntax is as shown:

\\<computer name>\<shared directory>\

followed by any number of directories and terminated with a directory or file name.

The computer name is always preceded by a double backward slash (\\).

In UNC, the computer name is also known as the host name.

[#3]

Hi neetha,

I tried with UNC ..it did not work..but on the other hand it not raise the script error. the script ran and nothing got loaded.

[Ralf-Narfeldt]

Can you share the script?

[#5]

Hi Ralf,

Sorry for the late reply.

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

[Ralf-Narfeldt]

I think you have several small mistakes.

I don't understand what you do with FoundFile = right('$(FoundFile)', 8); and ['$(FoundFile)'\file1-'$(FoundFile)'].

Is this to deal with some file name filter?

In the LOAD statement you were missing some brackets around field names, and you had two spaces in [Product  number].

The code below should work to load all csv files, but if you have some further limitations of which files to load you need to state them.

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.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 SubDirectory in dirlist( Root & '\*' )

        call GetCSVFIleNames(SubDirectory);

    next SubDirectory

end sub

Call GetCSVFIleNames('C:\Temp');

[Anonymous]

Hi Benny,

Please try:

Loading data from multiple xls-files from folder with subfolder

[#8]

Hi neetha

This Load works. but iam facing 2 problems

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); if I keep this line..it takes always files from the source where the qvw file is placed.. it will not consider the H drive which I have given below. if I remove this H drive works.

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

Second problem is

H:\2014.csv\file1-H:\2014.csv' - I getting file not found error. in actual it should be 2014.csv\file1-2014.csv.

it should not reflect H Drive in the path.. it should only display the file name

[#9]

hi ralf

The reason for FoundFile = right('$(FoundFile)', 8); is iam looking folders specifically like 2014.csv and 2015.csv etc

and under each folder

for ex: 2014.csv >> contains

file1-2014.csv

file2-2014.csv

file3-2014.csv

that's the reason I have put as ['$(FoundFile)'\file1-'$(FoundFile)'] > so output will be 2014.csv\file-2014.csv

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

[Ralf-Narfeldt · Accepted solution]

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

[#11]

hi ralf, even this holds good..