Solved

Error Writing CSV File - Trailing Delimiters Dropped from Final Lines

Posted on 2004-04-11
10
217 Views
Last Modified: 2010-05-02
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:

1,1,56,CASH,INV-0401429,1,1080770675,,,20040330,1,1,3.51,0
2,1,56,20,-2.29,560400-13,,,,,,,,
2,1,56,40,3.21,460400-13,,,,,,,,
2,1,56,60,0.27,211001,,,,,,,,
2,1,56,80,0.03,211002,,,,,,,,
2,1,56,100,2.29,140005,,,,,,,,
3,1,56,1,3.51,20040429,,,,,,,,
1,1,57,CC056,INV-0401398,1,1080770675,,,20040330,1,1,39.38,0
2,1,57,20,-18.75,581300-14,,,,,,,,
2,1,57,40,37.5,430200-14
2,1,57,60,18.75,140008
2,1,57,80,1.88,430300-14
3,1,57,1,39.38,20040429

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,
                adLockOptimistic, adCmdText
        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
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop

            '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)
            rs3.MoveNext
            rs1.MoveNext
        End If
    Loop

Close #1

Thanks!
Courtney
0
Comment
Question by:csryan
  • 3
  • 3
10 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 10803557
try to add blank strings if data is not there, something like this


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
                  ' first record has 14 columns so add 14 columns, 6 columns has data and remaining add blank strings
                Write #1, rs2!e1, rs2!e2, rs2!ItemCount, rs2!linecount, dblInvoicedetail, rs2!F1,"","","","","","","",""
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop

            '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),"","","","","","","",""

            rs3.MoveNext
            rs1.MoveNext
        End If
    Loop
0
 
LVL 3

Expert Comment

by:santosh26676
ID: 10803882
I agree with appari, you need to add atleast one blank text at the end of each string.
0
 

Author Comment

by:csryan
ID: 10805605
I tried this and the formatting still doesn't 'stick' on those last lines. Same result...

1,1,56,CASH,INV-0401429,1,1080770675,,,20040330,1,1,3.51,0
2,1,56,20,-2.29,560400-13,,,,,,,,
2,1,56,40,3.21,460400-13,,,,,,,,
2,1,56,60,0.27,211001,,,,,,,,
2,1,56,80,0.03,211002,,,,,,,,
2,1,56,100,2.29,140005,,,,,,,,
3,1,56,1,3.51,20040429,,,,,,,,
1,1,57,CC056,INV-0401398,1,1080770675,,,20040330,1,1,39.38,0
2,1,57,20,-18.75,581300-14,,,,,,,,
2,1,57,40,37.5,430200-14
2,1,57,60,18.75,140008
2,1,57,80,1.88,430300-14
3,1,57,1,39.38,20040429
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 39

Expert Comment

by:appari
ID: 10808734
it seems you didn't post complete code. i guess this because there is a "End If" without related if block.


            rs3.MoveNext
            rs1.MoveNext
        End If              <------------- where is the related if statement?

post complete code or change all the write #1 statements as explained in my first post.
0
 

Author Comment

by:csryan
ID: 10810410
I appreciate the help. The code gets a little convoluted, so I was trying to pull out the relevant parts. It creates different files for two different target applications and I had taken out the code for the 2nd app which is working fine (uses a single record format) and left in the code for the one having problems. Apologies for failing to remove the last endif - here is the chunk of code with the starting if statement... I had made your changes to the write statement and, as I reported, it did not resolve the problem with the csv file.  I would be grateful for any other suggestions you might have.
Thanks,
Courtney
 

    Do Until rs1.EOF
        dblAmount = 0
        i = 0
        'Loop through once to get total amount, then move back
        If dgGlobal.Software = OptionACCPAC Then
            Do Until rs2!ItemCount <> rs1!ItemCount
                dblAmount = dblAmount + rs2!F3
                i = i + 1
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop
            Do Until i = 0
                rs2.MovePrevious
                i = i - 1
            Loop
           
            'Write out the header/type 1 record.
            dblInvoiceTotal = dblAmount
            If dgGlobal.ReverseAmount Then
                dblInvoiceTotal = -dblInvoiceTotal
            End If
           
            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
                dblInvoicedetail = rs2!F3
'                dblInvoicedetail = -dblInvoicedetail
                If dgGlobal.ReverseAmount Then
                    dblInvoicedetail = -dblInvoicedetail
                End If
                Write #1, rs2!e1, rs2!e2, rs2!ItemCount, rs2!linecount, dblInvoicedetail, rs2!F1, "", "", "", "", "", "", "", ""
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop
            '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), "", "", "", "", "", "", "", ""
            rs3.MoveNext
            rs1.MoveNext
        End If
       
    Loop
0
 
LVL 39

Expert Comment

by:appari
ID: 10810553
the above code should append blank strings at the end.

just to check,

change all write #1 .... statements and add some unique number in the first column, something like this.

Do Until rs1.EOF
        dblAmount = 0
        i = 0
        'Loop through once to get total amount, then move back
        If dgGlobal.Software = OptionACCPAC Then
            Do Until rs2!ItemCount <> rs1!ItemCount
                dblAmount = dblAmount + rs2!F3
                i = i + 1
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop
            Do Until i = 0
                rs2.MovePrevious
                i = i - 1
            Loop
           
            'Write out the header/type 1 record.
            dblInvoiceTotal = dblAmount
            If dgGlobal.ReverseAmount Then
                dblInvoiceTotal = -dblInvoiceTotal
            End If
           
            Write #1, "Record1", 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
                dblInvoicedetail = rs2!F3
'                dblInvoicedetail = -dblInvoicedetail
                If dgGlobal.ReverseAmount Then
                    dblInvoicedetail = -dblInvoicedetail
                End If
                Write #1, "Record2", rs2!e1, rs2!e2, rs2!ItemCount, rs2!linecount, dblInvoicedetail, rs2!F1, "", "", "", "", "", "", "", ""
                rs2.MoveNext
                If rs2.EOF Then
                    Exit Do
                End If
            Loop
            'Write a single detail/type 3 record for each header.
            Write #1, "Record3", rs3!e1, rs3!e2, rs3!ItemCount, rs3!e3, dblInvoiceTotal, strAddDays(rs1!F15, 30), "", "", "", "", "", "", "", ""
            rs3.MoveNext
            rs1.MoveNext
        End If
       
    Loop


now run the code and post the output here.
0
 

Author Comment

by:csryan
ID: 10813519
Here it is... Any other suggestions? The really weird thing is that I have another version of this same program, same code, running in MS Access and it does not strip the commas when it saves.

Record1,1,1,56,CASH,INV-0401429,1,1080770675,,,20040330,1,1,3.51,0
Record2,2,1,56,20,-2.29,560400-13,,,,,,,,
Record2,2,1,56,40,3.21,460400-13,,,,,,,,
Record2,2,1,56,60,0.27,211001,,,,,,,,
Record2,2,1,56,80,0.03,211002,,,,,,,,
Record2,2,1,56,100,2.29,140005,,,,,,,,
Record3,3,1,56,1,3.51,20040429,,,,,,,,
Record1,1,1,57,CC056,INV-0401398,1,1080770675,,,20040330,1,1,39.38,0
Record2,2,1,57,20,-18.75,581300-14,,,,,,,,
Record2,2,1,57,40,37.5,430200-14
Record2,2,1,57,60,18.75,140008
Record2,2,1,57,80,1.88,430300-14
Record3,3,1,57,1,39.38,20040429
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question