topic Re: Load script taking ages in QlikView

Load script taking ages

Fri, 31 Jul 2015 11:47:57 GMT

Dear Qlikviewers,

I have written a load script that should load all filenames in all the subfolders. I believe there should be approximately 600 csv files in all of the subfolders. I believe this script should do the job. However, the load script seems to be running for ages. The script has been running for an hour and is still going. Is there anything about my script that could be altered to make it run more efficiently?

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

sub GetCSVFIleNames(TRD)

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

FileList:

Load

filename() as [FilenameWithPath]

Autogenerate(1);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFileNames('L:\my\file\location\');

Re: Load script taking ages

sorrakis01 — Fri, 31 Jul 2015 11:55:04 GMT

Hi,

At first time don't forget to insert it in a qvd.

Regards

Re: Load script taking ages

Kushal_Chawda — Fri, 31 Jul 2015 12:00:26 GMT

sub GetCSVFIleNames(TRD)

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

FileList:

First 1 Load

filename() as [FilenameWithPath]

Autogenerate(1);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFileNames('L:\my\file\location\');

Re: Load script taking ages

puttemans — Fri, 31 Jul 2015 12:04:48 GMT

This looks ok. Beware that you not only fetch data, but also rename variables in the same go. This will take somewhat longer.

If it can help you, I sometimes have QV-routines running for 3 days. These then involve the lecture & selection of variables within up to 80.000 txt files.

Re: Load script taking ages

rubenmarin — Fri, 31 Jul 2015 12:05:11 GMT

Hi Martijn, maybe changing "root" to "TRD" (the parameter)

sub GetCSVFIleNames(TRD)

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

FileList:

Load

filename() as [FilenameWithPath]

Autogenerate(1);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFileNames('L:\my\file\location\');

Re: Load script taking ages

sasiparupudi1 — Fri, 31 Jul 2015 12:06:48 GMT

Hi your input variable differs

sub GetCSVFIleNames(root)

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

FileList:

Load

filename() as [FilenameWithPath]

Autogenerate(1);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Re: Load script taking ages

Kushal_Chawda — Fri, 31 Jul 2015 12:09:13 GMT

Are all CSVs having Same name & Same Field name in sheet?

Re: Load script taking ages

sasiparupudi1 — Fri, 31 Jul 2015 12:10:35 GMT

sub GetCSVFIleNames(TRD)

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

FileList:

Load

'$(FoundFile)' as [FilenameWithPath]

Autogenerate(1);

next FoundFile

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

call GetCSVFIleNames(SubDirectory)

next SubDirectory

end sub

Call GetCSVFileNames('L:\my\file\location\');

Re: Load script taking ages

Kushal_Chawda — Sat, 01 Aug 2015 17:24:57 GMT

any luck?

Re: Load script taking ages

Digvijay_Singh — Sat, 01 Aug 2015 19:07:32 GMT

Below script also works perfectly, you can modify as per your need. This one loads all files and subfolder information with Q extension.Source - QV developer cookbook.

Sub GetFiles(vPath)

// Get a list of files with Q extensions

For each vFile in FileList('$(vPath)\*.Q*')

Files:

Load

'$(vPath)' as Folder,

'$(vFile)' as File,

FileSize('$(vFile)') as FileSize,

FileTime('$(vFile)') as FileTime

AutoGenerate(1);

End Sub

Sub GetSubFolders(vPath)

For each vDir in DirList('$(vPath)\*')

Folders:

Load '$(vDir)' as Folder

AutoGenerate(1);

Call GetFiles('$(vDir)');

//Recurse to get sub folders;

Call GetSubFolders('$(vDir)');

End Sub

Call GetFiles ('C:\Program Files\QlikView');

Call GetSubFolders('C:\Program Files\QlikView');