Empty line when exporting csv (1 Viewer)

[G81]

Hi, great forum here. It has helped me out countless times.

I'm exporting a table to csv using the transfertext method.

Then im inserting a custom header and footer using vba writeline and writeline readall for the main csv body.

The problem I have, is when exporting the original csv there is a blank line at the bottom. So when I writeline the footer, it appears after the blank line.

I was wondering if there is a simple way to do this? I can think of messy ways with counts of lines etc, but wondered if there was a function I could use in vba or something in the original export spec I could include.

The file ends up looking something like this:

Header1234
561651,sgdg,541651
561651,sgdg,541651
561651,sgdg,541651
561651,sgdg,541651

footer,4

 

[namliam]

If you need specific header/footer information your best off not using a 'automated' export becuase that is unable to do this.
It would be "better" (or the way I do this) is to manually export the csv in code

 

[G81]

If you need specific header/footer information your best off not using a 'automated' export becuase that is unable to do this.
It would be "better" (or the way I do this) is to manually export the csv in code

Hi thanks for replying.

Do you mean read the table into a recordset and then build the csv line by line including header and footer?

I thought about doing this but basically managed to add the custom header and footer quite easily (apart from that empty line) and was wondering if there was a good way of deleting that end empty line. My code after exporting the original csv is like this:

Public Function preparecsv()
 
Dim fso As Object, ts1 As Object, ts2 As Object
Dim qty As String
Dim db As Database
Dim rs As DAO.Recordset
 
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts1 = fso.OpenTextFile("c:\temp.csv")
Set ts2 = fso.CreateTextFile("c:\" & Format(Now(), "yyyymmddhhMM") & ".csv", True)
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select Sum(Qty) As TotalQty From tbltemp")
If rs.EOF = False Then
    qty = rs("TotalQty")
Else
    qty = "0"
End If
 
With ts2
    .WriteLine "START"
    .WriteLine ts1.ReadAll
    .WriteLine "END," & qty    
    .Close
End With
 
ts1.Close
fso.deletefile ("c:\temp.csv")
Set ts1 = Nothing
Set ts2 = Nothing
Set fso = Nothing
 
End Function

c:\temp.csv has a blank line at the end. So there's a blank line before the (.WriteLine "END," & qty)

Is there a way I can either do something after .WriteLine ts1.ReadAll to move back a line before doing the .WriteLine "END," & qty

or do something before the .WriteLine ts1.ReadAll to modify the last line.

 

[G81]

I played around some more and found a solution. I replaced the

.WriteLine ts1.ReadAll
.WriteLine "END," & qty

with

.Write ts1.ReadAll
.Write "END," & qty

Im assuming the difference between writeline and write is that write appends to last line, but writeline doesnt and adds a carriage return to begin a new line??? Can anyone confirm this?

 

[vbaInet]

That's about right!

 

[G81]

thankyou