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.
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
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.ConnectionProperties("Row Delimiter").Value = vbTab & vbCrLf
Set oConn = Nothing
Main = DTSTaskExecResult_Success
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)