Retrieve excel files from Sharepoint and store data in custom table

[Xhare]

Hi Team,

I have a requirement to retrieve the excel files data from Sharepoint and store this to table.

How to achieved this?

Thanks,

Richard

[jcmings]

Assuming you have the Excel files already downloaded, you can set this up in ServiceNow with a transform map. You'd start by creating a new import set table at System Import Sets > Load Data (make sure "Create table" is selected). After assigning a label, uploading your file, and clicking submit, you'll have the option to create a transform map. You can use the "Auto Map Matching Fields" related link to speed up the process of mapping your Excel column with your ServiceNow column. For some fields, you may need to script the mapping (for example, if a person is mentioned in Excel and needs to be looked up in ServiceNow).

You can read a good walkthrough of how to do all of this here: Create a Transform Map

[Xhare]

Hi @jcmings ,

I have created already data source on this and matched the field from staging table to target table.

My query is how to retrieve the data in Sharepoint? like SFTP server

[jcmings]

You could do either of the following:

• Set up an integration using the SharePoint spoke in Integration Hub
• Schedule an export from SharePoint; when ServiceNow receives the file, grab the attachment, put it into a data source, and then run the transform map

[John Martin2]

DTech Apps DocIntegrator solves this by providing direct and authenticated access to SharePoint files, eliminating the need for SFTP-like server connections. You can leverage DocIntegrator's capabilities to:

1. Establish a secure connection to your SharePoint site and library.
2. Use DocIntegrator's functions or APIs (within ServiceNow workflows or scripts) to locate and retrieve the specific Excel files. This would involve specifying the site URL, library name, and potentially file name patterns.
3. Access the content of the retrieved Excel files. DocIntegrator can often parse the file content, allowing you to extract the data.
4. Integrate this data extraction within your existing ServiceNow data source and transformation map to populate your staging and target tables, utilizing the connection and file access provided by DocIntegrator instead of a traditional file server approach.

Essentially, DocIntegrator acts as the secure bridge to access and retrieve the Excel files from SharePoint programmatically within the ServiceNow environment.