Sometimes you may receive a text file to import that has a row or column delimiter not available in the standard drop-down boxes of the Text File Properties dialog.

The first thing to try in these cases is to type directly into the Row Delimiter or Column Delimiter box. This will work fine if the delimiter is a character you can easily type from your keyboard. Obviously non-standard characters or those such as tab or a carriage-return + line-feed (CR+LF) cannot be typed into the file format dialog. To workaround this you can programmatically set the delimiter.

You must first find out what the delimiter values really are. By using a Hex Editor, you can view the hexadecimal code for each character to determine the real values. Here is a simple example.

Hex Code	Text
61	a
09	Tab
0d	CR
0a	LF
62	b
09	Tab
0d	CR
0a	LF

In this example I only have two rows of data with a single column, but the column delimiter is made up of Tab+CR+LF, which cannot be typed directly into the Column Delimiter drop-down box. To set this programmatically I use an ActiveX Script Task, that runs before any task using my text file connection, to set the Column Delimiter property of the text file provider connection.

' Pkg 257
Option Explicit

Function Main()
	Dim oConn

	Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
	oConn.ConnectionProperties("Row Delimiter").Value = vbTab & vbCrLf
	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function

Here I have used the VBScript string constants for Tab and CR+LF, but you can also use the ASCII codes directly. By taking the hexadecimal values and converting them to decimal you can then match them against a table of ASCII characters for reference. These values can also be used with the Chr function. So our row delimiter property setting can be re-written as follows:

oConn.ConnectionProperties("Row Delimiter").Value = Chr(9) & Chr(13) & Chr(10)

If using a Unicode file see the ChrW function instead.

When working with files like this in the DTS designer you may encounter the message box question - "Could not find the selected row delimiter within the first 8 KB of data. Is the selected row delimiter valid?". If you try and work through the problem, selecting the closest available standard option (CR+LF), then answer yes to this message box it will appear to work, allowing you to import the data. If you take a closer look at what has been imported you'll find that the additional Tab character has been imported, since it was not stripped out as part of the delimiter. The may cause problems later, for example

WHERE MyColumn = 'a'

will fail to match my imported data, since the column actually contains two characters,

WHERE MyColumn = 'a' + CHAR(9)

.