topic Re: Extract csv files from multiple folders in QlikView

Extract csv files from multiple folders

Mon, 14 Sep 2015 19:10:53 GMT

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

Re: Extract csv files from multiple folders

Oleg_Troyansky — Mon, 14 Sep 2015 20:19:36 GMT

Search QV Help for the word "DoDir", and you'll see an example that does what you need.

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 09:05:53 GMT

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.

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 09:28:16 GMT

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

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Tue, 15 Sep 2015 09:36:03 GMT

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

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Tue, 15 Sep 2015 09:40:51 GMT

In dirlist and filelist you need to specify the path like filelist ('c:\mydata\*.csv' ) if they are in another folder

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 12:17:48 GMT

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

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 12:18:47 GMT

filelist ('d:\source\*.csv' ) and dirlist(''d:\source\*.csv' ' ) - Sorry its the same.

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Tue, 15 Sep 2015 13:45:39 GMT

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

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 15:08:10 GMT

is this needs to be with in the sub routine?

Re: Extract csv files from multiple folders

Anonymous — Tue, 15 Sep 2015 15:26:59 GMT

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.

Re: Extract csv files from multiple folders

Tue, 15 Sep 2015 21:18:32 GMT

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.

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Wed, 16 Sep 2015 06:50:47 GMT

Can you share the script?

Re: Extract csv files from multiple folders

Tue, 22 Sep 2015 06:15:09 GMT

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

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Tue, 22 Sep 2015 07:38:37 GMT

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

Re: Extract csv files from multiple folders

Anonymous — Tue, 22 Sep 2015 07:41:32 GMT

Hi Benny,

Please try:

Loading data from multiple xls-files from folder with subfolder

Re: Extract csv files from multiple folders

Tue, 22 Sep 2015 08:02:33 GMT

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

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

Re: Extract csv files from multiple folders

Tue, 22 Sep 2015 08:43:18 GMT

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

Re: Extract csv files from multiple folders

Ralf-Narfeldt — Tue, 22 Sep 2015 09:31:08 GMT

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

Re: Extract csv files from multiple folders

Thu, 24 Sep 2015 06:54:27 GMT

hi ralf, even this holds good..