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 folder specified in the URI -- or, if IncludeSubdirectories is set, Schema.ini can be defined in each subfolder.

To allow you to define a Schema.ini only when necessary, you can also use IncludeFiles and ExtendedProperties.

ExtendedProperties is compatible with Microsoft Jet OLE DB 4.0. The format for all text files can be set in ExtendedProperties. Schema.ini overrides ExtendedProperties for a specific file.

Files specified in Schema.ini are reported as tables in addition to files included by IncludeFiles. The cmdlet 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]

Specifying the File Format

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.

Defining 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 cmdlet 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 format is set to fixed length, then defining the width of each column is mandatory.

Schema.ini Example

Below is an example Schema.ini:

[Jerrie's travel expense.csv] 
ColNameHeader=True 
Format=Delimited(,) 
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(,) 
Col1=id numeric 
Col2=invoicedate date 
Col3=total numeric

Supported Data Types

Data types can be any of the following: