Connect CSV and TXT Text Sources to SuperCHANNEL

SuperCHANNEL can read data from a text file, by using the Microsoft ODBC Text Driver through the jdbc:odbc bridge.

Limitations of the jdbc:odbc Bridge

There are a number of limitations when using the jdbc:odbc bridge:

It cannot recognize table joins. You will have to construct table references in the SuperCHANNEL GUI.
It supports a maximum of 64 characters for column names. Any text beyond this limit will be truncated.
It does not support Unicode characters.

In addition, please note that the jdbc:odbc bridge has been removed from Java 8 onwards.

You are recommended to consider using a relational database instead.

The jdbc:odbc bridge is also not recommended for large data sets. You are strongly recommended to use a relational database instead, connecting using a JDBC driver.

If using a relational database is not an option for you, then you may be able to channel your data using Space-Time Research's Textual Database Definition format.

Step 1 - Prepare the Text Source File

The first step is to prepare your text source file(s) as follows:

Requirement

Description

Format

Your text file(s) must either be delimited or fixed-width:

Delimited	Can be delimited either by columns, tabs or a custom delimiter (the same delimiter must be used throughout the file). Null values are denoted by two delimiters in a row with no data between them. Character strings can be enclosed in double quotes (""). No blanks can occur before or after delimited values.
Fixed Width	The width of each data entry must be specified in a schema. Null values are denoted by blanks.

Column Headings

The text file can have an optional header line and zero or more text lines. Although the header line uses the same format as the other lines, the ODBC driver can interpret the header line entries as column names.

Maximum Table Size

255 fields.

Maximum Field Name Size

64 characters.

Maximum Field Width

32,766 characters.

Maximum Record Size

65,000 bytes.

Step 2 - Create the ODBC Data Connection

Start the ODBC Data Source Administrator. You must use the version (64 bit or 32 bit) that matches the version of SuperCHANNEL you have installed:
- If you are using 64 bit SuperCHANNEL, open the Windows Control Panel and browse to System and Security > Administrative Tools, then open Data Sources (ODBC):
- If you are using 32 bit SuperCHANNEL, browse to C:\Windows\SysWOW64 and open odbcad32.exe:
Both versions of the administration tool have the same appearance and operation.

The ODBC Data Source Administrator window displays.

Select either the User DSN, System DSN or File DSN tab, depending on which type of Data Source Name (DSN) you want to create. This is the data structure that will define the connection between SuperCHANNEL and your text file.

The type you select determines who is able to access the DSN you create:

Type	Who Can Access?	Where Is Connection Information Stored?
User DSN	Only the user who created the data source.	System registry.
System DSN	Any user of this system. You may not have permission to create this type of data source.	System registry.
File DSN	Any user with access to the data source file.	In a data source file (.dsn)

Click Add.
Select Microsoft Access Text Driver and click Finish.

If the text driver is not listed, then you will need to download it from Microsoft. Make sure you download the correct version (32 bit or 64 bit).

The ODBC Text Setup dialog displays.
In the Data Source Name field, enter a name for the data source. You may also want to enter a description in the Description field, to help you keep track of your DSNs, although this is not required.
Clear the Use Current Directory check box, and click Select Directory, then select the directory containing your text files.
Do the following to define the column headings:
1. Click Options >>
2. Click Define Format. The Define Text Format dialog displays, showing a list of all the files in your chosen directory.
3. Select a file from the list:
4. Select the Column Name Header check box.
  
  If your text file does not have a header line, leave this check box unselected. Your columns will be represented by default column names (F1, F2, F2, etc) and you will be able to manually enter the column headings.
5. In the Format field, select the format of the selected file.
6. Click Guess. The column names display in the Columns pane.
7. If necessary, you can step through each heading to change the Data Type, Name of the heading and Width (if applicable). You will need to do this if the guesses do not match the contents of your file, or if your file does not have column headings, in which case you can enter them manually.
8. Repeat these steps to define the headings for all your files, then click OK.
Click OK on the ODBC Text Setup dialog to create your driver and save the settings.
If you need to change any of the settings, select your data source in the list and click Configure.

Step 3 - Start SuperCHANNEL

Start SuperCHANNEL, and use the following connection string to connect to the text data source:

CODE

jdbc:odbc:<datasource_name>

Where <datasource_name> is the name of the data source you created.

For example:

CODE

jdbc:odbc:text_samples

If SuperCHANNEL displays the following error message, then this indicates that the data source version (32 bit / 64 bit) does not match the version of SuperCHANNEL you are using:

"The specified DSN contains an architecture mismatch between the Driver and Application"

Go back to the start and recreate the data source using the correct data source version.

Managing Changes to the Text Data Source Format

If you make any changes to the format of your data (such as adding a new column), then these changes will not be picked up automatically. You must update the ODBC data source definition whenever you make changes to the text file format.

You have two options for doing this:

Run the ODBC Data Source Administrator again and select the option to Configure the data source, then repeat the Guess process.
Edit the schema.ini file directly. This is a text file that is created automatically when you set up the data source. It is located in the same directory as your text files.