topic Re: FTP data import into script in QlikView

FTP data import into script

felcar2013 — Thu, 23 May 2013 15:54:09 GMT

i need to import several zipped CSV files from a FTP Server into the script directly. Do you know how to do this?

there is an option, but i never used it (i also have no FTP Server connection yet, so i never tried)

thanks

felipe

Re: FTP data import into script

Gysbert_Wassenaar — Fri, 24 May 2013 08:43:43 GMT

Qlikview can't read zipped csv files. You either need to store the csv files uncompressed on the ftp server or you have to use an external tool like curl or wget to download the zip files, then use a script to unzip the files. Then Qlikview can load the uncompressed csv files.

edit: enable the Use FTP option under Data from Files to retrieve files via ftp

Re: FTP data import into script

felcar2013 — Wed, 05 Jun 2013 16:05:07 GMT

thanks

the files are comming unzipped, we changed this.

using the ftp option from QV, is it possible to get access to unzipped files located in a ftp server and to bring them to the QV Application? Or do i need a macro, what i also have.

I cannot try now with accessing directly the ftp server since i need some credentials, which i do not have, but just wanted to know

thanks

felipe

Re: FTP data import into script

marcus_sommer — Thu, 06 Jun 2013 08:56:38 GMT

Try this: (with loop because wildcards don't work per ftp-load)

set ftp = 'ftp://USER:PASSWORD@YOURSERVER.com/';PASSWORD@YOURSERVER.com/';

set datafile = 'YOURFILE';

set ext = '.csv';

set typCSV = "(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes)";

for i = 0 to $(vCounter)

let vZeitraum = date(AddMonths('01.02.2012', $(i)), 'YYYY_MM');

table:

Load *

FROM $(ftp)$(datafile)$(vZeitraum)$(ext)$(typCSV);

- Marcus

Re: FTP data import into script

felcar2013 — Thu, 06 Jun 2013 10:51:40 GMT

thanks for the script, the vCounter should be the number of my datafiles in the ftp server, right?

Re: FTP data import into script

marcus_sommer — Thu, 06 Jun 2013 11:04:37 GMT

Yes, a simple counter:

let vCounter = ((year(today() - 1) - year('01.02.2012')) * 12) + (month(today() - 1) - month('01.02.2012'))

to generate a YearMonth-stamp for the loop.

- Marcus

Re: FTP data import into script

Anonymous — Tue, 25 Feb 2014 01:17:12 GMT

how can i create loop for every day? i want to load for everyday.. file is generated for every 1 min; deleted for 15days..

Re: FTP data import into script

Tue, 25 Feb 2014 04:24:57 GMT

What is the sample file name format

Re: FTP data import into script

Anonymous — Tue, 25 Feb 2014 04:31:31 GMT

csv 's

Re: FTP data import into script

marcus_sommer — Tue, 25 Feb 2014 08:02:59 GMT

see: For Each File in Filelist - FTP load is not wor... | Qlik Community

- Marcus

Re: FTP data import into script

Tue, 25 Feb 2014 13:16:01 GMT

File Name Format ? As you mentioned the new file was created every min.

The File name like: FILENAME_2014-2-25 06.01.csv ?

Re: FTP data import into script

Anonymous — Tue, 25 Feb 2014 19:47:24 GMT

csv 's with

Filename_05_30_02_02_12_2014 is like Filename_hh_mm_ss_MM_DD_YYYY

Re: FTP data import into script

Tue, 25 Feb 2014 20:39:17 GMT

The filenames are consistent on the minute like below:

FILENAME_05_30_02_02_12_2014

FILENAME_05_31_02_02_12_2014

FILENAME_05_32_02_02_12_2014

FILENAME_05_33_02_02_12_2014

Then create the multi inner loop on Hour & Minute like below:

LET vToday = Date(Today() , 'MM_DD_YYYY') ;

For vHour = 01 to 24

For vMin= 1 to 60

LET vFileName = 'FILENAME_$(vHour)_$(vMin)_02_$(vToday)';

LOAD * FROM $(vFileName).csv () ;

Next vMin

Next vHour

If you have any probelem, please open new thread with 5 filenames as reference.

Re: FTP data import into script

pandiarajan — Tue, 01 Apr 2014 05:56:45 GMT

Kindly Share any sample qvw for this example