Can someone please help me out here? I have been trying relentlessly
to solve DTS transformation issue. I'm trying to import a pip "|"
delimeted text file that contains 1.5 - 2.0 million records. Around
record 65,000 I get the famous error "Too many columns found in the
current row; non-whitespace characters were found after the last
defined column's data"

According to DTS my flat file contains 28 columns. I've tried
importing the whole file and I've tried importing only a few columns
in hopes that my problem would disappear. For some reason it appears
that DTS is recognizing that there is possbily another column? I'm
not sure.

The row delimiter is {CR}{LF}
I have batch size set to 10000
Max Error count is set for 3

I would manually look for the error in the recordset, however, the
data in this file is ever changing and is imported once a night.

If I can simply skip over this phantom column and continue on
importing that would be great.

Sure hope someone can shed some light.

Thanks,
-Tony

Hi Tony,

Two things that you can do. Since you're using SQL Server 2000 you can
log the source and destination columns within the transform when an error
occurs. Also, try increasing your max error count and see if that helps.
One other thing that you can do is to manually insert a record with one
more column as the first record in the file. When DTS cracks the file it
will see this column and make it available as a source column. You can then
tell DTS to ignore the column. The problem that DTS is having is that you
said there are only supposed to 28 columns, but it's not seeing a CR/LF
after the 28th column and it doesn't know what to do. By inserting the fake
record, with the 29th column, you're just telling DTS that it's okay to see
29 columns...of course this could go on for a while, adding columns, since
it sounds like you're not producing the file, someone else is, you'll never
know how many columns they chose to put into the file.
If you can get the row number, you can use something like the following
to pindown the errant line (It's slow, but it works):

' START
Dim sInputLine, sMain, s
Dim i, iP, iEndFileName
Dim fso, tf, f
Dim nStartPos, iLineCnt
Dim iPopupDelay
Dim varAr
Dim objArgs
Const TEMPFILE = "C:\Temp\QuickLookTempFile.txt"
nStartPos = 1
iLineCnt = 200
iPopupDelay = 4
Set objArgs = WScript.Arguments
If objArgs.Count > 0 Then
sInputLine = objArgs(0)
Else
sInputLine = InputBox( _
"Enter Full name of File:" & vbCrLf & vbCrLf _
& "Arguments allowed after file name:" & _
vbCrLf & "[number of lines to " & _
"show] [line to start at]" & vbCrLf & _
"Use a single space for argument separator." , _
"Display ten lines of a file", "C:\")
sInputLine = Trim(sInputLine)
End If
Set objArgs = Nothing
If sInputLine = "" Then
DisplayMsg "No file name entered."
WScript.quit (0)
End If
i = InstrRev(sInputLine, ".")
If i = 0 Then
DisplayMsg "The filename " & sInputLine & _
" has no extension."
WScript.quit (0)
End If
i = inStr(i, sInputLine, " ")
If i > 0 Then
iEndFileName = i - 1
s = Trim(Mid(sInputLine, i))
If Len(s) > 0 Then
varAr = Split(s, " ")
If UBound(varAr) > 0 Then nStartPos = _
CLng(varAr(1))
iLineCnt = CLng(varAr(0))
s = ""
End If
sInputLine = Left(sInputLine, iEndFileName)
End If
Set fso = WScript.CreateObject( _
"Scripting.FileSystemObject")
If Not (fso.FileExists(sInputLine)) Then
DisplayMsg "The File " & sInputLine & _
" does not exist."
Set fso = Nothing
WScript.quit (0)
End If
Set tf = fso.OpenTextFile(sInputLine)
i = 1: iP = 0
Do While tf.AtEndOfStream <> True
sMain = tf.ReadLine
If i >= nStartPos Then
s = s & sMain & vbCrLf
iP = iP + 1
End If
i = i + 1
If iP >= iLineCnt Then Exit Do
Loop
tf.Close
Set f = fso.CreateTextFile(TempFile)
f.Write (s)
f.Close
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "notepad " & TempFile
Set fso = Nothing
Set WshShell = Nothing
Sub DisplayMsg(sMag)
Set WshShell = _
WScript.CreateObject("WScript.Shell")
WshShell.Popup sMsg, iPopupDelay, _
"Exiting Windows Script Host", _
vbOKOnly + vbInformation
Set WshShell = Nothing
End Sub
' END

--
Regards,

Don R. Watters
Data Group Manager
PhotoWorks, Inc.


"Tony Opiola" <dealerof21 (AT) yahoo (DOT) com> wrote

Hi Don,

Thanks for the script!

I was able to match up the error line in the SQL log file with the
script you posted. It worked like a charm. It turns out that the DTS
assumes there are 28 columns in the flat file, however, further into
the file, around row 65600, three more additional columns where
discovered. Now I wonder if there is a way to trick DTS into thinking
there are 31 columns instead of 28.

What I may do is rewrite the flat file with a bogus record as a
generic header record containing 31 columns. This way DTS will pick
up 31 columns, and then I'll do the import?

Thanks for all your help!
-Tony


"Don R. Watters" <DonW (AT) PhotoWorks (DOT) Com> wrote

Hi Tony,

Glad that helped. As I pointed out in the first post I made, the way
to trick DTS into thinking there are 31 columns is to do exactly what you're
thinking...post a bogus header and ignore the last three columns. Hope that
works.

--
Regards,

Don R. Watters
Data Group Manager
PhotoWorks, Inc.


"Tony Opiola" <dealerof21 (AT) yahoo (DOT) com> wrote

Hey Tony

You may need to work around the problem. You could

1. Code a parser in vbscript
2. Import records into msaccess and then to sql server

I know these have their own limitations, but they are options nevertheless

K