topic Re: How to Download an Excel sheet from Microsoft Teams Files Location in Connectivity & Data Prep

How to Download an Excel sheet from Microsoft Teams Files Location

rohitk1609 — Fri, 02 Apr 2021 09:01:47 GMT

Hi Techies,

I need to download an excel sheet which is presented at Microsoft Teams Files location. There is an option which gives two paths

Microsoft Teams

https://teams.microsoft.com/l/file/A13BD6C7?tenantId=-81a1-9e8ae3459641&fileType=xlsx&objectUrl=https%3A%2F%2Fapps.jci.com%2Fsites%2F%2FShared%20Documents%2FGeneral%2F2020-05-13%20Client%20Migrations%20Progress%20Report2.xlsx&baseUrl=https%3A%2F%2Fapps.***.com%2Fsites%2FCTWAA&serviceName=teams&threadId=19:029@thread.tacv2&groupId=4bc

2. Sharepoint

https://apps.***.com/sites/****/_layouts/15/WopiFrame2.aspx?sourcedoc={70-0a120829ecae}&action=edit

One top right corner, there are three dots, when I click on it, it gives me two options of link which I stated above. Can you please guide me which connector works for this scenario. I have Nov 2017 Web connector too.

Thanks,

Rohit

Re: How to Download an Excel sheet from Microsoft Teams Files Location

fosuzuki — Wed, 26 Aug 2020 14:13:37 GMT

Hi Rohit,

As far as I know, the MS Teams files are stored in Sharepoint. So you can use the Qlik Office 365 Sharepoint connector.

Re: How to Download an Excel sheet from Microsoft Teams Files Location

rohitk1609 — Thu, 27 Aug 2020 11:16:02 GMT

Hi Fosuzuki,

We don't have Office 365 Share point connector. Is there any other way we can do it?

Thanks,

Rohit

Re: How to Download an Excel sheet from Microsoft Teams Files Location

chrisbrain — Thu, 27 Aug 2020 11:25:57 GMT

Hi - Are you using the Qlik Web Connectors Nov 2017 release then? Can't you update to the latest version and try the Office 365 Sharepoint connector?

Re: How to Download an Excel sheet from Microsoft Teams Files Location

male_carrasco — Sat, 29 Aug 2020 13:28:31 GMT

Hi @fosuzuki ,

I have the Qlik Office 365 Sharepoint connector, however, I tried to set up to found the file and I can't.

Do you know how to obtain the file?

Thank you in advance.

ps: Thank @rohitk1609 for your post.

Re: How to Download an Excel sheet from Microsoft Teams Files Location

rohitk1609 — Mon, 31 Aug 2020 20:14:33 GMT

I can't update it because we are using MongoDB connector also which would be depreciated in latest release.

Re: How to Download an Excel sheet from Microsoft Teams Files Location

fosuzuki — Thu, 03 Sep 2020 19:35:44 GMT

Hello @male_carrasco ,

I'd recommend you to use the ListFolders together with the ListFiles to look around for other folders/subfolders.

Are you using WebConnectors with QlikView or Qlik Sense?

Re: How to Download an Excel sheet from Microsoft Teams Files Location

rohitk1609 — Thu, 03 Sep 2020 19:46:48 GMT

Can you please explain me in detail step wise "use the ListFolders together with the ListFiles"?

I am using Web connector Nov 2017 with Qlik Sense

Re: How to Download an Excel sheet from Microsoft Teams Files Location

male_carrasco — Thu, 03 Sep 2020 20:26:05 GMT

Thank @fosuzuki for your reply!
I'm using Sense. I solved it! but now, I have a new challenge.
I found the files, but I'm trying to loading data from the files. Every month I will load a new file from the folder (Same format, fields).
If you know how to read the data from several files I will appreciate a clue.

Best Regards

Re: How to Download an Excel sheet from Microsoft Teams Files Location

fosuzuki — Thu, 03 Sep 2020 20:59:15 GMT

Nice!

Now you need to use the GetFile function, and input the UniqueId for the file you need.

Re: How to Download an Excel sheet from Microsoft Teams Files Location

fosuzuki — Thu, 03 Sep 2020 21:01:02 GMT

I meant that you should start with ListFolders to get a list of folders. Then, take one of these folders, and use it with ListFiles to get the files from this folder.

And then iterate through this process until you find the file you need.

Re: How to Download an Excel sheet from Microsoft Teams Files Location

male_carrasco — Thu, 03 Sep 2020 21:34:31 GMT

Yes, I did but I will load a new file every month, so I will get a new Unique Id for every file, so every month I will have to add it to my script.
Is it possible to automate it on the script only one time?

I started with this:

But It's not working...

Re: How to Download an Excel sheet from Microsoft Teams Files Location

stevedark — Thu, 03 Sep 2020 22:27:05 GMT

Hi @male_carrasco

It doesn't address Teams and Excel, but you might get some ideas for what you need to do from this blog post:

https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

If you are only interested in that one file you should be able to get the Id without doing the loop, by adding a WHERE statement to the first table. You may be able to do a filter on the initial load, but if not you can do it on a preceding load.

Id_Table:
LOAD
*
WHERE Match(Name, '*Filename.xlsx')
;
LOAD
Text(UniqueId) as UniqeId,
etc...

When you say that it's not working, is the variable v3rdWorkday not containing the ID, or is it another issue?

I think what the issue might be is that you are missing single quotes around $(vFile), so the match line should start:

if Match('$(vFile)', '*3rd Workday etc.

Hope that helps.

Steve

Re: How to Download an Excel sheet from Microsoft Teams Files Location

male_carrasco — Fri, 04 Sep 2020 01:42:40 GMT

Hi @stevedark

Thanks for your help!

I did some steps but I can't load the files.
1) I loaded the list files from Teams.

2) I tried to load the files

3) but it has an error.

Do you have any advice?

Regards!

Re: How to Download an Excel sheet from Microsoft Teams Files Location

stevedark — Fri, 04 Sep 2020 06:58:50 GMT

Hi @male_carrasco

In order to be able to run a LOAD directly from a URL you would need to have your server switched to legacy mode in QMC. If this is not set then you will get an error message saying that you can only load from libraries. I'm guessing that is set correctly to allow you to load direct from URLs?

You can always test QWC statements by copying and pasting the URL into a browser. If you can't copy it from the progress dialog then you should be able to find it in the log file for the app reload, or store it to a text file like this:

tmpConn: LOAD 'https://qwc.server:5555/etc.etc.' as Connection AUTOGENERATE(1); STORE tmpConn INTO [lib://mylibrary/QWCURL.txt] (txt); DROP TABLE tmpConn;

When you paste the URL into your browser you will either get some data or an error message. That error message could be very useful. Depending on your QWC settings you may need to RDP to the QWC server to get a full message, as by default it gives out generic messages to calls from remote machines.

I don't think that the data that comes back from that stream will be a QVX, I think it is more likely to be a binary stream of that Excel file. In which case, the format specifier would not be (qvx) on the end, rather the same (ooxml, etc.) that you have when loading the spreadsheet from a file connection. If when you paste the URL into your browser it downloads the Excel file this is what you will need to use.

Hopefully one of those things will point you in the right direction a little.

Good luck!

Re: How to Download an Excel sheet from Microsoft Teams Files Location

male_carrasco — Fri, 04 Sep 2020 14:35:08 GMT

Thanks a lot for your help @stevedark

I did it! but on QLIK 😁😍

On Sense, I can't 😩

Re: How to Download an Excel sheet from Microsoft Teams Files Location

stevedark — Fri, 04 Sep 2020 14:08:09 GMT

Hi @male_carrasco

That being the case you have two choices, one is to put Sense into Legacy mode. This will allow you to run script that works on QlikView against Sense. This is not the recommended route.

The other way is that you create a generic Get connection to somewhere random (e.g. https://jsonplaceholder.typicode.com/posts) and then use the WITH CONNECTION statement to replace the URL with the one you have constructed from variables, like this:

WITH CONNECTION ( URL "http://localhost:5555/etc.&ID=$(vID)&etc." );

Details on WITH CONNECTION are here:
https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-REST-data/Load-data.htm#WITH-CONNECTION-keyword

A blog post (on a different topic) that explains it and shows it in use is here:
https://www.quickintelligence.co.uk/reading-rss-feeds-with-qlik-rest-connector/

Hope that allows you to get it created in Sense.

If you have access to both, there is always the cheating method of having QlikView load the data from source, then write to QVD and then have Sense pick up from there.

Steve