topic Re: Importing date value from Sharepoint in Connectivity & Data Prep

Importing date value from Sharepoint

rachel_delany — Fri, 13 Apr 2018 03:50:23 GMT

I am attempting to import some list data from Sharepoint, including a date value, through the REST connector.

I have successfully connected to the list and in the data preview all looks fine, however the dates all show as null values.

On data preview the dates appear as below. I have tried importing the date with the below formula with no luck:

Date(Date#([@Content], 'YYYY-MM-DDThh:mm:ssK') ,'YYYY-MM-DD') AS [@Content_u2]

Anyone come up against this before?

Re: Importing date value from Sharepoint

petter — Fri, 13 Apr 2018 04:57:06 GMT

The date coming from SharePoint is represented in a completely valid ISO-8601 time format that Qlik Sense is perfectly capable of auto interpreting. That is exactly what the data preview is doing and the reason it works as you noticed...

Strangely enough the format specifiers that Qlik accepts do not include the T or the final Z so any attempt to explicitly convert a perfectly valid ISO-8601 date with Date# will fail unless you strip the T and Z out first.

So just doing:

[@Content] AS aDate,

Should work well.

Re: Importing date value from Sharepoint

rachel_delany — Sun, 15 Apr 2018 22:37:21 GMT

That was what I had tried initially Petter with the same result.

I will try stripping out the T and Z first and see if that is successful.

Re: Importing date value from Sharepoint

rachel_delany — Thu, 19 Apr 2018 05:14:13 GMT

It turns out that QlikSense was developing a faulty load script.

When I tried it in QlikView it worked perfectly, and I moved the script over from there and it imported as expected.

Thanks for your help petter-s‌