Removing a Carriage Return

[azizrasul]

I have a text file with x number of lines. When opening the file, I have an extra blank line which I presume is a carriage return,

How do I
1. ensure I don't have a blank line when I write to the file or
2. get rid of the extra line afterwards

Here's the code I've used for the data write: -

Private Sub Command0_Click()
 
    Dim rst As DAO.Recordset
 
    Set rst = CurrentDb.OpenRecordset("Table2", dbOpenDynaset)
 
    DoCmd.TransferText acExportDelim, "", "Table1", "D:\Table1.txt", False
    DoCmd.TransferText acExportDelim, "", "Table2", "D:\Table2.txt", False
 
    Open "D:\Table1.txt" For Append As #1
    Open "D:\Table2.txt" For Input As #2
 
    Do While Not rst.EOF
        Print #1, rst!FirstName
        rst.MoveNext
    Loop
 
    Close #1
    Close #2
 
    Kill ("D:\Table2.txt")
 
End Sub

[ADezii]

I have a text file with x number of lines. When opening the file, I have an extra blank line which I presume is a carriage return,

How do I
1. ensure I don't have a blank line when I write to the file or
2. get rid of the extra line afterwards

Here's the code I've used for the data write: -

Private Sub Command0_Click()
 
    Dim rst As DAO.Recordset
 
    Set rst = CurrentDb.OpenRecordset("Table2", dbOpenDynaset)
 
    DoCmd.TransferText acExportDelim, "", "Table1", "D:\Table1.txt", False
    DoCmd.TransferText acExportDelim, "", "Table2", "D:\Table2.txt", False
 
    Open "D:\Table1.txt" For Append As #1
    Open "D:\Table2.txt" For Input As #2
 
    Do While Not rst.EOF
        Print #1, rst!FirstName
        rst.MoveNext
    Loop
 
    Close #1
    Close #2
 
    Kill ("D:\Table2.txt")
 
End Sub

Typically a line containing a Carriage Return only will have a 0 length. Test for this condition before you write to the file. Modify the code within your Loop as follows, and let me know how you make out:

Do While Not rst.EOF
  If  Len(rst!FirstName) > 0 Then
    Print #1, rst!FirstName
      rst.MoveNext
  End If

Loop

[azizrasul]

Tried that, but made no difference. I don't think the Len function includes the vbCrLf in it's length evaluation.

[ADezii]

Tried that, but made no difference. I don't think the Len function includes the vbCrLf in it's length evaluation.

Try this and let me know again:

Do While Not rst.EOF
  If Instr(rst![FirstName], vbCrLf) = 0 Then      'If Carriage Return not in Line
    Print #1, rst!FirstName
  End If
  rst.MoveNext
Loop

[azizrasul]

I will try that tomorrow. Just a point here, in that there will be a carriage return for each record. It's just the LAST carriage return that I'm trying to get rid of. I'll try your suggestion and see how it goes.

[azizrasul]

It didn't work, unfortunately.

[ADezii]

It didn't work, unfortunately.

Give this a try. The Debug.Print statement will generate a Run Time Error when it comes to the Line with the last Carriage Return. It will go to the Handler which will close the File and the Recordset. The previous lines would have already been written to the Recordset. Let me know how you make out.

Dim strLine As String, MyDB As DAO.Database, MyRS As DAO.Recordset
 
On Error GoTo Err_Click
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblName", dbOpenDynaset)
 
 
Open "C:\Dell\Test.txt" For Input As #1
 
Do While Not EOF(1)
  Line Input #1, strLine
    Debug.Print Left$(strLine, Len(strLine) - 1)
  MyRS.AddNew
    MyRS![TheName] = strLine
  MyRS.Update
Loop
 
Close #1
MyRS.Close
 
Err_Click_Exit:
  Exit Sub
 
Err_Click:
  Close #1
  MyRS.Close
   Resume Err_Click_Exit: