Flat File Custom Settings

By default, DataSync scans the first 100 rows to determine the data type for each column. If there are a big amount of rows, you can use the RowScanDepth parameter to scan more rows but it could be time-consuming. You can overwrite these settings by applying a custom configuration (Schema.ini) for each flat file.

Using Schema.ini

In the Schema.ini file you can specify the format of a text file you want to model as a table and you can also define the columns of the table. Schema.ini must be located in the Source folder -- or, if Include Subdirectories is set, Schema.ini can be defined in each subfolder.

Files specified in Schema.ini are reported as tables. The provider uses a definition in Schema.ini if one exists and the filename otherwise to report the table.

Defining Tables in Schema.ini

A section in Schema.ini must begin with the file name enclosed in square brackets. For example:

[Jerrie's travel expense.txt]

After adding a file name entry, you can set the Format property to the format of the file. The possible values are the following:

  • CSVDelimited

  • TabDelimited

  • Delimited(custom character)

For example, the following is equivalent to CSVDelimited:

Format=Delimited(,)

Note

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

You then need to define columns. There are two ways to define columns based on the fields in your text files:

  • Define the column names in the file's first row, the header row. When you connect the provider determines the data type.

  • Define the column number, name, data type, and width in Schema.ini. Columns defined this way override columns initially accepted from the header row. You may also ignore a file's header row by specifying ColNameHeader=False in the file's section in Schema.ini.

To define a column in Schema.ini, use the following format:

Coln=ColumnName DataType [Width Width]

For example:

Col2=A Text Width 100

Note

If the format is set to a fixed length, then defining the width of each column is mandatory.

[Jerrie's travel expense.csv]

ColNameHeader=True

Format=Delimited(,)

DateTimeFormat=yyyy/MM/dd

Col1=Date Text

Col2=A Text

Col3=B Text

Col4=C Text

Col5=Total Text

Col6=Date Text

Col7=D Text

Col8=E Text

Col9=F Text

Col10=G Text

Col11=rate numeric

 

[invoices.csv]

ColNameHeader=True

Format=Delimited(,)

DateTimeFormat= yyyy/MM/dd

Col1=id numeric

Col2=invoicedate date

Col3=total numeric

Supported Data Types

Data types can be any of the following:

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