Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Specialist III
Specialist III

How to Download an Excel sheet from Microsoft Teams Files Location

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

  1. Microsoft Teams

https://teams.microsoft.com/l/file/A13BD6C7?tenantId=-81a1-9e8ae3459641&fileType=xlsx&objectUrl=http...

2. Sharepoint

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

teams.png

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

16 Replies
fosuzuki
Partner
Partner

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.

male_carrasco
Creator
Creator

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:

Capture3.PNG
But It's not working...

stevedark
Luminary Alumni
Luminary Alumni

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

male_carrasco
Creator
Creator

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.

Capture4.PNG

2) I tried to load the files
Capture5.PNG

3) but it has an error.

Capture6.PNG

Do you have any advice?

Regards!

 

stevedark
Luminary Alumni
Luminary Alumni

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!

male_carrasco
Creator
Creator

Thanks a lot for your help @stevedark 

I did it! but on QLIK 😁😍

On Sense, I can't 😩

stevedark
Luminary Alumni
Luminary Alumni

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-R...

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