topic Re: connection with sharepoint/share drive- appending the data on addition of new file. in App Development

connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Thu, 07 Jan 2021 17:50:47 GMT

Hello,

once the connection is established with share point and share drive . Can a script be written to schedule to append the data when new file is added in the share drive /share point .

So basically the upload should happen automatically when there is new file in share drive or share point.

Re: connection with sharepoint/share drive- appending the data on addition of new file.

stevedark — Fri, 08 Jan 2021 11:13:50 GMT

Hi @meenakshi_m

You would need to have a process that frequently polls for new data and then appends that to a QVD if found. Out of the box you would need to reload the incrementally loaded QVD after every check - whether it changed or not. This is not ideal.

It should be possible to trigger a load only if new data are found - but this is a chunk of work and is not supported natively by Sense.

A kludgy way of doing it would be to have the incremental part of the load fail if no new data are found and then only have the front end load happen on successful reload of the QVD generator.

This post may give you some further ideas:
https://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Steve

Re: connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Sat, 09 Jan 2021 20:10:51 GMT

Hi Steve,

Thanks for the solution this worked. But facing issue with Date format on the converted QVD files while evaluating some filters on past data

Have tried date format but is not helping .can you please show where to exactly include the date format in you incremental code.

Appreciate your help!Thanks.

Re: connection with sharepoint/share drive- appending the data on addition of new file.

stevedark — Sun, 10 Jan 2021 12:04:44 GMT

Hi @meenakshi_m

Is that a date field within the file you are loading or the date of the files you are loading?

What file format is it that you are loading from SharePoint?

How does the date look in a Filter Pane if you don't do anything to it when you load?

If it looks like a date, but sorts incorrectly and messes up when you try and break it down you probably need to do a date# on it to convert it to a numeric, and then a date function to format it. This should happen in the load;

DateField as RawDate,
Date(Date#(DateFIeld, 'YYYYMMDD', 'DD MMM YYYY') as Date,

You can then do a preceding load on this to put that date into Months and Year etc.

Hope that helps.

Steve

Re: connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Wed, 13 Jan 2021 16:33:15 GMT

hi Steve,

Thanks this has helped.all these works fine when connecting to shared drive.

But how do I set the same code to work on share point.

Could you please help if there is any need change in code which need to be done while connecting to share point.

appreciate your help.

Re: connection with sharepoint/share drive- appending the data on addition of new file.

stevedark — Thu, 14 Jan 2021 19:38:51 GMT

Hi @meenakshi_m

Loading from SharePoint is a different topic, and how you go about it depends on what version of Qlik you are using, whether it is Sense on Prem, Sense SaaS or QlikView.

You will probably want to be using a connector which will download a list of files on the SharePoint server into a table. This will take the place of for each vFile in FileList. To enumerate around a table you need to do for iFile = 0 to NoOfRows('TableName') -1 and then use the PEEK function to get all of the values from the table.

For a time it was not possible to load the files directly from SharePoint, but I believe it now is. I've been involved in some threads on this in Community, but I can't seem to find any now. Suggest searching the forums to see if someone has a solution.

Connecting with other cloud file sharing services is not a problem. I've blogged on this here:
https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

Hope you can get the SharePoint connection working.

Cheers,
Steve

Re: connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Wed, 20 Jan 2021 09:21:47 GMT

Hi Steve

Appreciate your respond on same.Have been trying to find but did not get any satisfactory solution .So the requirement is there are files uploaded in share point 2016 during different days of month .And these files should be updated in my Qlik sense app.

what connector is required to download the list of files on the share point server into a table. How and Where this is done.Could you please provide more details with steps required. Thank you

Meenakshi Mantri

Re: connection with sharepoint/share drive- appending the data on addition of new file.

stevedark — Wed, 20 Jan 2021 12:36:33 GMT

Sure. Which flavour of Sense are you using? Enterprise, SaaS or Desktop?

Re: connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Thu, 21 Jan 2021 10:19:14 GMT

Hi Steve,

Thanks for your help.This is Enterprise Version.

I am to connect to share point and retrieve 1 XLSX file .I understand that I need to get the list of files present in share point and then create a loop. But I am not aware on how to get the list of files from share point dynamically.(list should be updated when new file is added)

Re: connection with sharepoint/share drive- appending the data on addition of new file.

meenakshi_m — Mon, 25 Jan 2021 18:14:17 GMT

Hello Steve dark,

Any luck please.

Re: connection with sharepoint/share drive- appending the data on addition of new file.

stevedark — Tue, 26 Jan 2021 08:08:28 GMT

Hi @meenakshi_m

You need to have Qlik Web Connectors installed, either on the Enterprise Sense server or another in the same domain (there is more config if you go for another machine).

Notes on QWC can be found here:
https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Setup-deploy/Qlik_Web_Connectors_package.htm

And the latest download can be found in the Downloads part of qlik.com, or the current one (as of now) is:
https://da3hntz84uekx.cloudfront.net/WebConnectors/2.98.1/0/_MSI/QlikWebConnectorsDecember2020.zip

You need to follow the installation instructions carefully. Two easily missed gotchas are ensuring you unblock the zip file and that you run the service installation batch file using "run as Administrator".

Go to https://servername:5555/ to get to the QWC web interface. You will need to accept the licence agreements and go to the Licence section and enter your Qlik Sense Enterprise licence details. Note that this requires that this doesn't work with the old token based licences and you may need to get your licence converted to do this bit.

Find the Office 365 Sharepoint connector under the premium connectors.

Here you have a number of different ways of connecting. First you need to authenticate with your SharePoint site though.

Once that is done you should be able to use ListFolders, ListFiles and GetFile to retrieve files from the server. Do this manually at first, entering all of the required parameters into the web form.

Once you have this working you can go to the Qlik Sense (Standard Mode) to get the code to run, there are important instructions on this sheet on how to set this up.

You will need to do a List Files into a table and then enumerate around it to get each of the IDs to pass into the Get File function. The loop will be something like:

for iFile = 0 to NoOfRows('FileList') -1
sFileID = peek('FileID', iFile, 'FileList');

// Get file
next

For more info you might want to read:
https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/
https://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Hope that helps.

Steve