Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →
Replicate Multiple CSV Accounts
Replicate multiple CSV accounts to one or many databases.
CData Sync for CSV is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple CSV connections. In this article we show how to use the CLI to replicate multiple CSV accounts.
Configure CSV Connections
You can save connection and email notification settings in an XML configuration file. To replicate multiple CSV accounts, use multiple configuration files. Below is an example configuration to replicate CSV to SQLite:
Windows
<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
<DatabaseType>SQLite</DatabaseType>
<DatabaseProvider>System.Data.SQLite</DatabaseProvider>
<ConnectionString>DataSource=MyCSVFilesFolder;</ConnectionString>
<ReplicateAll>False</ReplicateAll>
<NotificationUserName></NotificationUserName>
<DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
<TaskSchedulerStartTime>09:51</TaskSchedulerStartTime>
<TaskSchedulerInterval>Never</TaskSchedulerInterval>
</CDataSync>
Java
<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
<DatabaseType>SQLite</DatabaseType>
<DatabaseProvider>org.sqlite.JDBC</DatabaseProvider>
<ConnectionString>DataSource=MyCSVFilesFolder;</ConnectionString>
<ReplicateAll>False</ReplicateAll>
<NotificationUserName></NotificationUserName>
<DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
</CDataSync>
The DataSource property must be set to a valid local folder name.
Also, specify the IncludeFiles property to work with text files having extensions that differ from .csv, .tab, or .txt. Specify multiple file extensions in a comma-separated list. You can also set Extended Properties compatible with the Microsoft Jet OLE DB 4.0 driver. Alternatively, you can provide the format of text files in a Schema.ini file.
Set UseRowNumbers to true if you are deleting or updating in CSV. This will create a new column with the name RowNumber which will be used as key for that table.
Configure Queries for Each CSV Instance
Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the CSV API. The statement below caches and incrementally updates a table of CSV data:
REPLICATE Customer;
You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple CSV accounts into the same database:
You can use a different table prefix in the REPLICATE SELECT statement:
REPLICATE PROD_Customer SELECT * FROM Customer
Alternatively, you can use a different schema:
REPLICATE PROD.Customer SELECT * FROM Customer
Run Sync
After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:
Windows
CSVSync.exe -g MyProductionCSVConfig.xml -f MyProductionCSVSync.sql
Java
java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar CSVSync.jar -g MyProductionCSVConfig.xml -f MyProductionCSVSync.sql
Data Connectors
ETL/ ELT Solutions
Cloud & API Connectivity
OEM & Custom Drivers
