Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2005
    Location
    Australia
    Posts
    7

    Unanswered: Automatically adding blank lines in text file

    Hi All!

    I'm having trouble importing data from Access 2007 into MYOB due to the format of the .txt file created.

    MYOB recognises a new record by a blank line between records. However, the .txt file I created with Access doesn't have the file created with a blank line between records.

    I tried adding the blank line manually on a few test records and it worked perfectly. However there will be heaps of records, and adding the blank space manually will be too time consuming and thus not a viable option.

    Basically, I export a file based on a query which is sorted by txtInvNo then DockDate. I need to add a blank line after every Invoice Number.

    Currently it is:

    Code:
    txtInvNo,InvDate,DockDate,DocketNo,cboType,currAmt,GST1,GST,exGST,txtAgentName,T
    axCode
    10026,20/02/2011,01/02/2011,01/02/2011 Docket 3,41300,$71.00,6.45,$6.45,$64.55,Pasadena LPO,GST
    10028,20/02/2011,14/02/2011,14/02/2011 Docket 76,41700,$1.20,0.10,$0.11,$1.09,Pasadena LPO,GST
    10029,20/02/2011,05/02/2011,05/02/2011 Docket 15,41700,$50.00,4.54,$4.55,$45.45,Pasadena LPO,GST
    10029,20/02/2011,03/02/2011,03/02/2011 Docket 12,41300,$12.00,1.09,$1.09,$10.91,Pasadena LPO,GST
    10029,20/02/2011,04/02/2011,04/02/2011 Docket 13,41300,$1.00,0.09,$0.09,$0.91,Pasadena LPO,GST
    10029,20/02/2011,08/02/2011,08/02/2011 Docket 67,41300,$3.50,0.31,$0.32,$3.18,Penfold By Office Choice,GST
    10029,20/02/2011,07/02/2011,07/02/2011 Docket 66,41300,$12.90,1.17,$1.17,$11.73,Penfold By Office Choice,GST
    10029,20/02/2011,04/02/2011,04/02/2011 Docket 65,41300,$65.00,5.90,$5.91,$59.09,Penfold By Office Choice,GST
    10030,20/02/2011,01/01/2011,01/01/2011 Docket 1,41300,$1.00,0.09,$0.09,$0.91,Penfold By Office Choice,GST
    10030,20/02/2011,02/01/2011,02/01/2011 Docket 2,41300,$2.00,0.18,$0.18,$1.82,Penfold By Office Choice,GST
    10030,20/02/2011,03/01/2011,03/01/2011 Docket 3,41700,$3.00,0.27,$0.27,$2.73,Penfold By Office Choice,GST
    10031,20/02/2011,06/01/2011,06/01/2011 Docket 12,41700,$1.00,0.09,$0.09,$0.91,Penfold By Office Choice,GST
    10031,20/02/2011,01/01/2011,01/01/2011 Docket 6,41300,$12.00,1.09,$1.09,$10.91,Penfold By Office Choice,GST
    10031,20/02/2011,02/01/2011,02/01/2011 Docket 8,41300,$16.00,1.45,$1.45,$14.55,Penfold By Office Choice,GST
    10032,20/02/2011,02/01/2011,02/01/2011 Docket 3,41300,$45.00,4.09,$4.09,$40.91,Hilton Plaza Post Office & Newsagency,GST
    10032,20/02/2011,03/02/2011,03/02/2011 Docket 5,41300,$5.00,0.45,$0.45,$4.55,Hilton Plaza Post Office & Newsagency,GST
    But it needs to be like this:

    Code:
    txtInvNo,InvDate,DockDate,DocketNo,cboType,currAmt,GST1,GST,exGST,txtAgentName,T
    axCode
    
    10026,20/02/2011,01/02/2011,01/02/2011 Docket 3,41300,$71.00,6.45,$6.45,$64.55,Pasadena LPO,GST
    
    10028,20/02/2011,14/02/2011,14/02/2011 Docket 76,41700,$1.20,0.10,$0.11,$1.09,Pasadena LPO,GST
    
    10029,20/02/2011,05/02/2011,05/02/2011 Docket 15,41700,$50.00,4.54,$4.55,$45.45,Pasadena LPO,GST
    10029,20/02/2011,03/02/2011,03/02/2011 Docket 12,41300,$12.00,1.09,$1.09,$10.91,Pasadena LPO,GST
    10029,20/02/2011,04/02/2011,04/02/2011 Docket 13,41300,$1.00,0.09,$0.09,$0.91,Pasadena LPO,GST
    10029,20/02/2011,08/02/2011,08/02/2011 Docket 67,41300,$3.50,0.31,$0.32,$3.18,Penfold By Office Choice,GST
    10029,20/02/2011,07/02/2011,07/02/2011 Docket 66,41300,$12.90,1.17,$1.17,$11.73,Penfold By Office Choice,GST
    10029,20/02/2011,04/02/2011,04/02/2011 Docket 65,41300,$65.00,5.90,$5.91,$59.09,Penfold By Office Choice,GST
    
    10030,20/02/2011,01/01/2011,01/01/2011 Docket 1,41300,$1.00,0.09,$0.09,$0.91,Penfold By Office Choice,GST
    10030,20/02/2011,02/01/2011,02/01/2011 Docket 2,41300,$2.00,0.18,$0.18,$1.82,Penfold By Office Choice,GST
    10030,20/02/2011,03/01/2011,03/01/2011 Docket 3,41700,$3.00,0.27,$0.27,$2.73,Penfold By Office Choice,GST
    
    10031,20/02/2011,06/01/2011,06/01/2011 Docket 12,41700,$1.00,0.09,$0.09,$0.91,Penfold By Office Choice,GST
    10031,20/02/2011,01/01/2011,01/01/2011 Docket 6,41300,$12.00,1.09,$1.09,$10.91,Penfold By Office Choice,GST
    10031,20/02/2011,02/01/2011,02/01/2011 Docket 8,41300,$16.00,1.45,$1.45,$14.55,Penfold By Office Choice,GST
    
    10032,20/02/2011,02/01/2011,02/01/2011 Docket 3,41300,$45.00,4.09,$4.09,$40.91,Hilton Plaza Post Office & Newsagency,GST
    10032,20/02/2011,03/02/2011,03/02/2011 Docket 5,41300,$5.00,0.45,$0.45,$4.55,Hilton Plaza Post Office & Newsagency,GST
    Can anyone help, pls? I use the DAO reference as I'm not very familiar with the ADO reference.

    Thanks a lot in advance, and I look forward to hearing from someone soon!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Sub ExportQueryToTxt(ByVal QueryName As String, ByVal FileName As String, Optional ByVal ListSeparator As String = ",")
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim intHandle As Integer
        Dim strLine As String
        
        intHandle = FreeFile
        Open FileName For Output As #intHandle   ' Use 'For Append' to add lines to an existing file, if any.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(QueryName, dbOpenSnapshot)
        With rst
            Do Until .EOF
                For Each fld In rst.Fields
                    If Len(strLine) > 0 Then strLine = strLine & ListSeparator
                    strLine = strLine & fld.Value
                Next
                strLine = strLine & vbNewLine
                Print #intHandle, strLine
                strLine = ""
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        Close #intHandle
        
    End Sub
    Usage:
    Code:
    ExportQueryToTxt "Qry_Export", "c:\export.txt" ' ";" (for semicolon as list separator
    Have a nice day!

  3. #3
    Join Date
    Nov 2005
    Location
    Australia
    Posts
    7
    Hi Sinndho,

    Thanks a lot for taking the time to reply! I really appreciate it!

    From my understanding of your code, it adds a blank like after every line. How do I sort it by txtInvNo and enter a blank line only after 1 invoice and before the next?

    Any help would be great!

    Thanks again!
    Last edited by Sherrie; 02-21-11 at 07:28. Reason: Typo

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This should do the trick but I have not tested (in a hurry today):
    Code:
    Sub ExportQueryToTxt(ByVal DataSource As String, _
                         ByVal FileName As String, _
                         Optional DocIDIndex As Long = 0, _
                         Optional ByVal ListSeparator As String = ",")
    '
    ' DataSource:    Name of a table, a SELECT query or a SELECT SQL statement.
    '                In anay case, the rowset must be sorted on the DocIDIndex column.
    
    ' FileName:      Name of the output file that will receive the exported rows.
    
    ' DocIDIndex:    Ordinal position of the column in the rowset
    '                that contains the document ID (default = 0 --> first column).
    
    ' ListSeparator: Character used to separate the different columns of data
    '                in the text output file (default = ",")
    '
    ' Example of call: ExportQueryToTxt "Qry_Export", "c:\export.txt", 1, ";"
    '
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim intHandle As Integer
        Dim strLine As String
        Dim varDocID As Variant
        
        intHandle = FreeFile
        Open FileName For Output As #intHandle   ' Use 'For Append' to add lines to an existing file, if any.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(DataSource, dbOpenSnapshot)
        With rst
            If Not .EOF Then
                varDocID = rst.Fields(DocIDIndex).Value
                Do Until .EOF
                    For Each fld In rst.Fields
                        If Len(strLine) > 0 Then strLine = strLine & ListSeparator
                        strLine = strLine & fld.Value
                    Next
                    If rst.Fields(DocIDIndex).Value <> varDocID Then strLine = vbNewLine & strLine
                    Print #intHandle, strLine
                    strLine = ""
                    varDocID = rst.Fields(DocIDIndex).Value
                    .MoveNext
                Loop
            End If
            .Close
        End With
        Set rst = Nothing
        Close #intHandle
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Nov 2005
    Location
    Australia
    Posts
    7
    Hi Sinndho,

    Thank you very much for that! Especially the explanations. Will try to decipher before I try it and will let you know how it goes

  6. #6
    Join Date
    Nov 2005
    Location
    Australia
    Posts
    7
    Just another quick question, though.

    What is the DocIDIndex column? Is that my invoice number field?

    Thanks again.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    is that my invoice number field?
    In your case it is. More generally it's the column index (starting at zero) for the data column that identifies the rows that are kept together in the output file (= rows without an extra NewLine char. ).

    As the recordset is read sequencially, this is why it must be sorted on that column as any change in its value implies the insertion of the Newline char., so beginning a new group of (related) lines in the text document. This is what is done by the line:
    Code:
    If rst.Fields(DocIDIndex).Value <> varDocID Then strLine = vbNewLine & strLine
    While the value of the same column for the former row is kept in varDocID that is updated by the line:
    Code:
    varDocID = rst.Fields(DocIDIndex).Value
    Have a nice day!

  8. #8
    Join Date
    Nov 2005
    Location
    Australia
    Posts
    7

    velyd basic

    Sinndho,

    Sorry for the late reply. Been sick so haven't been able to program much to test it out.

    Have tried it and it works like a charm!

    Thanks heaps for that - it's greatly appreciated!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Apr 2013
    Posts
    6
    I have the same problem and I am only just starting to learn MS ACCESS so I need a lot of help. I import a csv file from our web shop cart into access, run an append query to add extra field data for MYOB and append this data to ITEMSALES table which I then export in text format so I can import it into MYOB. The ITEMSALES shows each record by line item, so there may be several records belonging to the same invoice number. I need help with code to add the blank line before I export the text file, or when I export the text file. I don't know how to trigger the code. Is there a way to make SINNDO's code work for me. Table to be exported = ITEMSALES Invoice field=Invoice# Please HELP I am desperate.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try the ExportQueryToTxt() procedure? If yes, what went wrong?
    Have a nice day!

  12. #12
    Join Date
    Apr 2013
    Posts
    6
    Hi Sinndho,
    I haven't tried it yet because I am completely dumb. I am not sure where in the code I refer to the my table eg "Itemsales" or my field "invoice#". Also, I am not sure how to trigger the code. At present I run an append query which adds some extra fields for MYOB and it appends these records to the table from which I will export the data. Once that is done, I export to a text file by selecting export txt from the menu. I am not sure how and when to tell access to run the code you provided. You can tell I am a complete novice I bet! Do I setup the code you wrote in a module or a function or am I missing something very simple?
    Last edited by jom1918; 04-29-13 at 05:48.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Create a new Standard Module (i.e. not a module linked to a form) or open an existing one then paste the code I posted into it. Follow the example given in the comments at the top of the procedure to call the procedure ExportQueryToTxt(), replacing the parameters in the example by your own. You cannot use the standard export txt feature of Access menus to call the procedure, at least not easily. Call it from a command button on a form or, for testing purposes, from the immediate window.
    Have a nice day!

  14. #14
    Join Date
    Apr 2013
    Posts
    6
    Thanks Sinndho. I will give it a try and let you know how I go. I really appreciate your help.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •