topic Re: How to Download data from multiple files from sharepoint? in QlikView

How to Download data from multiple files from sharepoint?

Mon, 08 Dec 2014 14:34:45 GMT

Dear Experts,

I am a newbie to qlikview. I searched a lot in this forum but could not find what I was looking for.

Please advise.

I have a sharepoint where multiple CSV files are located.

Each CSV file is of exactly the same format.

I want to download the contents of these multiple files in a single QVD or one QVD for each file using a script.

Please let me know how to achieve this.

So far, I was successful in downloading the contents of one single file. But, I am unable to reciprocate it with multiple files using <<sharepoint path>>/*.csv.

Please note, I am referring to downloading from flat files uploaded to sharepoint, and NOT from sharepoint list.

Warm Regards,

Sanjeev Kulkarni

Re: How to Download data from multiple files from sharepoint?

arthur_dom — Mon, 08 Dec 2014 18:19:21 GMT

If you are using a directory you can load with something like

DATA:

Load * From [Path\]*.csv] (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: How to Download data from multiple files from sharepoint?

Wed, 10 Dec 2014 07:38:23 GMT

Hi Arthur,

Thank you for your inputs.

Let me add few more details.

The path in which my CSV files are present in the sharepoint is:

http://projects.xyz.com/sites/XYZProj/XYZReduction/XYZReports/Forms/AllItems.aspx

I am able to load the contents of just ONE file (File1) using this Code:

--------------------
LOAD *
FROM
[http://UserName:Password@projects.XYZ.com/sites/XYZProj/XYZReduction/XYZReports/File1.csv Password@projects.XYZ.com/sites/XYZProj/XYZReduction/XYZReports/File1.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

--------------------

However, I am unable to put the code in loop and download the contents of all the files in a single code.

Can you please help me with the code?

Re: How to Download data from multiple files from sharepoint?

arthur_dom — Wed, 10 Dec 2014 16:27:11 GMT

Hi.

Usually i use the UNC path( Windows SharePoint Services and UNC Document Access).... on that way you can do something like:

Load *

From

\\projects\sites\XYZProj\XYZReduction\XYZReports\*.csv

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

But, this only works if is in Intranets.

The kind of link you passed seems to me of a sharepoint accessed externally.... For do that problably you will need someone to create a list of files on that folder (How to quickly list documents and sub folders from a Document Library in SharePoint to a file | SharePointGeoff ) . If that list exists you can write:

ListOfFiles:

Load

rowno() as RowNumber,

Files

From <FileList> (format);

let vRows = noOfRows(ListOfFiles);

for i = 1 to vRows-1

let vFile = peek( 'Files', $(i) ) ;

$(vFile):

Load * from

[ht Password@projects.XYZ.com/sites/XYZProj/XYZReduction/XYZReports/$(vFile).csv]

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

next;

Re: How to Download data from multiple files from sharepoint?

Tue, 16 Dec 2014 10:57:27 GMT

Hi Arthur,

Even though this is an intranet site, the UNC path is not working. I don't know why.

On your 2nd solution:

I tried to create a list of files present in the path and tried to reload the contents and it worked!

However, there is a bottleneck with this solution.

The CSV files are generated on a daily basis. And I can't ask someone to create a list and save it daily. Is it possible to code and automate the list generation and use it in Qlikview?

Thanks in advance.

Sanjeev

Re: How to Download data from multiple files from sharepoint?

arthur_dom — Tue, 16 Dec 2014 17:35:10 GMT

hmmm i believe you can.

Try to create a list using autogenerate. Something like this. For the script below i user the report.csv like Report_AAAAMMDD.csv, First date in 2013 january first, last date today.

let varDateBase = num(makedate(2012,12,31) ) ;

let varDays = today() - $(varDateBase) ;

ListBase:

Load date($(varDateBase)+ rowno()) as Date,

'Report_'& (date($(varDateBase)+ rowno(),'YYYYMMDD'))&'.csv' AS File

AutoGenerate $(varDays) ;

Then you can use the list 😃

Re: How to Download data from multiple files from sharepoint?

Fri, 19 Dec 2014 06:03:17 GMT

Hi Arthur,

Is there a way to automate this in Qlikview?

How to quickly list documents and sub folders from a Document Library in SharePoint to a file | SharePointGeoff

Re: How to Download data from multiple files from sharepoint?

arthur_dom — Fri, 19 Dec 2014 16:55:35 GMT

I believe ( if you have access to SHAREPOINT server) it can have a batch file creating the list daily. If the QlikView server is in the same network you can try using it with windows powershell...