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.

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.

I'm open to suggestions as to how to resolve this and really need to
get a solution soon.

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.

I've also tried importing to Access first and then SQL, as this has
worked for me in the past, but it did not work.


Regards,
Ciarán

(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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,

As I've never used PERL i'm trying to import the file to one column in
SQL using DTS.
I'm experiencing difficulty here too though....

The header row has a len of 143, and the records below can be much
longer.
The ipmort process is cutting the records after 143 characters, meaning
the whole file is not imported.
I've tried increasing the number of characters in the transform option
but it has not worked..

If you think using your PERL code is the best option, how to I get
started using PERL?
i.e. what software do i need to downlaod to drop you script into?

Any other suggestions that would help me are welcome.

Regards,
Ciarán

(chudson007@hotmail.com) writes:
> As I've never used PERL i'm trying to import the file to one column in
> SQL using DTS.
> I'm experiencing difficulty here too though....
>
> The header row has a len of 143, and the records below can be much
> longer.
> The ipmort process is cutting the records after 143 characters, meaning
> the whole file is not imported.
> I've tried increasing the number of characters in the transform option
> but it has not worked..

Unfortunately, I don't know DTS, so I can help with that part.

> If you think using your PERL code is the best option, how to I get
> started using PERL?
> i.e. what software do i need to downlaod to drop you script into?

Free download is on http://www.activestate.com/Products/ActivePerl/?mp=1.

Admittedly, Perl has bit of a learning curve, but once you know it, it's
a wonderful language to work with. There's plenty of books on the market.
Since I learnt Perl myself a long time ago, I cannot say which is the
best to start with today. It might be the so-called Lama book, "Learning
Perl" from O'Rielly.


--
Erland Sommarskog, SQL Server MVP, esquel@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