A
Andyza
I'm using FileSystemObject to open and write to a tab delimited text
file.
First, I connect to a database and select some data. Then I create the
text file and insert each record in the text file. At the end of each
record I insert a vbCrLf character in the text file to create a new
line. Thus each record is in a new line.
The problem is that I get a tab delimited text file where the LAST LINE
in the text file is always a blank line. I need to remove the LAST
vbCrLf character from the text file so that there is no blank line at
the end of the text file.
I've tried checking for AtEndOfStream in a Loop to try and remove the
LAST vbCrLf character, but there's something wrong with the code...
The Do While Not objStream.AtEndOfStream loop is crashing with
"Microsoft VBScript runtime (0x800A0036) Bad file mode" errors.
What have I done wrong?
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strDataFileName = Server.MapPath("SomeFolder\TextFile.txt")
'Delete any existing Format 01 text file (from previous batches)
If (objFSO.FileExists(strDataFileName)) Then
objFSO.DeleteFile(strDataFileName)
End If
'Create blank text file
strDataPath = Server.MapPath("SomeFolder\")
Set strCreateFile = objFSO.CreateTextFile("TextFile.txt", True)
Set strCreateFile = Nothing
Const ForWriting = 2
Set objStream = objFSO.OpenTextFile(strDataFileName, ForWriting, True)
strSelect = "SELECT X,Y,Z FROM SomeTale WHERE SomeCondition = '12345'"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")
Set rs = conn.Execute(strSelect)
'Add the data to the text file
Do While Not rs.eof
strText = rs("SURNAME") & VbTab & rs("FIRSTNAME") & VbTab &
rs("DSINITIALS") & VbTab & VbTab & VbTab & "0.00" & VbTab & rs("ID") &
VbTab & rs("BLAH") & VbTab & "X"
objStream.Write (strText & vbCrLf) 'This inserts a vbCrLf
character at the end of each record
rs.MoveNext
Loop
rs.Close : Set rs = nothing
conn.Close : Set conn = nothing
'Remove the last VbCrlf from the text file.
Do While Not objStream.AtEndOfStream
CLine = objStream.ReadLine
CLine = Replace(CLine,VbCrlf,"")
strDataFileName.Write CLine
Loop
objStream.Close : Set objStream = Nothing
Set objFSO = Nothing
file.
First, I connect to a database and select some data. Then I create the
text file and insert each record in the text file. At the end of each
record I insert a vbCrLf character in the text file to create a new
line. Thus each record is in a new line.
The problem is that I get a tab delimited text file where the LAST LINE
in the text file is always a blank line. I need to remove the LAST
vbCrLf character from the text file so that there is no blank line at
the end of the text file.
I've tried checking for AtEndOfStream in a Loop to try and remove the
LAST vbCrLf character, but there's something wrong with the code...
The Do While Not objStream.AtEndOfStream loop is crashing with
"Microsoft VBScript runtime (0x800A0036) Bad file mode" errors.
What have I done wrong?
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strDataFileName = Server.MapPath("SomeFolder\TextFile.txt")
'Delete any existing Format 01 text file (from previous batches)
If (objFSO.FileExists(strDataFileName)) Then
objFSO.DeleteFile(strDataFileName)
End If
'Create blank text file
strDataPath = Server.MapPath("SomeFolder\")
Set strCreateFile = objFSO.CreateTextFile("TextFile.txt", True)
Set strCreateFile = Nothing
Const ForWriting = 2
Set objStream = objFSO.OpenTextFile(strDataFileName, ForWriting, True)
strSelect = "SELECT X,Y,Z FROM SomeTale WHERE SomeCondition = '12345'"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")
Set rs = conn.Execute(strSelect)
'Add the data to the text file
Do While Not rs.eof
strText = rs("SURNAME") & VbTab & rs("FIRSTNAME") & VbTab &
rs("DSINITIALS") & VbTab & VbTab & VbTab & "0.00" & VbTab & rs("ID") &
VbTab & rs("BLAH") & VbTab & "X"
objStream.Write (strText & vbCrLf) 'This inserts a vbCrLf
character at the end of each record
rs.MoveNext
Loop
rs.Close : Set rs = nothing
conn.Close : Set conn = nothing
'Remove the last VbCrlf from the text file.
Do While Not objStream.AtEndOfStream
CLine = objStream.ReadLine
CLine = Replace(CLine,VbCrlf,"")
strDataFileName.Write CLine
Loop
objStream.Close : Set objStream = Nothing
Set objFSO = Nothing