Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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
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
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.
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
Hi jeffmartins,
Works great, thanks!