Table FileReaderWriterCsv
Description
No description available
Supported Operations
Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.| Method | Supported | Reference EndPoint |
|---|---|---|
| SELECT | get_csv_file | |
| INSERT | create_csv_file | |
| UPDATE | create_csv_file | |
| UPSERT | ||
| DELETE | ||
| LOOKUP |
Examples
SSIS
Use SharePoint Online Connector in API Source component to read data or in API Destination component to read/write data:
Read from CSV File Read or Write table using API Source
API Source - SharePoint Online
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
SharePoint Online
CSV File Read or Write
| Required Parameters | |
|---|---|
| Drive Id | Fill-in the parameter... |
| File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed | Fill-in the parameter... |
| ColumnDelimiter | Fill-in the parameter... |
| HasColumnHeaderRow | Fill-in the parameter... |
| FileCompressionType | Fill-in the parameter... |
| Optional Parameters | |
| Site Id (Re-Select Drive Id after you change this) | |
| Search Type - For UI Only (i.e. Recursive -OR- Non-Recursive) - Default=Recursive) | |
| Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed | |
| DataFormat | Notset |
| Continue On 404 Error (When item not found) | True |
| Encoding | |
| CharacterSet | |
| EnableCustomReplace | |
| SearchFor | |
| ReplaceWith | |
| ThrowErrorOnColumnCountMismatch | |
| ThrowErrorOnNoRecordFound | |
| AllowComment | |
| SkipRows | |
| SkipHeaderCommentRows | |
| TreatBlankAsNull | |
| IgnoreBlankLines | |
| SkipEmptyRecords | |
| TrimHeaders | |
| TrimFields | |
| DateFormatString | |
Read/write to CSV File Read or Write table using API Destination
API Destination - SharePoint Online
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
SharePoint Online
CSV File Read or Write
Select
| Required Parameters | |
|---|---|
| Drive Id | Fill-in the parameter... |
| File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed | Fill-in the parameter... |
| ColumnDelimiter | Fill-in the parameter... |
| HasColumnHeaderRow | Fill-in the parameter... |
| FileCompressionType | Fill-in the parameter... |
| Optional Parameters | |
| Site Id (Re-Select Drive Id after you change this) | |
| Search Type - For UI Only (i.e. Recursive -OR- Non-Recursive) - Default=Recursive) | |
| Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed | |
| DataFormat | Notset |
| Continue On 404 Error (When item not found) | True |
| Encoding | |
| CharacterSet | |
| EnableCustomReplace | |
| SearchFor | |
| ReplaceWith | |
| ThrowErrorOnColumnCountMismatch | |
| ThrowErrorOnNoRecordFound | |
| AllowComment | |
| SkipRows | |
| SkipHeaderCommentRows | |
| TreatBlankAsNull | |
| IgnoreBlankLines | |
| SkipEmptyRecords | |
| TrimHeaders | |
| TrimFields | |
| DateFormatString | |
ODBC application
Use these SQL queries in your ODBC application data source:
Create or update a CSV file
<p>Creates or updates a CSV file directly in a SharePoint document library using a streaming approach. Reads records from an external source (e.g. MSSQL) and writes to CSV.</p>
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
)SQL Server
Use these SQL queries in SQL Server after you create a data source in Data Gateway:
Create or update a CSV file
<p>Creates or updates a CSV file directly in a SharePoint document library using a streaming approach. Reads records from an external source (e.g. MSSQL) and writes to CSV.</p>
DECLARE @MyQuery NVARCHAR(MAX) = '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
)';
EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];