topic How to get all files into a Web Folder in QlikView

How to get all files into a Web Folder

brice_roselier — Tue, 11 Dec 2018 16:44:09 GMT

Hello,

Sorry if the question has been asked already, but I didn't found.

I need to gel all XML file placed into a folder on a web server.

I can get one file when I create a File Web connexion as data source, but I can't select all files like path/*.xml

What could be a solution please ?

I use Qlik sense February 2018

Thanks in advance

Re: How to get all files into a Web Folder

Gysbert_Wassenaar — Tue, 11 Dec 2018 16:53:00 GMT

You can't solve this on the Qlikview side. The server that hosts the files will either need to allow seeing the index of that folder, i.e. a list of all the files. Or it will need to host a file there that contains a list of all the files. In both cases Qlikview will then first need to get that list of files. Only then can it iterate through that list to retrieve all the files.

Re: How to get all files into a Web Folder

brice_roselier — Wed, 12 Dec 2018 13:51:53 GMT

Thanks a lot,

I will generate a file containing all files to load.

Do you have a link (tuto or discussion) who explain how to load files content from a list ?

Thanks

Re: How to get all files into a Web Folder

Gysbert_Wassenaar — Thu, 13 Dec 2018 17:26:53 GMT

First load the file with the list of files into a table. Suppose you have file called fileslist.txt with this content:

file1.csv
file2.csv
file3.csv

You can load that data into a table called Files:

Files:
LOAD @1 as Filename FROM filelist.txt;

Then you can use a for next loop to iterate through the records of the table to retrieve each filename and then load the data from the files:

// create an empty table to which we can append all the data
Data: LOAD '' as SourceFile Autogenerate 0;

For i = 0 to NoOfRows('Files') - 1  

    LET vFileName = Peek('Filename', $(i), 'Files');

    CONCATENATE (Data)
    LOAD 
        *, 
        $(vFileName) as SourceFile
    FROM
        [http://myserver.com/$(vFileName)] (txt, delimiter is ',')
        ;

Next

Re: How to get all files into a Web Folder

brice_roselier — Mon, 17 Dec 2018 09:15:31 GMT

Thank you Gysbert_Wassenaar,

I have adapted the script for my context, but it doesn't work for me. May be because of my data come from xml files and not csv ?

Ok to create an empty table and loop :

The file containing list of the files to load is on the server too. I created a connection from file Web :

files: 
LOAD
    @1 as name
FROM [lib://index_result_tif]
(txt, codepage is 28591, no labels, delimiter is spaces, msq);

tif_results: 
LOAD 
	'' as SourceFile Autogenerate 0;

For i = 0 to NoOfRows('files') - 1  

    LET vFileName = Peek('name', $(i), 'files');

  /*  CONCATENATE (tif_results)*/
    /*LOAD 
      
        $(vFileName) as SourceFile
    FROM
        [http://10.1.11.30/$(vFileName)] (txt, delimiter is ',')
        ;
*/
Next

When I try to load data from server (only for the moment), I get :

L'erreur suivante s'est produite:
This statement only works with lib:// paths in this script mode
Emplacement de l'erreur:
LOAD 
      
        detail_cm_linux_postgresql_chrome.xml as SourceFile
    FROM
        [http://10.1.11.30/qualite/tpta/tests/tif/last/detail_cm_linux_postgresql_chrome.xml] (txt, delimiter is ',')

Qlik sense could not load data because of file on server is not a librairy file.

Thank you

Re: How to get all files into a Web Folder

Gysbert_Wassenaar — Mon, 17 Dec 2018 09:45:59 GMT

Ok, two problems. First of you're not using Qlikview although you posted this question in the Qlikview section. In Qlik Sense you can't reference files directly but you need to use a library connection. So you'll have to create such a library connection to one of the xml files. You can the reuse that to get the other xml files using the URL IS option to change the url dynamically. See the example on this page: https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/DataSource/load-data-from-files.htm#Web

And you can't load xml files as csv files. Create a load statement for a single xml file first you you know how to load the data from the xml files. Then use that load statement in the for next loop and change the bits that need to change, like the URL IS option.