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

SOLUTION
Try changing the "Row delimiter" field from {CR}{LF} to {CR} within the
"Select file format" dialog of DTS Import Data wizard (it's the 3rd
dialog of the Wizard).

REASON
I believe it has to do with the way a line of text is terminated under
UNIX vs. Windows because everytime I've encountered this error it was
while importing a text file downloaded from a UNIX FTP site.

LB
"Do not attempt to be all things to all people. Unless, of course,
you'd like to live your life burnt out, exhausted, and unappreciated."

After researching this a little further, it appears that the DTS can be
told to expect any number of columns in the import file provided that
there is a row with the max number of columns found in the first 2mb of
data. Microsoft doesn't consider it a bug but it is. Here are the
details:

1) Import file greater than 2mb w/ varying number of columns in rows
based on delimiter

2) DTS configured to import specific number of columns from each row
that is a subset of the average number of columns (i.e. Load first 12
columns of data for each row that averages 15 columns)

3) First 2mb of file contains rows with a maximum number of columns
less than the maximum number of columns in rows found in data after the
first 2mb (i.e. Max rows in first 2mb might be 15; a few rows after the
first 2mb may have more columns than the average number of 15 and each
of them will return the same error).

Note: The number of columns is not specific to the problem and they are
only provided to help clarify.


Fix:
Ensure a row containing the maximum number of columns exists in the
first 2mb of data.

This can be accomplished (for example) through the use of a
temporary/load table containing one column. The table can be loaded
with all the rows and a query run against it to determine the maximum
number of column delimiters. Take the max number of delimiters and
subtract it from the number of delimiters in the first row. Add that
number of delimiters to the end of the data in the first row.

This is not the only solution...just an example of how to work around
the problem. Other examples would be welcomed posts.



lbunet@yahoo.com wrote:
> ERROR
> "Too many columns found in the current row; non-whitespace characters
> were found after the last defined column's data."
>
> SOLUTION
> Try changing the "Row delimiter" field from {CR}{LF} to {CR} within the
> "Select file format" dialog of DTS Import Data wizard (it's the 3rd
> dialog of the Wizard).
>
> REASON
> I believe it has to do with the way a line of text is terminated under
> UNIX vs. Windows because everytime I've encountered this error it was
> while importing a text file downloaded from a UNIX FTP site.
>
> LB
> "Do not attempt to be all things to all people. Unless, of course,
> you'd like to live your life burnt out, exhausted, and unappreciated."