Connect AI - How to open and return data from a specific csv file saved in a sharepoint folder?

Question

Hi Community!I’m trying to create a source to sharepoint where we can navigate to a specific file saved on the sharepoint location and open the contents of a csv or xlsm file. Is this possible? I have the sharepoint connection created using AzureAD authentication method.	Using a query such as this: 	SELECT * FROM [Sharepoint_testing].[REST].[Financial Models] where Name like '%.xlsm%' I can get the various file names contained in the Sharepoint location, and see things like file size, modify date, etc.	At this point, I can’t figure out how to use the file name retrieved and then query it to get at the contents of it… any help with this?	 TIA for the assist, i appreciate it.

Jerod · Accepted Answer

Hi ​@jfuhrman!The Excel Online (Excel for the web) source connector will allow you to access data from within Excel files. You need to create an ExcelOnline connection in Connect AI, making sure to set theSharepoint URL property to connect to Excel files on a specific SharePoint site. Leave this field blank to connect to Excel files on your OneDrive.You can read more about configuring this connection in the Help docs:https://cloud.cdata.com/docs/ExcelOnline.htmlIt doesn’t look like the connector currently supports reading from CSV, but you could convert those to .xls* files. Otherwise, ​@Ethem Q’s suggestion of API server would work.You can always suggest adding the CSV connector to Connect AIhere:https://community.cdata.com/ideas

Ethem Q · Answer

Hi ​@jfuhrmanUnfortunately, you won't be able to accomplish what you are are trying to do in Connect AIwith the Sharepoint connection. While using the Sharepoint connectionallows you to access the metadata of the actual CSV or XSLM files stored in Sharepoint, it does not have a way to access the actual content of the files since we are using the Sharepoint connection as a medium here and we are not connecting to the file directly. In order to connect to CSV or XSLM files stored in Sharepoint or in any other Cloud storage services, we would need to use a CSV and an Excel connections which we do not support in Connect AIat this time. We will submit a request to the Connect AIteam to add support for these sources.In the meantime while our team evaluates this request, as an alternative solution to Connect AIyou might want to use our API Server tool.I can explain a bit more about the API Server so you can get an idea of it if this can work for you. The API Server allows you to expose data from the various data sources that CData supports. Once you can create a connection, you can define which resources are available, and you can manage which users have access to which resources very similar to Connect AI. One of the advantages of the API Server is that it enables access to the various data sources at the same API endpoint. By default the API Server ships with a few connections, but you can download the corresponding connectors within the API Server Connections board.You should be able to download the CSV and Excel connectors and set up a successful connection to the files stored in Sharepoint by setting the ConnectionType to either Sharepoint REST or SharePoint SOAP depending on which service you want to use for your connection. Once you have made a connection you should be able to create resources based on the data of your files which can be exposed via an OData endpoint.You can download and get more information on how to set up the API Server from the link below:API Server: https://www.cdata.com/apiserver/If you have any other questions feel free to reach out to the CData Support team at support@cdata.comand someone from the support team will further assist you.

Sign up

CData Community

Scanning file for viruses.

This file cannot be downloaded