FireDAC Components for CSV

Build 23.0.8839

Establishing a Connection

You can create and manage connections with the Data Explorer and the FireDAC Connection Editor wizards. See Connection Definitions for more information on creating connections from code and creating persistent, private, or temporary connections.

The CData FireDAC Components for CSV allows connecting to local and remote CSV resources. Set the URI property to the CSV resource location, in addition to any other properties necessary to connect to your data source.

Connecting to Local Files

Set the ConnectionType to Local. Local files support SELECT\INSERT\UPDATE\DELETE.

Set the URI to a folder containing CSV files: C:\folder1.

You can also connect to multiple CSV files which share the same schema. Below is an example connection string: 

URI=C:\folder; AggregateFiles=True;

If you would prefer to expose all of the individual CSV files as tables instead, leave this property False. 

URI=C:\folder; AggregateFiles=False;

Connecting to Cloud-Hosted CSV Files

While the component is capable of pulling data from CSV files hosted on a variety of cloud data stores, INSERT, UPDATE, and DELETE are not supported outside of local files in this component.

If you need INSERT/UPDATE/DELETE cloud files, you can download the corresponding CData component for that cloud host (supported via stored procedures), make changes with the local file's corresponding component, then upload the file using the cloud source's stored procedures.

As an example, if you wanted to update a file stored on SharePoint, you could use the CData SharePoint component's DownloadDocument procedure to download the CSV file, update the local CSV file with the CData CSV component, then use the SharePoint component's UploadDocument procedure to upload the changed file to SharePoint.

A unique prefix at the beginning of the URI connection property is used to identify the cloud data store being targed by the component and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table).

Amazon S3

Set the following to identify your CSV resources stored on Amazon S3:

  • ConnectionType: Set the ConnectionType to Amazon S3.
  • URI: Set this to the bucket and folder: s3://bucket1/folder1.

See Connecting to Amazon S3 for more information regarding how to connect and authenticate to CSV files hosted on Amazon S3.

Azure Blob Storage

Set the following to identify your CSV resources stored on Azure Blob Storage:

  • ConnectionType: Set this to Azure Blob Storage.
  • URI: Set this to the name of your container and the name of the blob. For example: azureblob://mycontainer/myblob.

See Connecting to Azure Blob Storage for more information regarding how to connect and authenticate to CSV files hosted on Amazon Blob Storage.

Azure Data Lake Storage

Set the following to identify your CSV resources stored on Azure Data Lake Storage:

  • ConnectionType: Set this to Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, or Azure Data Lake Storage Gen2 SSL.
  • URI: Set this to the name of the file system and the name of the folder which contains your CSV files. For example:
    • Gen 1: adl://myfilesystem/folder1
    • Gen 2: abfs://myfilesystem/folder1
    • Gen 2 SSL: abfss://myfilesystem/folder1

See Connecting to Azure Data Lake Storage for more information regarding how to connect and authenticate to CSV files hosted on Azure Data Lake Storage.

Azure File Storage

Set the following properties to connect:

  • ConnectionType: Set this to Azure Files.
  • URI: Set this the name of your azure file share and the name of the resource. For example: azurefile://fileShare/remotePath.
  • AzureStorageAccount (Required): Set this to the account associated with the Azure file.

You can authenticate either an Azure access key or an Azure shared access signature. Set one of the following:

  • AzureAccessKey: Set this to the access key associated with the Azure file.
  • AzureSharedAccessSignature: Set this to the shared access signature associated with the Azure file.

Box

Set the following to identify your CSV resources stored on Box:

  • ConnectionType: Set this to Box.
  • URI: Set this the name of the file system and the name of the folder which contains your CSV files. For example: box://folder1.

See Connecting to Box for more information regarding how to connect and authenticate to CSV files hosted on Box.

Dropbox

Set the following to identify your CSV resources stored on Dropbox:

  • ConnectionType: Set this to Dropbox.
  • URI: Set this to the path to a folder containing CSV files. For example: dropbox://folder1.

See Connecting to Dropbox for more information regarding how to connect and authenticate to CSV files hosted on Dropbox.

FTP

The component supports both plaintext and SSL/TLS connections to FTP servers.

Set the following connection properties to connect:

  • ConnectionType: Set this to either FTP or FTPS.
  • URI: Set this to the address of the server followed by the path to the folder to be used as the root folder. For example: ftp://localhost:990/folder1 or ftps://localhost:990/folder1.
  • User: Set this to your username on the FTP(S) server you want to connect to.
  • Password: Set this to your password on the FTP(S) server you want to connect to.

Google Cloud Storage

Set the following to identify your CSV resources stored on Google Cloud Storage:

  • ConnectionType: Set this to Google Cloud Storage.
  • URI: Set this to the path to the name of the file system and the name of the folder which contains your CSV files. For example: gs://bucket/remotePath.

See Connecting to Google Cloud Storage for more information regarding how to connect and authenticate to CSV files hosted on Google Cloud Storage.

Google Drive

Set the following to identify your CSV resources stored on Google Drive:

  • ConnectionType: Set this to Google Drive.
  • URI: Set to the path to the name of the file system and the name of the folder which contains your CSV files. For example: gdrive://folder1.

See Connecting to Google Drive for more information regarding how to connect and authenticate to CSV files hosted on Google Drive.

HDFS

Set the following to identify your CSV resources stored on HDFS:

  • ConnectionType: Set this to HDFS or HDFS Secure.
  • URI: Set this to the path to a folder containing CSV files. For example:
    • HDFS: webhdfs://host:port/remotePath
    • HDFS Secure: webhdfss://host:port/remotePath

There are two authentication methods available for connecting to HDFS data source, Anonymous Authentication and Negotiate (Kerberos) Authentication.

Anonymous Authentication

In some situations, you can connect to HDFS without any authentication connection properties. To do so, set the AuthScheme property to None (default).

Authenticate using Kerberos

When authentication credentials are required, you can use Kerberos for authentication. See Using Kerberos for details on how to authenticate with Kerberos.

HTTP Streams

Set the following to identify your CSV resources stored on HTTP streams:

  • ConnectionType: Set this to HTTP or HTTPS.
  • URI: Set this to the URI of your HTTP(S) stream. For example:
    • HTTP: http://remoteStream
    • HTTPS: https://remoteStream

See Connecting to HTTP Streams for more information regarding how to connect and authenticate to CSV files hosted on HTTP Streams.

IBM Cloud Object Storage

Set the following to identify your CSV resources stored on IBM Cloud Object Storage:

  • ConnectionType: Set this to IBM Object Storage Source.
  • URI: Set this to the bucket and folder. For example: ibmobjectstorage://bucket1/remotePath.
  • Region: Set this property to your IBM instance region. For example: eu-gb.

See Connecting to IBM Object Storage for more information regarding how to connect and authenticate to CSV files hosted on IBM Cloud Object Storage.

OneDrive

Set the following to identify your CSV resources stored on OneDrive:

  • ConnectionType: Set this to OneDrive.
  • URI: Set this to the path to a folder containing CSV files. For example: onedrive://remotePath.

See Connecting to OneDrive for more information regarding how to connect and authenticate to CSV files hosted on OneDrive.

Oracle Cloud Storage

Set the following properties to authenticate with HMAC:

  • ConnectionType: Set the ConnectionType to Oracle Cloud Storage.
  • URI: Set this to the bucket and folder: os://bucket/remotePath.
  • AccessKey: Set this to an Oracle Cloud Access Key.
  • SecretKey: Set this to an Oracle Cloud Secret Key.
  • OracleNamespace: Set this to an Oracle cloud namespace.
  • Region (optional): Set this to the hosting region for your S3-like Web Services.

SFTP

Set the following to identify your CSV resources stored on SFTP:

  • ConnectionType: Set this to SFTP.
  • URI: Set this to the address of the server followed by the path to the folder to be used as the root folder. For example: sftp://server:port/remotePath.

See Connecting to SFTP for more information regarding how to connect and authenticate to CSV files hosted on SFTP.

SharePoint Online

Set the following to identify your CSV resources stored on SharePoint Online:

  • ConnectionType: Set this to SharePoint REST or SharePoint SOAP.
  • URI: Set this to a document library containing CSV files. For example:
    • SharePoint Online REST: sprest://remotePath
    • SharePoint Online SOAP: sp://remotePath

See Connecting to SharePoint Online for more information regarding how to connect and authenticate to CSV files hosted on SharePoint Online.

Connecting to Other Sources: System Streams

You can also read from and write to system streams. Reference the stream from code with the ExtendedProperties connection property.

Securing CSV Connections

By default, the component attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.

Using the FireDAC Connection Editor

Complete the following steps to use the FireDAC Connection Editor to assign a CSV connection to a TFDConnection object:

  1. Open a project and drop a TFDPhysCDataCSVDriverLink from the Tool Palette onto the Form Designer.
  2. Drop a TFDConnection object onto the Form Designer.
  3. Double-click the TFDConnection and select CDataCSV in the Driver Id menu.
  4. Define the necessary connection properties.
  5. To execute ad-hoc SQL statements based on this connection, enter SQL commands on the SQL Script tab.
  6. In the Form Designer, select the TFDConnection object and set the Connected property to true.

Using the Data Explorer

Complete the following steps to use the Data Explorer to define persistent connections that can be shared across applications and projects:

  1. Click View > Tool Windows > Data Explorer in RAD Studio and expand the FireDAC node.
  2. Right-click the CData CSV Data Source node and click Add New Connection.
  3. Enter a name for the connection. The FireDAC Connection Editor opens.

Working with Persistent Connections

The connections you define in the Data Explorer can be assigned to a TFDConnection object by specifying the connection name in the TFDConnection object's ConnectionDefName property.

Browsing Data and Metadata

The Data Explorer also provides options for browsing CSV objects at design time:

  • Expand the CData CSV Data Source node in the Data Explorer to view the defined connections.
  • Expand a connection node to find the database objects available for a connection.
  • Drill down to a database object to find metadata such as primary keys.
  • To display data, double-click a table or view or right-click and click View.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839