How to load multiple files n qlikview from FTP (source)?

[sakshikaul]

Hi

I want to load multiple files of material master in qlikview. and source is FTP. .here in below image you can see there are multiple files ie full load and some changed records are also kept in the same folder. so how to hamdle the same ?

[marcus_sommer]

Wildcard-loadings aren't possible with FTP. You need to use/create a full-filepath to the file by either duplicating your load-statement or creating them within a loop or you need to move all files to a local/network-storage (maybe with a batch). Here you will find some backgrounds and examples: For Each File in Filelist - FTP load is not working?

- Marcus

[sakshikaul]

Hi 

You mean to say like this ?

Material_Master_full:
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[ftp://ftpIPQ:ftpben16@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

concatenate(Material_Master_full)
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[ftp://ftpIPQ:ftpben16@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_20181130_000005.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

store Material_Master_full into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Material_Master_Full.qvd;
drop table Material_Master_full;

[marcus_sommer]

Yes, just duplicating the load-statements and concatenating the loads into a single table will work. By just a few loadings it's not very elegant but quite practically. If there are dozens or even more loadings then it would be useful to create some kind of loop or to move the files to a local storages - like hinted within the link above.

- Marcus

[sakshikaul]

Hi 

Thanks.I have multiple files which are created on daily basis on ftp so I think concatenating the files is not a feasible method to handle these files in qlik view. So , need to use for each loop in this case. I want syntax for the same as the above link is showing some error. Please help 

[marcus_sommer]

Unfortunately seems this posting currently not be accessable within the new community-release - therefore the link to the old one which I could still access. Here now some copy & paste to the parts I related for:

...
Loads per FTP couldn't have wildcards in path or filename. Also functions like filetime() and filelist() won't be supported from ftp-protocoll. But you could generate a full-path in a variable per algorithm or per in-listing. Alternatively you could use a triggered batch-file (execute statement) with e.g. mget --include "*.csv".
...

...
It will depend from the names of your files. If the names contain the date you could try like this:

for i = 1 to 14
     vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.csv';
     table:    
     Load * From $(vFileName) (txt, utf8, embedded labels, delimiter is '\t', msq);
next
...

...

You need always some logic to create a full-path. If this isn't possible or to complex you must transfer all files from ftp to a local or network storage and could then make normal loads with wildcards and so on. For this you need (a triggered) batch-file (per execute statement) with a statement like this:

open YOURSERVER
USER
PASSWORD

lcd "D:\YOURTARGETPATH"
mget --include "*.csv"
bye
exit

Type "ftp" and "batch" in google and you will find many informations and examples.
...

- Marcus

[sakshikaul]

Hi

what all changes do I have to make in the following syntax ?

for i = 1 to 14
     vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.csv';
     table:    
     Load * From $(vFileName) (txt, utf8, embedded labels, delimiter is '\t', msq);
next

This is my path and Location as per Ftp 

FROM
[ftp://userid:userpassword10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

[sakshikaul]

Hi

can you please elaborate more on this topic ?

I need to use for loop so that multiple files which are created on daily basis can be loaded multiple times from Ftp in qlikview 

so How to write and handle the code for the same?

[marcus_sommer]

Maybe like this:

set FTPServer = ftp://userid:userpassword@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_FULL;

for i = 1 to 14
     vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.txt';
     TRACE $(vFileName);
     // table: Load * From $(vFileName) (txt, codepage is 1252, embedded labels, delimiter is '|', msq);
next

At first comment the load and look on the returns of TRACE (in log-file and/or progress-window) to adjust/ensure that your logic really returns a valid filepath.

- Marcus

[sakshikaul]

Hi 

without loading a file its functioning

but when I uncomment the table (refer following) its giving an error. 

  table: Load * From $(vFileName) (txt, codepage is 1252, embedded labels, delimiter is '|', msq);