Return the count of lines in each file

[#1]

Hi all,

I have the below codes in qlikview:

sub GetCSVFIleNames(Root)
    for each FoundFile in filelist( Root & '\*.csv')
        FileList:
            Load 
                '$(FoundFile)'          as [FilenameWithPath]

                 Autogenerate(1);
    next FoundFile
    for each SubDirectory in dirlist( Root & '\*' )
        call GetCSVFIleNames(SubDirectory)
    next SubDirectory
end sub
 
Call GetCSVFIleNames('W:\Directory\Folder\Subfolder')

This code searches the path W:\Directory\Folder\Subfolder\ and all subfolders underneath and returns a list of all the CSV files (including path) it can find in a column.

How do I add another column that returns the number of lines in each of the CSV file?

Thanks in advance.

[jeffmartins]

Hi patsys50,

You can get the number of rows in each CSV file with the following code:

Sub GetCSVFIleNames(Root)

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

        FileList:

        Load '$(FoundFile)'  as [FilenameWithPath],

                   sum(1)        as [NoOfRows]

        From '$(FoundFile)'

        (txt, utf8, embedded labels, delimiter is ',', msq);   // this line can be different depending on your csv file

    next FoundFile

    for each SubDirectory in dirlist( Root & '\*' )

        call GetCSVFIleNames(SubDirectory)

    next SubDirectory

End Sub

Call GetCSVFIleNames('W:\Directory\Folder\Subfolder')

Hope this helps you.

Regards

[#3]

Hi jeffmartins

Thanks for your post.

I copied and pasted your suggestion but I got the following error message:

Syntax error, missing/misplaced FROM:
FileList:

            Load 'W:\Directory\Folder\Subfolder'\File1.csv' as [FilenameWithPath]

                sum(1) as [NoOfRows]

         From 'W:\Directory\Folder\Subfolder'\File1.csv'
 
          (txt, utf8, embedded labels, delimiter is ';', msq)
FileList:

            Load 'W:\Directory\Folder\Subfolder'\File1.csv' as [FilenameWithPath]

                sum(1) as [NoOfRows]

         From 'W:\Directory\Folder\Subfolder'\File1.csv'
 
          (txt, utf8, embedded labels, delimiter is ';', msq)

I changed the delimited to ; instead of ,

Any idea what is causing the error?

Thanks again.

[#4]

Hi jeffmartins,

Sorry, please disregard my previous post.

Your code was actually running.

The only problem is that the field NoOfRows all showed 1 as value.

Any idea on t his?

Thanks

[jeffmartins]

Hi patsys50,

The attached file contains the test code I've used in order to solve this problem.

The code is working fine for me. Extract the file and reload the qvw.

Hope this helps you.

Regards

[#6]

Hi jeffmartins,

I forgot to mention, I am using personal edition qlikview - I could not open qv files created by other users.

Will you kindly post the codes here instead?

Thanks in advance.

[jeffmartins · Accepted solution]

Hi patsys50,

Create a new qvw app with the following code:

sub GetCSVFIleNames(Root)

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

        FileList:

        Load '$(FoundFile)'  as [FilenameWithPath],

                   sum(1)          as [NoOfRows]

        From '$(FoundFile)' (txt, utf8, embedded labels, delimiter is ',', msq);  

    next FoundFile

    for each SubDirectory in dirlist( Root & '\*' )

        call GetCSVFIleNames(SubDirectory)

    next SubDirectory

end sub

//Call GetCSVFIleNames('W:\Directory\Folder\Subfolder')

Call GetCSVFIleNames('.')

Extract the zip file and put the folder "test" and the new qvw on the same folder then reload the qvw app.

Regards

[#8]

Hi jeffmartins,

Works great, thanks!