Replicating from a Flat File

Overview

When replicating from a relational database to a flat file, Syniti Replicate allows you to replicate data using the Refresh replication type.

A one-time complete replication from CSV or TXT format flat file, according to replication settings and scripts. You can schedule replications, identify the columns to be replicated, and add scripts to transform data during replication.

When replicating from a file as a source, use the Microsoft Text ODBC Driver. This driver is typically available with Microsoft Office packages containing Microsoft Access, or by downloading the Microsoft Access Database Engine 2016 Redistributable and installing it.

Create a Source Connection

1. In the Metadata Explorer, expand the Metadata node to view the Sources and Targets nodes.

2. Select the Sources node and right-click to view its menu.

3. Select Add New Connection.

4. On the Select Provider page, enter a name for the source and select Files - CSV from the Database list.

   

5. Click Next to display the Set Connection String page.

   

6. Click the Connection String value to view and click the ellipsis (…) button. The ODBC Text Setup dialog box is displayed.

7. Use this dialog box to set the folder containing text files to replicate.

   

8. Uncheck the Use Current Directory option as this is set by default to the Syniti Replicate install folder.

9. Click Select Directory.
   

10. In the Select Directory dialog box, access the folder containing the data files.

11. Select the folder and click OK in the dialog box.

12. Select Options → Define Format… to define the format for your source flat files.

    

13. Check Column Name Header if your file include headers, those columns names will be used to create the target table using the Create Target Table Wizard.

14. Once you Select Column Name Header, click in Guess to display a list of all columns inside the grid. By selecting a column name inside the grid, you will be able to modify the Data Type, Name or Width.

    • Source data files should contain comma-separated column names as the first row, followed by one row of data per record as a list of comma-separated column values.

      • ID,NAME

      • 1,"aaa"

      • 2,”bbb”

15. The Format list allows you to select a Custom Delimiter, to select a special character as text delimiter (tab, pipe, semicolon, etc.). Default value is CSV Delimiter.

16. Complete the wizard to create a source connection.

Replicating Unicode Format

Syniti Replicate supports the UTF-8 and UTF-8 BOM Unicode encoding formats. Complete the following steps to display the language in correct format:

1. Close the Syniti Replicate: Management Center if it is currently open.

2. Access the directory where your CSV files are stored.

3. Open the SCHEMA.INI file using Notepad or Notepad++.

4. Add CharacterSet=65001 to the file and save your changes.

   

Limitations

• Flat files must be stored on the same system where you installed Syniti Replicate. Shared drives or network locations are not supported.

• Modify connections setting from the Remote Management Center is not supported for Flat File connection.

• Maximum character limit for a field is 255 characters.