DTS Error: "Too many columns found in the current row"

[PRMiller]

Afternoon,

Having a problem importing a text file using DTS. I have not run into this before.

My source is a pipe-delimited file with no text qualifiers. Sample of the data:

Extension|Name|Vector Number|Measured|Acceptable Service Level|BSR Application|BSR Agent Strategy
00001|866-604-3265-VPRV|1|external|20||1st-found
00003|ICMPrime/P2.3|1|none|20||1st-found
00004|IP remote agent test|703|none|20||1st-found
00007|8777325474 to skill 23 not5|1|none|20||1st-found
00008|VRUtoICM VDN23988 to sk 23|1|none|20||1st-found
00010|Spanish Non-IVR to Skill 5|758|none|20||1st-found
00011|Spanish Non-IVR to skill 7|769|none|20||1st-found
00012|Spanish Non-IVR to skill 8|771|none|20||1st-found

When trying to configrue it as a source, I receive the following error:

"Too many columns found in the current row; non-whitespace characters were found after the last defined column's data."

After searching through the forums, I checked a couple suggestions. First up, I imported to Excel and found that I have 2,786 rows and 7 columns. Obviously shouldn't be a read buffer overflow issue.

Next, I set up error logging, but I don't have access to the logs on the server. I have an e-mail out to the sysadmin, but not sure how long that will take.

The part I'm curious about is the second half of the error message. What does that mean? Does it indicate that one of my rows has one too many columns? Using Excel, I scrolled through the data and this didn't appear to be the case.

On the other hand, does the fact that I have seven column headers but only six columns of data have any bearing? Shouldn't the || between six and seven resolve that?

Any thoughts?

 

[SQLDenis]

What is your row terminator?
Is this file coming from a Unix system?

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[PRMiller]

Denis,

I'm not quite sure what the row terminator is. I'm exporting from Avaya's Site Administration utility to a text file. Avaya does prompt for field delimiters and text qualifiers, and also allows exporting of column heads, but does not prompt for a row terminator.

Interestingly enough, I had a similar process set up at my old company, where we used version 2.9 of the software. At my new office, we're using version 1.13. Not sure what may have changed in the export process between versions.

How would you suggest I determine the row terminator? Looking in the raw text file, the last characters are typically letters, as part of the last data element of each row. For example

00003|ICMPrime/P2.3|1|none|20||1st-found

 

[SQLDenis]

a row terminator is usually the CR + LF (Carriage return + Line Feed) on some system it's only line feed or carriage return
You can specify the row terminator in DTS in the file properties

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[PRMiller]

Well, it's official: I'm an idiot. In the DTS file properties I was misreading "row delimiter" as "column delimiter," and changing it to "|" instead of leaving it defaulted to CRLF.

<SIGH>

Thanks for making me think! I think it's time to go home...