Use TIBCO Scribe® Insight to create a source connection to one or more text files. Some uses for text files include:

• Quickly enter data into a target datastore, such as Microsoft Dynamics CRM or Salesforce.
• Test DTS files and formulas with a small, controllable amount of data.
• Migrate data exported from a product for which there is no TIBCO Scribe® Adapter or Connector.

NOTE: If your data contains quote marks see the Using Data Containing Quotes section below.
 

Creating The ODBC DSN

Create the ODBC DSN using the Scribe Text ODBC DSN driver installed with TIBCO Scribe® Insight. The DSN specifies the path and file name that the DTS is expecting for the source connection. In this process, configure the file format information used to parse the text file.

1. In the TIBCO Scribe® Insight Workbench, click View > Connections.
2. In the Connection Manager click New.
3. Select ODBC Data Sources > Add/Modify ODBC Data Source. The ODBC Data Source Administrator dialog displays.
4. Click the System DSN tab.
5. Click Add. The Create New Data Source dialog displays.

 

6. Select the Scribe Text File option and click Finish. The ODBC Text Driver Setup dialog displays.

7. Enter a Data Source Name.
8. Enter the path to the data source in the Database Directory field.
9. Select a Default Table Type. The default is Comma.

10. Click Advanced.

11. On the Advanced tab, click Define.
12. Browse to the text file and click Open.

13. On the Define Table dialog review the settings associated with the file to make sure it is parsed correctly.For each field modify the Precision field as needed, especially for strings/text fields. The Precision field value controls maximum field length.

14. Click OK.
15. Click Cancel on the browser window.
16. On the ODBC Text Driver Setup dialog, click OK.
17. On the ODBC Data Source Administrator, click OK.
18. On the Add A Connection dialog, select the new ODBC Connection and click OK.

 

19. On the Connection Settings dialog, click OK.
20. On the Connection Manager dialog, verify that the new connection is in the list and click Close.

When finished a QTEXT.ini file is created with the settings from the Define Table dialog and the file definition. This file always needs to reside in the folder with the CSV file.
 

Using Fixed Width Columns

If the text file uses fixed width columns, an Offset value must be specified in the ODBC DSN on the Define Table dialog.

In the example below there are 4 fixed width colums of size 3, 7, 30, 30.
  On the Define Table dialog you must specify the Precision, Length, and Offset for each field.  The first offset is 0 because this is the offset from the beginning of the record.
  The second field is defined with the appropriate Precision and Length with the offset being the number of characters from the beginning of the record until the start of the column. Or to put it another way, the total of all of the previous offsets.
 
The fields will be defined in the QTEXT.ini file as follows:
The numeric values (3,0,3,0) correspond to Precision, Scale, Length, Offset settings.
 
The QTEXT.ini file is created when you configure the text file parameters in the Define Table dialog and is stored in the folder where the text file is kept.
 

Add The Source Connection To A DTS

1. In the TIBCO Scribe® Insight Workbench, click Configure Source.
2. On the Configure Source dialog select your new Text Connection.
3. In the tree below the connection name, open All Data Objects (by type) > Tables and select the table with the correct source data.
4. Click OK. The fields display in the Source Pane.

Using Data Containing Quotes

There can be situations when the Scribe Text ODBC driver does not work properly when the data contains quote marks. When there are quote marks in the data an incomplete field definition list is generated during the creation of the ODBC DSN. This is the QTEXT.ini file stored with the text file. Fields may be merged during runtime (e.g. the delimiters are handled properly)

To work around this problem do the following:

1. Create the ODBC DSN using a set of data with no quotes. The field definition is created properly and contains all fields.
2. Add an entry to the ODBC DSN record created in the Windows registry. For 64 bit servers this is in the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI tree structure.
3. Locate the name of the DSN that was created and add a new String Value named EnableDoubleQuotedData.
4. Set the value to 0. When set to 0, double quotes are always treated as part of the data.
5. To return to the default setting, set the value to 1 or remove the entry from the registry.

For additional information see the following:

TIBCO Scribe® Insight Help: Connecting To An ODBC Data Source, Connecting To A Text Source
Knowledge Base Article: Creating A File Based Integration
 

Use TIBCO Scribe® Insight to create a source connection to one or more text files.