I am trying to write a script to read a tab delimited text file. I am not
clear on how to map the tabbed data to a column. I am not a programmer but
I am ttying and need some guidance. I found the following and got it work
as far as reading the tabbed file, but how to do the tab data to a column
for insert? I have done this fixed width files before but the tab-delimited
is getting me. I started down the road of building the Values for
insert..not sure if this is correct way or not. I end up with a extra
comma, too.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Const ForReading = 1, ForWriting = 2, ForAppend = 8
dim fso, tsFile, varLine, strDelimiter, strLine, ValueMsg, strTextFilePath
strTextFilePath = "d:\mfe20051002.log"

set fso = createobject("Scripting.FileSystemObject")
set tsFile = fso.OpenTextFile(strTextFilePath, 1)
strDelimiter = vbTab '(for tab delimited)
'strDelimiter = "," '(for comma delimited)
strLine = tsFile.ReadLine
strLine = tsFile.ReadLine
do while not tsFile.AtEndOfStream
strLine = tsFile.ReadLine
varLine = split(strLine, strDelimiter)
ValueMsg = "Values("
for ii = 0 to ubound(varLine)
ValueMsg = ValueMsg & varLine(ii) & ", "
next
' msgbox ValueMsg
loop

Main = DTSTaskExecResult_Success
End Function

Why not just use DTS proper to do the import rather than coding thsi all
yourself?

Can the Text File connection not handle the format?


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"CD" <mcdye1 (AT) hotmail (DOT) REMOVETHIS.com> wrote

Thank for the reply,

I am going down this road due to the fact that the file is in use. Using
the Text Connection in DTS it is not able to open the file. But using
notepad I can open the file. My thought is the TextConn is trying to open
the file in a locked mode, maybe? Hence trying to open this way.

I tired the code below and was able to open the active file as expected.

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote