Using Schema.ini

Column names, data types, character sets, and data conversions may be specified for the Business Importer using a schema.ini file. This file contains the definition of the columns for any text files in the current directory, and overwrites all other settings, including Microsoft registry settings. Using the schema.ini file approach is useful, for example, when you need to define fixed length fields, or specify a custom delimiter.

For example, if you need to use a delimiter different than the one specified in HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text\Format (or the equivalent key for 32-bit systems), but for any reason you cannot update that registry setting, you may over-ride the registry with a setting in schema.ini. For example, suppose that the registry setting is CSVDelimited, but your imported file uses a Tab character as the delimiter. Until you create an appropriate schema.ini, the import will fail, typically by crushing all your imported columns into one column in the Business Adapter Studio. To over-ride the registry setting for a particular import, create a schema.ini containing a line such as the following:

Format=TabDelimited

Microsoft Windows offers an easy way to generate a default schema.ini file based on the existing text files in a directory.

To generate and adjust the schema.ini file:

1. To initiate the process, do one of the following:
   • On a 32-bit machine, access the Windows Control Panel and select ODBC from the icons in the control panel.
   • On a 64-bit machine, run the following command: C:\Windows\SysWOW64\Odbcad32.exe.
   The ODBC Data Source Administrator properties display.

   Figure: The ODBC Data Source Administrator screen

   

   Note: This tool is primarily used to create and manage ODBC data sources. However, it is used here simply to create a default schema.ini file.
2. Click Add....
   The Create New Data Source dialog displays.

   Figure: Pick the driver matching your data source

   

3. For CSV files or text files, select the Microsoft Text Driver.
4. Click Finish.
   The ODBC Text Setup dialog displays.

   Figure: Locate the CSV (or text) file

   

5. Click Select Directory... and browse to identify and select the CSV (or text) file that you want to use to import data.
6. Click Define Format....
   The Define Text Format dialog displays.

   Figure: Define the format of the text file

   

7. Use this dialog to identify the columns of data in your text file, and the data type of each column.
   Tip: Click Guess to allow the program to analyze the text file and provide default table and column details. You can then modify any incorrect details.
8. When you have finished defining the contents of the text file, click OK to return to the ODBC Text Setup dialog.
9. Click Cancel. The data source is not set up, but a new schema.ini file is created in the same folder as the text file. The schema.ini contains a definition of the tables and columns of the text file.
10. You can now edit the schema.ini file as desired, using a text editor such as notepad.exe or wordpad.exe.
    Tip: For further information about configuring a schema.ini file, see http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx.