SharePoint Online Connector for Informatica
In this article you will learn how to integrate Using SharePoint Online Connector you will be able to connect, read, and write data from within Informatica. Follow the steps below to see how we would accomplish that. The driver mentioned above is part of ODBC PowerPack which is a collection of high-performance Drivers for various API data source (i.e. REST API, JSON, XML, CSV, Amazon S3 and many more). Using familiar SQL query language you can make live connections and read/write data from API sources or JSON / XML / CSV Files inside SQL Server (T-SQL) or your favorite Reporting (i.e. Power BI, Tableau, Qlik, SSRS, MicroStrategy, Excel, MS Access), ETL Tools (i.e. Informatica, Talend, Pentaho, SSIS). You can also call our drivers from programming languages such as JAVA, C#, Python, PowerShell etc. If you are new to ODBC and ZappySys ODBC PowerPack then check the following links to get started. |
Connect to SharePoint Online in other apps
|
How to read API data in Informatica (Call JSON / XML SOAP Service)
How to write data to API (POST) in Informatica (Call JSON / XML SOAP Service)
Introduction
JSON / REST API is becoming more and more popular each day as everyone embrace cloud-centric services. This article is primarily focused on Informatica users who want to access SharePoint Online data or may be other API Integration in Informatica. However many tips and techniques described in this article will help you to understand how to integrate SharePoint Online / XML SOAP / JSON / REST API in other ETL / Reporting apps such as Tableau, Power BI, SSRS, Talend, Excel and many more.
After going through this article you will learn how to Read SharePoint Online / JSON / REST API data in Informatica and understand the concept of JSON / REST API. We will go through many screenshots and step-by-step examples to demonstrate SharePoint Online or REST API integration in Informatica PowerCenter.
XML / JSON can come from a local file or REST API service (internal or public) so we will include both examples in this article (i.e. Read JSON files in Informatica, Import REST API in Informatica). So let’s get started. Next article will focus on how to write data to API in Informatica (POST / PUT data)
Requirements
This article assumes that you have full filled following basic requirements.
- Download Install ZappySys ODBC PowerPack (API Driver for SharePoint Online included)
- Install Informatica PowerCenter Client Tools (e.g. Workflow and Mapping Designers)
- Access to a Relational database such as SQL Server (or use any of your choice e.g. Oracle, MySQL, DB2 ). If nothing available then you can use flat file target.
Before we dive deep to learn how to load SharePoint Online data in Informatica (i.e. SharePoint Online to SQL Table), Here the summary of high-level steps you need to perform to import SharePoint Online in Informatica (same steps for Import JSON / XML / REST API).
- Download and Install ZappySys API Driver (for connecting to SharePoint Online)
- Create ODBC DSN using ZappySys API driver and choose SharePoint Online Connector during Wizard
- Create Relational > ODBC Connection in Informatica Workflow designer (Point to DSN we created in the previous step)
- Import SharePoint Online Source Definition in the Informatica Mapping Designer > Sources Tab
- Import Target Table Definition in the Informatica Mapping Designer > Targets Tab
- Create source to target mapping in Mappings tab
- Save mapping (name m_API_to_SQL_Load )
- Create Session using the mapping we created in the previous step
- Save Workflow and execute to load SharePoint Online data into SQL Table. Verify your data and log.
Below video is not about SharePoint Online API but its showing API access in general (for any API). By watching following ~5 min video can learn steps listed in this article to load JSON API data into SQL Server Table using ZappySys JSON Driver. You can go though full article to learn many useful details not covered in this video.
Now let’s get started. For example purpose, we will read data from SharePoint Online and load data into SQL Server Table using Informatica Workflow.
Create ODBC Data Source (DSN) based on ZappySys API Driver
Step-by-step instructions
To get data from SharePoint Online using Informatica we first need to create a DSN (Data Source) which will access data from SharePoint Online. We will later be able to read data using Informatica. Perform these steps:
-
Install ZappySys ODBC PowerPack.
-
Open ODBC Data Sources (x64):
-
Create a User Data Source (User DSN) based on ZappySys API Driver
ZappySys API DriverYou should create a System DSN (instead of a User DSN) if the client application is launched under a Windows System Account, e.g. as a Windows Service. If the client application is 32-bit (x86) running with a System DSN, use ODBC Data Sources (32-bit) instead of the 64-bit version. -
When the Configuration window appears give your data source a name if you haven't done that already, then select "SharePoint Online" from the list of Popular Connectors. If "SharePoint Online" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:
SharepointOnlineDSNSharePoint Online -
Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]
Steps how to get and use SharePoint Online credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g.automation@my-company.com
). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to App registrations and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference. -
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
- Use
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Use
https://zappysys.com/oauth
as the URL.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
User.Read
-
Sites.Read.All
-
Sites.ReadWrite.All
-
Files.Read.All
-
Files.ReadWrite.All
-
- Press Generate Token button to generate Access and Refresh Tokens.
- Optional step. Choose Default Site Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- Done! Now you are ready to use the API Connector!
Fill in all required parameters and set optional parameters if needed:
SharepointOnlineDSNSharePoint OnlineUser Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill-in the parameter... Token URL Fill-in the parameter... Client ID Fill-in the parameter... Scope Fill-in the parameter... Return URL Fill-in the parameter... Default Site Id (select after pressing 'Generate Token') Fill-in the parameter... Optional Parameters Client Secret Default Drive Id (select after pressing 'Generate Token') Login Prompt Option RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503|423 RetryCountMax 5 RetryMultiplyWaitTime True Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed) Extra Headers (e.g. Header1:AAA||Header2:BBB) IsAppCred 0 Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]
Steps how to get and use SharePoint Online credentials
Follow these simple steps to create Microsoft Entra ID application with application access permissions:
-
Create an OAuth app
-
Grant application SharePoint Online permissions (optional, for granular permissions)
This step allows to grant OAuth application granular permissions, i.e. access configured specificSites
,Lists
, andList Items
.Step-1: Create OAuth app
- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
- e.g. select
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Leave the URL field empty.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Sites permissions (search for
site
):INFO: If you want to access specific lists or list items (table-level vs row-level security) rather than the full site, then addLists.SelectedOperations.Selected
orListItems.SelectedOperations.Selected
permissions , just like in the previous step (search forlist
).WARNING: If you add any of these permissions -Sites.Selected
,Lists.SelectedOperations.Selected
, orListItems.SelectedOperations.Selected
- you must grant the app the SharePoint permissions for the specific resource (e.g. aSite
, aList
, or aListItem
). Follow instructions in Grant SharePoint permissions to the OAuth app (optional) section on how to accomplish that. -
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Optional step. Choose Default Site Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- Done!
Step-2 (optional): Grant SharePoint permissions to the OAuth app (optional)
If you used
Sites.Selected
,Lists.SelectedOperations.Selected
orListItems.SelectedOperations.Selected
permission in the previous section, you must grant the app the SharePoint permissions for the specific resource (e.g. aSite
, aList
, or aListItem
). You can do it using PowerShell or SharePoint admin center (obsolete method).Granting SharePoint permissions using PowerShell
Unfortunately, there is no user interface available to control these permissions yet. For now, granting permissions has to be accomplished via Microsoft Graph API [Microsoft reference]:
You must be the owner of the resource to grant permissions (i.e. belong to SharePoint owners group or be the owner of theSite
orList
).- Open PowerShell (run as admin).
-
Call the following PowerShell code to grant
read
andwrite
permission for the app we created earlier (assuming Application (client) ID is89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e
):# More info at: # - https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell # - https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.sites/?view=graph-powershell-1.0 # Find SharePoint Site Id by following these steps: # - Login into SharePoint Online # - Open this URL https://{your-company}.sharepoint.com/_api/site in the browser # NOTE: For a subsite use https://{your-company}.sharepoint.com/sites/{your-subsite}/_api/site # - Find 'Id' element in the response (e.g. <d:Id m:type="Edm.Guid">efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3</d:Id>) # - Copy the Site Id, i.e.: efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3 # Set $siteId variable to the retrieved Site Id: $siteId="efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3" # Find your Application Id (i.e. Client Id) in the Azure Portal, in App Registrations page: # https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade $applicationId="89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e" # Available permissions: read, write, fullcontrol, owner ('write' includes 'read' permission) $appPermission="write" # Step-1: Install 'Microsoft.Graph.Sites' module if it's not installed if (-not (Get-Module Microsoft.Graph.Sites -ListAvailable)) { Install-Module Microsoft.Graph.Sites } # Step-2: Load module Import-Module Microsoft.Graph.Sites # Step-3: Login (use Azure admin or SharePoint owner account) DisConnect-MgGraph Connect-MgGraph # Step-4: Set parameters for API call (set permissions, Site ID and Application ID) $params = @{ roles = @($appPermission) grantedTo = @{ application = @{id = $applicationId} } } # Step-5: Grant permissions New-MgSitePermission -SiteId $siteId -BodyParameter $params # Done! Write-Host "Granted SharePoint permissions to application '$applicationId' for site '$siteId'."
- That's it! Now you can use the API Connector!
Granting SharePoint permissions using SharePoint admin center (obsolete method)
If you used
Site.Selected
permission you can link it SharePoint site in SharePoint admin center [SharePoint reference]. Follow these simple steps to accomplish that:-
Log in to SharePoint admin center using this URL:
(replace
YOURCOMPANY
with your company name):https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appinv.aspx
INFO: To view all the registered apps in SharePoint, visit this page:https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web
. - In the App Id field enter Application (client) ID you copied in the previous step.
-
In the Permission Request XML field enter XML snippet which describes which SharePoint permissions you want to grant to the OAuth app, e.g.:
<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" /> </AppPermissionRequests>
INFO: This example gives appFullControl
, but you can also grant itRead
orWrite
permissions. - Click Create to grant the permission to your OAuth app.
- That's it! Now you can use the API Connector!
Fill in all required parameters and set optional parameters if needed:
SharepointOnlineDSNSharePoint OnlineApplication Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Token URL Fill-in the parameter... Client ID Fill-in the parameter... Client Secret Fill-in the parameter... Default Site Id Fill-in the parameter... Optional Parameters Scope https://graph.microsoft.com/.default Default Drive Id RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503|423 RetryCountMax 5 RetryMultiplyWaitTime True Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed) Extra Headers (e.g. Header1:AAA||Header2:BBB) IsAppCred 1
Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
Click OK to finish creating the data source.
Video instructions
Create Connection in Informatica Workflow Designer
Once you create DSN using API Driver our next step is to define a connection for SharePoint Online source in Informatica PowerCenter Workflow designer.
- Open Workflow designer [W] icon
- Goto Connections > Relational
- Click New and select ODBC
- Now on the ODBC connection setup enter connection name, some fake userid / password (this is a required field but its ignored by JSON Driver)
-
In the Connection String field enter the exact same name of DSN (Open ODBC Data Sources UI to confirm)
Configure SharePoint Online connection in Informatica for REST API – Using ZappySys API Driver
- Click OK to close the connection properties.
That’s it. Now we ready to move to next step (define source and target in Mapping Designer).
Now let’s look at steps to import SharePoint Online table definition.
- Open Informatica Mapping Designer (Click [D] icon)
- Click on Source Icon to switch to Sources designer
- From the top menu > Click on Sources > Import from Database …
- Select ODBC data source from the dropdown (Find out DSN we created earlier to use as JSON Source)
-
Click Connect button to get a list of tables. Any array node is listed as a table. Also, you will see array node with parent columns (e.g. value_with_parent). You may get some warning like below but they are harmless so just ignore by clicking OK.
DLL name entry missing from C:\Informatica\PowerCenter8.6.1\client\bin\powrmart.ini Section = ODBCDLL Entry = ZappySys JSON Driver
—————————————————-
Using EXTODBC.DLL to support ZappySys JSON Driver. For native support of ZappySys JSON Driver make an entry in the .ini file. - Select Table you wish to get (You can filter rows by custom SQL query. We will see later in this article how to do)
- Optionally once table structure is imported you can rename it
- That’s it, we are now ready to perform similar steps to import Target table structure in the next section.
Import SQL Server Target Definition in Informatica Mapping Designer
Now let’s look at steps to import Target table definition (very similar to the previous section, the only difference is this time we will select DSN which points to SQL Server or any other Target Server).
Now lets look at steps to import target table definition in Informatica mapping designer.
- In the Mapping Designer, Click on Target Icon to switch to Target designer
- From the top menu > Click on Targets > Import from Database …
- Select DSN for your Target server (if DSN doesn’t exist then create one by opening ODBC Sources just like we created one for JSON API source (see the previous section about creating DSN).
- Enter your userid , password and Schema name and click Connect to see tables
- Select Table name to and click OK import definition.
Once you have imported source and target table definition, we can create mapping and transformation to load data from JSON to SQL Table.
- First open Mapping Designer (Click [D] icon)
- Drag JSON Source from sources folder
- Drag SQL Table from Targets folder
- Map desired columns from Source to target
-
For certain columns you may have to do datatype conversion. For example to convert OrderDate form nstring to DataTime you have to use Expression Transform like below and map it to target. In below example, our JSON has date format (e.g. 2018-01-31 12:00:00 AM ). To import this to DateTime field in SQL server we need to convert it using TO_DATE function. Use double quotes around T to make this format working.
TO_DATE(OrderDate,'YYYY-MM-DD H12:MI:SS AM') --For ISO use below way TO_DATE(OrderDate,'YYYY-MM-DD"T"HH24:MI:SS')
- Once you done with mapping save your mapping and name it (i.e. m_Api_To_SQL)
- Now lets move to next section to create workflow.
Create Workflow and Session in Informatica
Now the final step is to create a new workflow. Perform following steps to create workflow which with a session task to import JSON data into SQL table.
- Open workflow designer by click [W] icon.
-
Launch new workflow creation wizard by click Workflow top menu > Wizard
name your workflow (e.g. wf_Api_Tp_Sql_Table_Import) - Finish the wizard and double-click the Session to edit some default properties.
- First change Error settings so we fail session on error (By default its always green)
- Select JSON connection for Source
- Change default Source query if needed. You can pass parameters to this query to make it dynamic.
- Select Target connection of SQL Target Table
- Save workflow
- That’s it. We ready to run our first workflow to load JSON data to SQL.
Execute Workflow and Validate Log in Informatica
Now once you are done with your workflow, execute it to see the log.
POST data to SharePoint Online in Informatica
There will be a time when you like to send Source data to REST API or SOAP Web Service. You can use below Query for example. For detailed explanation on how to POST data in Informatica check this article.
Video Tutorial – How to POST data to REST API in Informatica
Here is detailed step by step video on REST API POST in informatica PowerCenter
Keywords
how to import SharePoint Online in informatica | how to read SharePoint Online data in informatica powercenter | how to test json from informatica | how to use SharePoint Online data as source in informatica power center | how to connect SharePoint Online in informatica 10 | informatica how to import data from SharePoint Online | informatica jtx to import SharePoint Online (use of java transformation) | informatica plugin for restful api using json | informatica power center and SharePoint Online support | informatica read SharePoint Online | informatica rest api | informatica SharePoint Online connector | json parser import informatica
Advanced topics
Create Custom Stored Procedure in ZappySys Driver
You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.
Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
-
Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
-
Enter the desired Procedure name and click on OK:
-
Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
CREATE PROCEDURE [usp_get_orders] @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>' AS SELECT * FROM Orders where OrderDate >= '<@fromdate>';
-
That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
Exec usp_get_orders '1996-01-01';
-
Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC usp_get_orders @fromdate=''1996-07-30''')
-
Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
Create Custom Virtual Table in ZappySys Driver
ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.
If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.
Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.
-
Go to Custom Objects Tab and Click on Add button and Select Add Table:
-
Enter the desired Table name and click on OK:
-
And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.
-
Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here
SELECT "ShipCountry", "OrderID", "CustomerID", "EmployeeID", "OrderDate", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode" FROM "Orders" Where "ShipCountry"='USA'
-
That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:
SELECT * FROM "vt__usa_orders_only"
-
Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC [usp_get_orders] ''1996-01-01''')
-
Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
Actions supported by SharePoint Online Connector
SharePoint Online Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.Parameter | Description |
---|---|
Site Id |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Drive Id |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Record Filter (Client Side) |
|
||||||||||||||||||
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||||||
Search Path (Default=Root Folder) - Max 200 items listed |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||||||
Search Path (Default=Root Folder) - Max 200 items listed |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||||||
Drive Id |
|
||||||||||||||||||
Search Type (Default=Recursive) |
|
||||||||||||||||||
Search Path (Default=Root Folder) |
|
||||||||||||||||||
Order By Field(s) - Only for Recursive SearchType |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Expand |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SiteId (Re-Select ListId after you change this) |
|
||||||||||
ListId |
|
||||||||||
Filter |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SiteId (Re-Select ListId after you change this) |
|
||||||||||
ListId |
|
||||||||||
Filter |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId (Re-Select ListId after you change this) |
|
ListId (Re-Enter ListItemId after you change this) |
|
ListItemId |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
FileId -OR- Path |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
|
||||||||||||||
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
|
||||||||||||||
AutoDetectByValue |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
|
||||||||||||||
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
|
||||||||||||||
Range |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||
Drive Id |
|
||||||||||
Disk File Path (e.g. c:\somefolder\file.xlsx) |
|
||||||||||
Target File Path OR Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234) |
|
||||||||||
Conflict Behavior (What to do if file exists) |
|
||||||||||
Use Source File Created/Modified DateTime rather than Uploaded Time |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Site Id (Re-Select Drive Id after you change this) |
|
||||||||||||||
Drive Id |
|
||||||||||||||
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
|
Parameter | Description |
---|---|
Site Id (Re-Select Drive Id after you change this) |
|
Drive Id |
|
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Operation |
|
||||||||||||||
Source Site Id (Optional - Select if different than default site) |
|
||||||||||||||
Source Drive Id |
|
||||||||||||||
Source File or Folder Id you like to move or rename |
|
||||||||||||||
Target Site Id (Optional - Select if different than source SiteId) |
|
||||||||||||||
Target DriveId (Optional - Select if different than source DriveId) |
|
||||||||||||||
Target Folder Id (e.g. 01R65Qxxxxxxx123 -OR- root) - Max 200 items listed |
|
||||||||||||||
Target Item Name (Optional for Move - e.g. Myfile.pdf or MyFolder) |
|
||||||||||||||
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Prefer Async Operation (Enable if you get error) |
|
Parameter | Description | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Source Site Id (Optional - Select if different than default site) |
|
||||||||||||||
Source Drive Id |
|
||||||||||||||
Source File or Folder Id you like to copy |
|
||||||||||||||
Target Site Id (Optional - Select if different than source SiteId) |
|
||||||||||||||
Target DriveId (Optional - Select if different than source DriveId) |
|
||||||||||||||
Target Folder Id (e.g. 01R65QTTRxxxxx -OR- root) |
|
||||||||||||||
Target Item Name (Optional) |
|
||||||||||||||
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
|
||||||||||||||
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
|
||||||||||||||
Prefer Async Operation (Enable if you get error) |
|
||||||||||||||
Conflict Behavior (What to do if file exists at target location) |
|
Parameter | Description | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Url |
|
||||||||||||||||||||||||||
Body |
|
||||||||||||||||||||||||||
IsMultiPart |
|
||||||||||||||||||||||||||
Filter |
|
||||||||||||||||||||||||||
Headers |
|
Parameter | Description |
---|---|
Url |
|
IsMultiPart |
|
Filter |
|
Headers |
|
SharePoint Online Connector Examples for Informatica Connection
This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.
Select lists from Lists table [Read more...]
SELECT * FROM Lists
Select data from a named list [Read more...]
SELECT * FROM MyList
--WHERE Id = 1234
Insert data into a named list [Read more...]
INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)
Insert multiple Lookup or Person/Group IDs into a named list [Read more...]
--new version --
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List
Insert multiple Choice Values into a named list [Read more...]
--new version --
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--Considering "MultiChoiceField" is the name of the field in SharePoint List
Update data in a named list [Read more...]
UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123'
Update Multi Select Lookup or Person/Group IDs field [Read more...]
UPDATE MyList
SET Title = 'Elizabeth Johnson',
--Method#1 (Pass type info in the same value)
ManyPersonsLookupId = '[11,22,33]', "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
--Method#2 (Pass type info via ODataField)
--OR--
--ManyPersonsLookupId = '[11,22,33]',
--ManyPersonsLookupIdODataType" = 'Collection(Edm.Int32)
WHERE Id = '123'
Update / Insert Sharepoint List Data (Mixed Data Types - e.g. Multi Choice, Lookup, Person, DateTime, Number) [Read more...]
UPDATE "Travel Requests" --list name
SET
Title='MyTitle @ <<FUN_NOW>>'
--Method#1 Pass Value and Type in 2 fields
, CustomText = 'This is text'
, CustomTextLarge = 'This is really long text ....'
, CustomNumber = 10000.12345
, CustomDateTime ='2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, CustomChoice = 'BB'
--Choice Multi-Select
, CustomChoiceMulti = '["AA", "BB"]'
, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type
--OR-- append like below in one field
--, CustomChoiceMulti = '["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"'
--Lookup
--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
, CustomLookupSingleLookupId = 1
--Lookup Multi-Select
, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type
, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomLookupMultiLookupId = '[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"'
--Person
,CustomPersonLookupId = 11
--Person Multi-Select
, CustomPersonMultiLookupId = '[11, 22]'
, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomPersonMultiLookupIdODataType = '[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"'
Where Id=1 --List Item Id
--- Insert Example ----
INSERT INTO "Travel Requests" --list name
(
Title
, CustomText
, CustomTextLarge
, CustomNumber
, CustomDateTime
, CustomChoice
, CustomChoiceMulti
, CustomChoiceMultiODataType
, CustomLookupSingleLookupId
--Lookup Multi-Select
, CustomLookupMultiLookupId
, CustomLookupMultiLookupIdODataType
--Person
,CustomPersonLookupId
, CustomPersonMultiLookupId
, CustomPersonMultiLookupIdODataType
)
VALUES(
'MyTitle @ <<FUN_NOW>>'
, 'This is text'
, 'This is really long text .. '
, 10000.12345
, '2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, 'BB'
--Choice Multi-Select
, '["AA", "BB"]'
, 'Collection(Edm.String)' --for multi select must supply OData Type
--Lookup
, 1
--Lookup Multi-Select
, '[1,3]' --for multi select must supply OData Type
, 'Collection(Edm.Int32)'
--Person
,11
--Person Multi-Select
, '[11, 22]'
, 'Collection(Edm.Int32)'
)
Delete item from a named list [Read more...]
DELETE FROM MyList WHERE Id=888
--OR--
DELETE FROM MyList WITH (ListItemId='123')
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.
Main site [Read more...]
Gets main SharePoint site details
SELECT * FROM get_main_site
List sites [Read more...]
Lists all available SharePoint sites
SELECT * FROM Sites
List drives [Read more...]
Lists all drives
SELECT * FROM Drives
Get drive [Read more...]
SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List folders from SharePoint Document Library [Read more...]
SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List files from SharePoint Document Library [Read more...]
SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List Excel files from SharePoint Document Library [Read more...]
SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
Get list fields [Read more...]
SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Get list items (SharePoint Document Library) [Read more...]
SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Get list items - dynamic metadata (SharePoint Document Library) [Read more...]
Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person)
SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.
Create list item (SharePoint Document Library) [Read more...]
INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
Update list item (SharePoint Document Library) [Read more...]
UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
,ListItemId='775'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
Delete list item (SharePoint Document Library) [Read more...]
DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.
List Excel file worksheets (SharePoint Document Library) [Read more...]
SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Read Excel Worksheet (SharePoint Document Library) - Auto Detect Range [Read more...]
Reads an Excel file without supplying Cell Address Range (It auto detects based on first and last cell)
SELECT * FROM get_excel_worksheet_autodetect
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,AutoDetectByValue='true'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
Read Excel Worksheet (SharePoint Document Library) for a specified Cell Address Range [Read more...]
Reads an Excel file from a specified Cell Range. To automatically read without cell range use other endpoint get_excel_worksheet_autodetect
SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,Range='A1:K10000'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.
Upload a file to a SharePoint Document Library [Read more...]
SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
--,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'Drives' table.
Upload a file to SharePoint Document Library (Overwrite file if exists) [Read more...]
Uploads a file to a SharePoint Document Library and if file already exists then overwrite it rather than throwing error (Default is throw error)
SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
Download a file to a local disk from a SharePoint document library [Read more...]
SELECT * FROM download_file_to_disk
WITH(
DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
, FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
, TargetFilePath='C:\temp\List.xlsx'
--, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Read a SharePoint file content as BASE64 (Binary to string) [Read more...]
Read file content as base64 string (Binary to Base64) and also save Base64 to disk to recreate file.
SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
Get full image path from Image type field [Read more...]
By default Image field in your List will show JSON document which contains many pieces of your Image field. To get full path of your Image file you can use JSON_VALUE function and extract multiple fields and concat together to get full path like below.
SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
FROM "My SharePoint List Name"
List files in a folder path (Recursive) [Read more...]
Use list_files endpoint to list files recursively (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
--,SearchPath='/root' --default
--,SearchPath='/root:/myfolder:' --folder path
,SearchPath='/root:/myfolder/subfolder:' --folder path nested
,OrderBy='lastModifiedDateTime desc'
)
--You can get DriveId by selecting from 'Drives' table.
List files in a folder Id (Non-Recursive) [Read more...]
This examplel shles under a specific FolderId (Available in Non-recursive mode only).
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
,SearchType='children' --must use this type if you like to list items by FolderId
,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)
--You can get DriveId by selecting from 'Drives' table.
Create / Update a CSV File from External Data Source (e.g. MSSQL / ODBC) [Read more...]
This example shows how to create / update a CSV file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to CSV file on SharePoint Document Library. If file does not exists then it creates a new one.
INSERT INTO FileReaderWriterCsv
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.csv:'
--, FileId='root:/subfolder/test_out.csv:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ...
--, RowDelimiter='{NEWLINE}' --{LF}, {TAB}, | , \x0009 ...
--, HasColumnHeaderRow=0 --set for header less file
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--See Query Builder for more options
)
Create / Update a JSON File in SharePoint Document Library from External Data Source (e.g. MSSQL / ODBC) [Read more...]
This example shows how to create / update a JSON file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to JSON file on SharePoint Document Library. If file does not exists then it creates a new one. It uses LayoutMap to generate JSON with custom layout.
INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.json:'
--, FileId='root:/subfolder/test_out.json:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
, JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
--, DoNotOutputNullProperty='True'
--, Encoding='UTF8' --UTF8WithoutBOM
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--Example#1: Output all columns
, LayoutMap='<?xml version="1.0" ?>
<settings>
<dataset id="root" main="True" readfrominput="True" />
<map src="*" />
</settings>'
--Example#2: Nested JSON (Records under an array)
/*
, LayoutMap='<?xml version="1.0" ?>
<settings singledataset="True">
<dataset id="root" main="True" readfrominput="True" />
<map name="MyArray" dataset="root" maptype="DocArray">
<map src="OrderID" name="OrderID" />
<map src="OrderDate" name="OrderDate" />
</map>
</settings>'
*/
--Example#3: Nested JSON (Records under a sub document section)
/*
, LayoutMap='<?xml version="1.0" ?>
<settings>
<dataset id="dsRoot" main="True" readfrominput="True" />
<map name="NestedSection">
<map src="OrderID" name="OrderID_MyLabel" />
<map src="OrderDate" name="OrderDate_MyLabel" />
</map>
</settings>'
*/
)
Read CSV File from SharePoint Document Library (Parse Rows and Columns without downloading) [Read more...]
This example shows how to read a CSV file from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/test_out.csv.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
Read Zip/Gzip Compressed CSV File from SharePoint Document Library (Parse Rows and Columns without downloading) [Read more...]
This example shows how to read a compressed CSV file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv.zip:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/dump.csv.gz:'
, FileCompressionType='Zip' --None, GZip, Zip
)
Read JSON File from SharePoint Document Library (Parse Rows and Columns without downloading) [Read more...]
This example shows how to read a JSON file from SharePoint Document Library without downloading on Disk (Streaming approach).
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
--to read compressed file use below way
--, FileId='root:/MyFolder/dump.json.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
Read Zip/Gzip Compressed JSON File from SharePoint Document Library (Parse Rows and Columns without downloading) [Read more...]
This example shows how to read a compressed JSON file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json.zip:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
, FileCompressionType='Zip' --None, GZip, Zip
)
Generic API Call - Update MultiChoice / Multi Select Lookup field in SharePoint List [Read more...]
This example shows how to make generic API call to update fields which allow multiple value selection in either Lookup or MultiChoice datatype. This example shows 2 types of fields. One is Multi Select e.g. MyMultiSelectLookupCol / MyMultiChoiceCol and another is Single select MySingleSelectCol (i.e. Dropdown). You must supply odata.type for Field. If its MultiChoice you can supply string ID/Value but set odata.type as Edm.String rather than Edm.Int32
SELECT * from generic_request
WITH(
Url='https://graph.microsoft.com/v1.0/sites/root/lists/1d3126af-14ca-46c7-a82a-4865873756c6/items/1'
, RequestMethod='PATCH'
, Filter='$.fields'
, Headers='Content-Type: application/json || x-header2: abcd'
, Body='{
"fields": {
"MyMultiSelectLookupColLookupId@odata.type": "Collection(Edm.Int32)"
,"MyMultiSelectLookupColLookupId": [ 1 , 2 ]
,"MyMultiChoiceColumn@odata.type": "Collection(Edm.String)"
,"MyMultiChoiceColumn": [ "AAA" , "BBB" ]
,"MySingleChoiceColumnLookupId":1
}
}'
)
Move a File or Folder in SharePoint Document Library [Read more...]
This example shows how to move a file or folder to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Item Id (File or Folder) you like to move or rename
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder Id
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR (for moving to root)
--, TargetFolderId='root'
--Target Item Name after Move (Optional): If you do not wish to change name after Move then do not supply below
--, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Rename a File or Folder in SharePoint Document Library [Read more...]
This example shows how to rename a file or folder and you can also move to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to rename (File or Folder)
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder (Optional): If you are doing just rename and moving to different location then keep it keep blank
--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--, TargetFolderId='root'
--Target Item Name (Required for rename)
, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Copy a File or Folder in SharePoint Document Library [Read more...]
This example shows how to copy a file or folder to a different location in SharePoint Document Library (In same Drive or different SharePoint Document Library). It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>
SELECT * FROM copy_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='xxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to copy
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site (Optional)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder where item will be copied
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR--
--, TargetFolderId='root'
--Target item name (Optional) - Only needed if new name needed after copy
, TargetItemName='new_file_or_folder_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--,ConflictBehavior='replace' --fail, rename (Ignored if TargetItemName is not supplied)
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
Conclusion
In this article we discussed how to connect to SharePoint Online in Informatica and integrate data without any coding. Click here to Download SharePoint Online Connector for Informatica and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).
Download SharePoint Online Connector for Informatica
Documentation
More integrations
Other application integration scenarios for SharePoint Online
Other connectors for Informatica
Download SharePoint Online Connector for Informatica
Documentation
How to connect SharePoint Online in Informatica?
How to get SharePoint Online data in Informatica?
How to read SharePoint Online data in Informatica?
How to load SharePoint Online data in Informatica?
How to import SharePoint Online data in Informatica?
How to pull SharePoint Online data in Informatica?
How to push data to SharePoint Online in Informatica?
How to write data to SharePoint Online in Informatica?
How to POST data to SharePoint Online in Informatica?
Call SharePoint Online API in Informatica
Consume SharePoint Online API in Informatica
SharePoint Online Informatica Automate
SharePoint Online Informatica Integration
Integration SharePoint Online in Informatica
Consume real-time SharePoint Online data in Informatica
Consume real-time SharePoint Online API data in Informatica
SharePoint Online ODBC Driver | ODBC Driver for SharePoint Online | ODBC SharePoint Online Driver | SSIS SharePoint Online Source | SSIS SharePoint Online Destination
Connect SharePoint Online in Informatica
Load SharePoint Online in Informatica
Load SharePoint Online data in Informatica
Read SharePoint Online data in Informatica
SharePoint Online API Call in Informatica