(ch********@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
