Posted by Erland Sommarskog on 03/11/06 20:26

(chudson007@hotmail.com) writes:
> Can somebody help me with a delimiter problem I have.
>
> I have several PIPE (|) delimted text files which I need to import to
> SQL.
>
> With one of the files I keep encountering the following error;
> "Error at Source for Row 27753. Errors encountered so far in this task:
> 1. Column Delimter not found."
>
> I suspect the problem is that one record (and possibly more) has a PIPE
> (|) within a field, because some of the fields contain free text.

Or there are data with newlines in it.

> Getting an export of the file again using a different delimter like tab
> or comma will not work as these characters occur throughout the file.

You could use a more complex delimiter. Juding from your error message,
you are usingh DTS, which I don't know much about. In BCP, my
favourite for character-based export is

-c -t @!@ -r "\n<->\n"

which sets the field separator to @!@ and the record separator to
<-> alone on a line.

> One solution I was thinking of, but do not know how to execute is to
> count the number of PIPEs on each record and then manually change the
> records which have count which is inconsistent with the rest of the
> file.

Here is a Perl script that you could use to track down problematic
lines in the file:

use strict;

my $no_of_fields = 5;
my $delim = qr/\|/;
my $lineno = 1;
my $file = 'E:\temp\slask.txt';

open(F, $file) or die "Cannot open '$file': $!\n";

my $line;

while (defined ($line = <F>)) {
my @fields = split($delim, $line);
if (scalar(@fields) != $no_of_fields) {
warn "Line: $lineno, no of fields: " . scalar(@fields) . "\n";
}
$lineno++;
}

close F;

To do it in T-SQL, you could import the file to a one-column per
table, with one line in the file per row. You could then find the
odd rows with:

SELECT * FROM tbl
WHERE len(data) - len(replace(data, '|', '') <> @no_of_fields

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]