Configure Flat File Custom Settings

By default, DataSync scans the first 100 rows in a flat file to determine the data type for each column. For large files, you can increase the number of scanned rows by setting the RowScanDepth parameter in Additional connection properties. This can improve type detection but may increase connection time.

You can override automatic detection by defining a custom Schema.ini configuration for each flat file.

Create a Schema.ini to define table formats

Use a Schema.ini file to control how DataSync interprets and models flat files as database tables. Within this file, you define the text file’s format and provide details about its columns, including names, data types, and widths.

Place the Schema.ini file in the Source folder so DataSync can locate it. If Include Subdirectories is enabled, you may keep a separate Schema.ini in each subfolder to define table formats for those files.

When a file entry exists in Schema.ini, DataSync uses that definition for the table schema. If no entry is found, the system will infer the schema by scanning the file name and its content.

Add table entries to Schema.ini

Each table definition in Schema.ini begins with the file name (including extension) in square brackets:

[sales_transactions.csv]

After adding the file name entry, set the Format property to match the file’s data layout.

Supported format values

Format value	Description
CSVDelimited	Comma-separated values
TabDelimited	Tab-separated values
Delimited(x)	Custom delimiter character x

Example

This is equivalent to CSVDelimited:

Format=Delimited(,)

important

By default, .txt files are processed as CSV files with headers.

Define columns in Schema.ini

You can define columns in two ways:

1. From header row Set ColNameHeader=True to use column names in the first row of the file. The provider determines the data types.
2. Explicit column definitions Specify the column number, name, data type, and width directly in Schema.ini. This overrides any header row in the file. To ignore the header row, set ColNameHeader=False.

Column definition syntax:

If the format is fixed length, you must define the width for every column.

Coln=ColumnName DataType [Width n]

Example:

Col2=A Text Width 100

Example Schema.ini

[sales_transactions.csv]
ColNameHeader=True
Format=Delimited(,)
DateTimeFormat=yyyy/MM/dd
Col1=TransactionID integer
Col2=TransactionDate date
Col3=CustomerID integer
Col4=CustomerName text
Col5=ProductCode varchar
Col6=ProductName text
Col7=Quantity integer
Col8=UnitPrice decimal
Col9=Discount decimal
Col10=TotalAmount decimal
Col11=Region varchar

[inventory master.csv]
ColNameHeader=True
Format=Delimited(,)
DateTimeFormat=yyyy-MM-dd
Col1=ProductCode varchar
Col2=ProductName text
Col3=Category varchar

Supported data types

The following data types are supported in Schema.ini column definitions:

binary	byte	boolean	date
time	datetime	decimal	double
tinyint	smallint	integer	bigint
float	string	text	longtext
char	varchar	nvarchar