Error Writing CSV File - Trailing Delimiters Dropped from Final Lines
Posted on 2004-04-11
I am using VB to create a CSV file that has a header line with more populated columns of data than the detail lines. When I try to import the file into another app, I get an EOF error. The csv file looks fine when I open it in notepad. If I open it in Excel and save as a csv, then it is apparent that the column placeholders for the empty trailing columns of the last detail lines in the file are lost. The trailing commas from the last few details lines are missing, but they are present for all other details lines in the file.
I have tried 1) adding a carriage return line feed character (vbcrlf) after the last detail line and it had no impact on correcting the formatting. Also have 2) added a dummy header line after the last detail line and this does work to save the trailing commas on those detail lines but when I then re-open the file and copy all records except that last header line to a new file, the new file has the same messed up formatting as the original.
Here is an example of the last two header/detail combo lines in the csv file after I have opened in Excel and re-saved as csv:
Here are the basics of the code I am using to create the file:
Open csvStrname For Output As #1
'Write out three header records with field names.
Write #1, "RECTYPE", "CNTBTCH", "CNTITEM", "IDCUST", "IDINVC", "TEXTTRX", "ORDRNBR", "CUSTPO",
"INVCDESC", "DATEINVC", "SWTAXBL", "SWMANTX", "AMTDUE", "AMTTAXTOT"
Write #1, "RECTYPE", "CNTBTCH", "CNTITEM", "CNTLINE", "AMTEXTN", "IDACCTREV"
Write #1, "RECTYPE", "CNTBTCH", "CNTITEM", "CNTPAYM", "AMTDUE", "DATEDUE"
'Open each export file to be processed and combined into a single file.
Dim strInvNumber As String
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
rs1.Open "select * from [AR Header Export] order by ItemCount", cnn, adOpenDynamic, adLockOptimistic, adCmdText
rs2.Open "select * from [AR Type 2 Export] order by ItemCount, LineCount", cnn, adOpenDynamic,
rs3.Open "select * from [AR Type 3 Export] order by ItemCount", cnn, adOpenDynamic, adLockOptimistic, adCmdText
Do Until rs1.EOF
Write #1, rs1!e1, rs1!e2, rs1!ItemCount, rs1!F1, rs1!F5, rs1!e3, rs1!F3, rs1!e4, rs1!e5, rs1!F15, rs1!e6, rs1!e7, dblInvoiceTotal, rs1!e8
Do Until rs2!ItemCount <> rs1!ItemCount
Write #1, rs2!e1, rs2!e2, rs2!ItemCount, rs2!linecount, dblInvoicedetail, rs2!F1
If rs2.EOF Then
'Write a single detail/type 3 record for each header.
Write #1, rs3!e1, rs3!e2, rs3!ItemCount, rs3!e3, dblInvoiceTotal, strAddDays(rs1!F15, 30)