ADO.NET Provider for CSV

Build 20.0.7654

Connecting to CSV Data Sources

Connecting to CSV

Below are example connection strings to CSV files or streams, using the provider's default data modeling configuration (see below)

Service provider URI formats Connection example
Local localPath

file:///localPath

URI=C:/folder1;
HTTP or HTTPS http://remoteStream

https://remoteStream

URI=http://www.host1.com/streamname1;
Amazon S3 s3://bucket1/folder1 URI=s3://bucket1/folder1; AWSSecretKey=secret1; AWSRegion=OHIO;
Azure Blob Storage azureblob://mycontainer/myblob/ URI=azureblob://mycontainer/myblob/; AzureAccount=myAccount; AzureAccessKey=myKey;
Google Drive gdrive://remotePath

gdrive://SharedWithMe/remotePath

URI=gdrive://folder1;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

URI=gdrive://SharedWithMe/folder1;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

OneDrive onedrive://remotePath

onedrive://SharedWithMe/remotePath

URI=onedrive://folder1;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

URI=onedrive://SharedWithMe/folder1;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

Box box://remotePath URI=box://folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;
Dropbox dropbox://remotePath URI=dropbox://folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345;
Sharepoint SOAP sp://remotePath URI=sp://Documents/folder1; User=user1; Password=password1; SharepointUrl=https://subdomain.sharepoint.com;
Sharepoint REST sprest://remotePath URI=sprest://Documents/folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; SharepointUrl=https://subdomain.sharepoint.com;
FTP or FTPS ftp://server:port/remotePath

ftps://server:port/remotepath

URI=ftps://localhost:990/folder1; User=user1; Password=password1;
SFTP sftp://server:port/remotePath URI=sftp://127.0.0.1:22/folder1 User=user1; Password=password1;

URI=sftp://127.0.0.1:22/folder1 SSHAuthmode=PublicKey; SSHClientCert=myPrivateKey

Azure Data Lake Store Gen1 adl://remotePath

adl://Account.azuredatalakestore.net@remotePath

URI=adl://folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; AzureAccount=myAccount; AzureTenant=tenant;

URI=adl://myAccount.azuredatalakestore.net@folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; AzureTenant=tenant;

AzureDataLakeStoreGen2 abfs://myfilesystem/remotePath

abfss://myfilesystem@accountName.dfs.core.windows.net/remotepath

URI=abfs://myfilesystem/folder1; AzureAccount=myAccount; AzureAccessKey=myKey;

URI=abfss://myfilesystem@myAccount.dfs.core.windows.net/folder1; AzureAccessKey=myKey;

AzureDataLakeStoreGen2 with SSL abfss://myfilesystem/remotePath

abfs://myfilesystem@accountName.dfs.core.windows.net/remotepath

URI=abfss://myfilesystem/folder1; AzureAccount=myAccount; AzureAccessKey=myKey;

URI=abfss://myfilesystem@myAccount.dfs.core.windows.net/folder1; AzureAccessKey=myKey;

Wasabi wasabi://bucket/remotePath URI=wasabi://bucket/folder1; AccessKey=token1; SecretKey=secret1; Region='us-west-1';
Google Cloud Storage gs://bucket/remotePath URI=gs://bucket/folder1; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; ProjectId=test;
Oracle Cloud Storage os://bucket/remotePath URI=os://bucket/folder1; AccessKey='myKey'; SecretKey='mySecretKey'; OracleNameSpace='myNameSpace' Region='us-west-1';

Dynamically Detecting Schemas

The following properties control how the provider automatically models CSV as tables when you connect:

  • HDR: Set this to get column names from the first line of the specified files (the default). Otherwise, the column names are the column numbers.
  • FMT: Set this to the format to be used to parse the text files: CsvDelimited (the default) or TabDelimited.
  • IncludeFiles: Set this to a comma-separated list of file extensions to include into the set of files modelled as tables. (By default, .txt, .tab, and .csv files are modelled.)
  • RowScanDepth: Set this to automatically determine data types by scanning rows up to the specified depth.

When working with local CSV, you can also use Schema.ini files, compatible with the Microsoft Jet driver, to define columns and data types. See Using Schema.ini for a guide.

Customizing Schemas

To customize column data types and other aspects of the schemas, you can save the schemas to static configuration files. The configuration files have a simple format that makes them easy to extend. For more information on extending the provider schemas, see Generating Schema Files.

Accessing Sub-Folders

Set the following properties to model subfolders as views:

  • IncludeSubdirectories: Set this to read files and Schema.ini from nested folders. In the case of a name collision, table names are prefixed by underscore-separated folder names. By default this is false.
  • DirectoryRetrievalDepth: Set this to specify how many subfolders will be recursively scanned when IncludeSubdirectories is set. By default, the provider scans all subfolders.

When IncludeSubdirectories is set, the automatically detected table names follow the convention below:

File PathRoot\subfolder1\tableARoot\subfolder1\subfolder2\tableA
Table Namesubfolder1_tableAsubfolder1_subfolder2_tableA

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654