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

[#2]

hi ralf, iqm facing one issue here . if iqm trying to loqd the same file twice in different load. its not considering the second

[Ralf-Narfeldt]

How do you mean it's not considering the second?

If you are loading with same field names, the files will auto-concatenate, even if you set another table label.

For example:

Table1:

Load Field1,Field2 from file1.csv;

Table2:

Load Field1,Field2 from file1.csv;

In this case Table2 will not be created, the data will be loaded a second time into Table1.

You can do this:

Table1:

Load Field1,Field2 from file1.csv;

Table2:

Noconcatenate Load Field1,Field2 from file1.csv;

[#4]

for example

sub GetFiles(Root)

    for each FoundFile in filelist( Root & '\file1*.csv')

        TEMP:
LOAD *
FROM
[$(FoundFile)]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines)
WHERE some ..condition;

    next FoundFile

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

here only the TEMP Table get the data and customer table does not filled with the data. if I interchange Customer table first. TEMP table does not get filled

[Ralf-Narfeldt]

If [Customer Name],[First Name],[Address] are the only fields in the file, these two loads are equivalent and result in the concatenation I explained about.

Temp:

LOAD * FROM  [$(FoundFile)]...

Customer:

LOAD [Customer Name], [First Name], [Address] FROM '$(FoundFile)'...

The result of the second load is that the data is loaded into Temp.

One way to prevent concatenation is to use the NOCONCATENATE prefix I showed you.

Another is to do a rename like [Customer Name] AS CustomerName in the second load.

Depends on what you want to achieve.

You can read some about concatenation in the Qlik Sense help, this info is valid for QlikView as well:

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/LoadData/concatenate-tables.h...

[#6]

hi ralf,

this is the scenario

So I require file1 firist time to calculate and then again to load it. how can achieve this

im loading file1 first time to calculate certain

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

       let vTmpFoundFile=SubField(FoundFile,'\',-1);

//if filename contains file1

f Index('$(FoundFile)','file1-')>0 then

temp:

LOAD *

    from

['$(FoundFile)']

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

if(FieldNumber(indexrate', 'TEMP')>0) then
set rating ="[broarange)]";
set ratingfactor= 11;
end if

Elseif Index('$(FoundFile)','file1-')>0 then

   Customer:

LOAD

Customer Name],

    [First Name],

     [Address] as [Address]

ratingfactor *  ----- I will do certain calculations.

   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

[Ralf-Narfeldt]

Row 5: (should be If I guess)

f Index('$(FoundFile)','file1-')>0 then

Row 18:

Elseif Index('$(FoundFile)','file1-')>0 then

This is the same condition, what's the purpose of that? The ElseIf is redundant when they have the same condition.

You commonly use it:

If <condition>

do this

ElseIf <anothercondition>

do something else

Endif

Do you create any new fields when you do the rating factor calculation? If you do, they should not concatenate.

It's very hard to help when you cut and paste code, and leave out things.

[#8]

yes a newfield is created

[#9]

when I say newfield something like ratingfactor * calculationbs as [total field]