Error removing last vbCrLf character from a text file created by FSO CreateTextFile

Discussion in 'ASP General' started by Andyza, Feb 15, 2006.

  1. Andyza

    Andyza Guest

    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
    Andyza, Feb 15, 2006
    #1
    1. Advertising

  2. Did you try it like this:-


    objStream.Write strText
    rs.MoveNext
    If Not rs.EOF Then objStream.write vbCrLf
    Loop
    Anthony Jones, Feb 15, 2006
    #2
    1. Advertising

  3. Andyza

    Andyza Guest

    Perfect!
    Thanks
    Andyza, Feb 16, 2006
    #3
  4. Andyza

    Andyza Guest

    > Did you try it like this:-
    >
    > objStream.Write strText
    > rs.MoveNext
    > If Not rs.EOF Then objStream.write vbCrLf
    > Loop


    Thanks. It worked perfectly.

    But then I tried moving the code into a Function to make it reusable:

    Function PrintCategory(Category)
    strText = strRecordNumber & VbTab & rs("somefield") & VbTab &
    Category & VbTab & rs("anotherfield") & VbTab & "X"
    objStream.Write (strText)
    If Not rs.eof Then
    objStream.Write vbCrLf
    End If
    End Function

    And I call it from the main body of the page using:

    Set rs = conn.Execute("SELECT X1,Y2,Z3 FROM SomeTable WHERE Something
    = '12345'")
    Do While Not rs.eof
    If rs("X1") = "Yes" Then
    PrintCategory(01)
    End If

    If rs("Y2") = "Yes" Then
    PrintCategory(02)
    End If

    If rs("Z3") = "Yes" Then
    PrintCategory(03)
    End If

    rs.MoveNext
    Loop


    The problem is that I now get a blank line/carriage return at the end
    of the file. As in my original post, I don't want the text file to have
    this blank line / carriage return after the LAST record.

    Where have I gone wrong?
    Andyza, Feb 17, 2006
    #4
  5. In my code it was important that the MoveNext method on the recordset be
    called before testing EOF.

    Your code is no longer doing that.
    Anthony Jones, Feb 17, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Johny
    Replies:
    8
    Views:
    385
  2. spradl

    FileSystemObject.CreateTextFile Problem

    spradl, Aug 12, 2003, in forum: ASP General
    Replies:
    9
    Views:
    173
    Ray at
    Aug 14, 2003
  3. Rik Hoeflaak

    Permission problem on files created by FSO

    Rik Hoeflaak, Jan 16, 2004, in forum: ASP General
    Replies:
    0
    Views:
    95
    Rik Hoeflaak
    Jan 16, 2004
  4. Carol
    Replies:
    4
    Views:
    173
    Carol
    Jul 30, 2004
  5. Replies:
    1
    Views:
    215
    Scott
    Apr 12, 2006
Loading...

Share This Page