Solved

Error Writing CSV File - Trailing Delimiters Dropped from Final Lines

Posted on 2004-04-11
10
210 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now