topic How to get each files in a directory in QlikView

How to get each files in a directory

Tue, 07 Apr 2015 09:32:27 GMT

Hello,

Actually, I try to make a script to allow me to convert each XLSX in QVD.

FOR EACH File in FileList([ftp://AAA:VVV@11.111.11.111/DV/])

XLSX:

LOAD

FROM [ftp://AAA:VVV@11.111.11.111/DV/*.xlsx] (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

STORE XLSX into $(File).qvd(qvd);

next File

When I made it run there's any error but nothing appears too. I don't know if I use the "FOR EACH" well or not and the "FROM".

Then, if someone can explain my error.

Thanks

Regards

Re: How to get each files in a directory

prieper — Tue, 07 Apr 2015 15:19:09 GMT

How would the script look like for just one file? Using the wizard may then allow you to take the steps for an automation.

Am not sure, whether the datatype is appropriate - you mention as filextension "xlsx", but specify that it should be txt?

What does the error-message say - sometimes it gives already an hint, what might be wrong.

Peter

Re: How to get each files in a directory

Tue, 07 Apr 2015 15:39:49 GMT

Hi Peter,

My script for one file:

XLSX:

LOAD

FROM [ftp://AAA:VVV@11.111.11.111/DV/Data%20Entry.xlsx] (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

STORE XLSX into XLSX.qvd(qvd);

For the datatype, I have xlsx for Microsoft Excel

There's not error message, that's why I don't really know what I can do to fix that. (First script I sent to this post)

I think that my script doesn't read my FTP's address because with this script:

XLSX:

LOAD

FROM [ftp://AAA:VVV@11.111.11.111/DV/*.xlsx]

(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

STORE XLSX into XLSX.qvd(qvd);

I have this message: "Cannot open file"

Thanks for your help

Re: How to get each files in a directory

vvvvvvizard — Tue, 07 Apr 2015 16:58:40 GMT

store th directory on ur desktop and run script , this will tell u whether it is a ftp issue

Re: How to get each files in a directory

petter — Tue, 07 Apr 2015 18:38:49 GMT

I am pretty sure FileList( ) does not support FTP as filesystem. FileList() only enumerate files on a Windows regular filesystem.

and likewise

FROM

does not support FTP as a filesystem which can accept wildcards...

Re: How to get each files in a directory

prieper — Tue, 07 Apr 2015 18:39:04 GMT

If you stop the script before the STORE ....command,

do you really have valid data loaded?

typically you should load an Excel-file with params like

(ooxml, embedded labels, table is Sheet1);

Peter

Re: How to get each files in a directory

Wed, 08 Apr 2015 00:12:17 GMT

Hi Kenny,

I have some models using similar logic and your script looks fine with one exception.

As Peter mentioned, you need to specify a sheet in the workbook via the table parameter or else the script has nothing to process hence no results.

This means you need you to update the following section to be something like:

(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes, table is [My Sheet]);

Hope this helps.

Kind regards,

Rod

Re: How to get each files in a directory

Thu, 09 Apr 2015 07:44:33 GMT

I stop my script before the STORE nothing loaded and I changed my params.

As Petter Skjolden said, my script cannot read my Filelist() because of FTP.

Do you know another way to load excel files without Filelist or For each ?

Re: How to get each files in a directory

Thu, 09 Apr 2015 07:49:55 GMT

If Filelist() doesn't support FTP, do not exist another way to load?

Re: How to get each files in a directory

Thu, 09 Apr 2015 07:56:20 GMT

I think my script don't really read in my FTP, because when I run nothing load. Do you use Filelist on your models ?

Re: How to get each files in a directory

petter — Thu, 09 Apr 2015 14:34:38 GMT

Actually the FTP-support is so flimsy that I have used other FTP solutions to implement FTP operations. You have a built-in FTP client in Windows that can be used and can be triggered from a load script with the EXECUTE statement. There are other high-quality open-source clients too that you can use from a load script: wget and especially cURL. Unfortunately this comes at a "price" ... which is time, effort and IT-skills - not license money... But in the end you get a trustworthy FTP-solution. You can download a directory-listing ... parse this to do some FTP-transfers and it works both ways and with full logging.

The LOAD ... FROM [FTP://....] does not work against all types of FTP-servers out there ... I tried it the day before yesterday without any luck against a hosting-provider that runs Microsofts FTP-server - No luck... and then against a UNIX-based FTP-server where it works OK.... but as I said it is not something I use or trust.

Re: How to get each files in a directory

Thu, 09 Apr 2015 22:15:59 GMT

Hi Kenny,

Yes, I am using filelist.

There is an example on the Community where another contributor has used a two step process to load in data via FTP without using Filelist so this may be an option for you:

Re: For Each File in Filelist - FTP load is not working?

As Petter posted, FTP support is a bit hit and miss and is also dependant on the FTP version/platform you are trying to work with.

Hope this helps.

Kind regards,

Rod