Import Header-Detail-Footer records from text file to Access

Posted on 2009-04-28
Last Modified: 2013-11-27
I need to import in Access an Account Statement report produced by our DOS accounting system. The report is in a text file format with fixed spacing. This file is used to print the statements on a pre-printed form. With Access, this would allow us to print Account Statements in a Windows environment, where we could easily change the report format, print to PDF to send by email, etc. all features which we can not do with the DOS based system.

So I'm looking for a rough overview of the structure of the code I would need to write in VBA in order to insert in Access the header information (client code, name, address, etc), the detail records (invoice date, inv.number, amount, etc, and the footer data into the header record. I should be ok for the details about parsing each line to find the appropriate fields based on their column location. I'm mainly concerned about the loops for going through the 3 possible sections of the report (header, detail, footer) and the creation/insertion of the records in the Access tables.

The format of the Statement text file is similar to the following:
First 10 lines of the page: Client header (date, client code, name, address)
Next 20 lines of the page: Client statement detail: Invoice date, Invoice number, Amount, Taxes, Total
Last line of the page: Total, Current, 30days, 60days, 90days, 120days+
This repeats for each client statement in the file.

So I would like the header data to go into a Client table, each statement detail to go into a Statement Detail table (linked to the client by ClientCode), and the footer data to go in fields in the Client header record. I am providing a sample of a page in the Code window.

Thanks for your help.

2 pages statement report sample:






      CLIENT A NAME                                                                 

      ADDRESS CLIENTA                                                     


      CITYB                ON  CA                                                             

      A1A 1A1




04/03/2009      997821                  19.36             0.00                19.36           

04/03/2009      997822                 127.55             0.00               146.91           

06/03/2009      997823                 822.75             0.00               969.66           

13/03/2009      997852                 114.45             0.00              1084.11           

17/03/2009      997853                 356.69             0.00              1440.80           

17/03/2009      997854                  67.72             0.00              1508.52           

24/03/2009      997873                 114.45             0.00              1622.97           

26/03/2009      997885                1782.30             0.00              3405.27           

26/03/2009      997886                 349.91             0.00              3755.18           

06/04/2009      997923                 403.74             0.00              4158.92           

14/04/2009      997964                  73.10             0.00              4232.02           

16/04/2009      997983                 177.67             0.00              4409.69           

















  2901.17      1508.52        0.00         0.00         0.00               4409.69            















      CLIENTB NAME.                                                                       

      ADDRESS CLIENTB                                                                   


      CITYB                ON  CA                                                             

      A1A 1A1




20/02/2009      997785               16777.74             0.00             16777.74           

24/02/2009      997793                  45.15             0.00             16822.89           

09/03/2009      997836                 220.11             0.00             17043.00           

15/04/2009      997965                 381.29             0.00             17424.29           

























   381.29       265.26    16777.74         0.00         0.00              17424.29

Open in new window

Question by:ndidomenico
    LVL 10

    Expert Comment

    Can you post an example of the header as it is in the real text file? This will make the logic easier to build!

    Author Comment

    The sample provided in the Code window is exactly how the header is in the real text file. I only replaced the real client names and addresses with dummy text for privacy reasons.


    Author Comment

    Addition to my previous post:
    The "CLIENTA" text between the invoice date line and the client name line represents the Client Code field. Same thing for CLIENTB on the 2nd page.
    LVL 10

    Accepted Solution

    Ok, thanks for clarifying - I'd put together a solution that works, but you may need to modify for your own database... See main bulk of code for the import routine below, but you'll want to check out the attached database for the finer detail... Hope this gives you enough to get it working!!
    Option Compare Database
    Option Explicit
    'Code by therealmongoose - experts exchange question id 24362247
    Sub ImportData()
        Dim strFile As String
        Dim strread As String
        Dim lngFile As Long
        Dim dbs As Database
        Dim rstData As Recordset
        Dim lngClientID As Long
        Dim strHeaderData(9) As String
        Dim x As Integer
        Set dbs = CurrentDb
        Set rstData = dbs.OpenRecordset("tblData")
        strFile = fncGetFile("c:\", CONST_TXT, "Select data file to import")
        lngFile = FreeFile
        Open strFile For Input As #lngFile
        Do While Not EOF(lngFile)
            Line Input #lngFile, strread
            If Not (Trim(strread) = "") Then 'ignore blank line
                'test for first line of client data header
                If IsDate(Mid(strread, 1, 10)) Then
                    'this is a data line - write to database
                    rstData!clientID = lngClientID
                    rstData!Date = DateValue(Mid(strread, 1, 10))
                    rstData!Data1 = CLng(Trim(Mid(strread, 13, 12)))
                    rstData!Data2 = CDbl(Trim(Mid(strread, 23, 23)))
                    rstData!Data3 = CDbl(Trim(Mid(strread, 54, 10)))
                    rstData!Data4 = CDbl(Trim(Mid(strread, 70, 18)))
                    If IsDate(Trim(strread)) Then
                    ' Assumes line with just a date on is the start of the header
                        For x = 0 To 9
                            strHeaderData(x) = Trim(strread)
                            Line Input #lngFile, strread
                        Next x
                        lngClientID = fncGetClient(strHeaderData)
                    'this is the total line - ignore data
                   End If
                End If
            End If
        Close #lngFile
    End Sub
    Function fncGetClient(varHeaderData As Variant) As Long
    On Error GoTo fncGetClient_error
    'Function checks if client exists - if yes returns Primary Key ID for client
    'If not creates new record in client table for client and returns new ClientID
        Dim dbs As Database
        Dim rst As Recordset
        Dim strsql As String
        Dim booFound As Boolean
        Dim lngClientID As Long
        'Assumes client name is unique - you may need to amend the sql where clause to
        'look at address as well if not...
        strsql = "select tblClient.* from tblClient where tblClient.ClientName = '" & varHeaderData(4) & "';"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strsql)
        booFound = True
        rst.MoveLast 'throws error code 3021 if no records present - i.e. client does not exist
        'error routine sets booFound to false if no record found....
        If booFound = True Then
            'client exists - return client id Primary Key
            'MsgBox "Client exists"
            lngClientID = rst!clientID
            'client does not exist - add record to client table and return new id
            'MsgBox "Creating new client"
            Set rst = dbs.OpenRecordset("tblClient")
            rst!ClientName = varHeaderData(4)
            rst!clientAddress = varHeaderData(5)
            'add more of the client details to the client table here.....
            lngClientID = rst!clientID
        End If
        Set rst = Nothing
        Set dbs = Nothing
        fncGetClient = lngClientID
        Exit Function
        If Err.Number = 3021 Then 'client does not exist
            booFound = False
            Resume Next
            MsgBox "oops something went wrong... " & Err.Number & " - " & Err.Description
            Resume fncGetClient_exit
        End If
    End Function

    Open in new window


    Author Comment

    WOW !!! Thanks a million ! This is really amazing work in such short time. Exactly what I needed, and much more. This will be really helpfull !

    One last question: When the import code gets to the total line (last section of each page), I need to store the amounts listed there in the client header record. How would you add this to the existing code ?

    LVL 10

    Expert Comment

    You probably don't want to put it in the client table as this will change each time you run an import for that client - best bet would be to capture the totals to a new table when you get to the totals line...


    'Add to start of procedure..
    	dim rstTotals as recordset
    	set rstTotals = dbs.openrecordset("tblTotals")
    	'then further down....>
                    	'this is the total line - ignore data
    			'new code to capture totals to new table
    			'This can be linked by client id and date to match main data...
    			rstTotals!ClientId = lngClientID
    			rstTotals!Date = strHeader(0)
    			rstTotals!Data1 = cDbl(mid(strRead,1,12)
    			rstTotals!Data2 = cDbl(mid(strRead,13,12)
    		End If

    Open in new window


    Author Comment

    I would prefer if the totals were in the Client table, because we don't need to keep or update the existing client records in the Access client database. In fact, before each import (once a month), we would erase all client and detail records in this database, and start fresh from a new import text file, which contains the up to date data comming from the accounting system for each client.
    LVL 10

    Expert Comment

    You need to modify your Client table structure to accept the totals figures and create an update query where the routine finds the totals line...
    I've amended the example database accordingly...

     Dim strSQL As String
                        'this is the total line - ignore data
                        'amended to capture totals to client table..
                        dblTotals(0) = CDbl(Trim(Mid(strread, 1, 12)))
                        dblTotals(1) = CDbl(Trim(Mid(strread, 13, 12)))
                        dblTotals(2) = CDbl(Trim(Mid(strread, 26, 12)))
                        dblTotals(3) = CDbl(Trim(Mid(strread, 38, 12)))
                        dblTotals(4) = CDbl(Trim(Mid(strread, 50, 12)))
                        dblTotals(5) = CDbl(Trim(Mid(strread, 62, 25)))
    		    strSQL = "UPDATE tblClient SET tblClient.Total1 = " & dblTotals(0) & ", tblClient.Total2 = " & dblTotals(1) & ", tblClient.Total3 = " & dblTotals(2) & ", tblClient.Total4 = " & dblTotals(3) & ", tblClient.Total5 = " & dblTotals(4) & ", tblClient.Total6 = " & dblTotals(5) & " WHERE (((tblClient.clientID)=" & lngClientID & "));"
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings True
                   End If

    Open in new window


    Author Closing Comment

    Thank you very much 'therealmongoose'. I am often amazed at the effort and time that experts like you in this forum will take to help others and provide a solution. Specially in the programming sections of EE, where an answer will require quit a bit of work, typing and effort.

    I can now finalize this project and adapt your code to this specific task (not much to change in fact !!!).

    Thank you
    LVL 10

    Expert Comment

    You're most welcome, and thanks for taking the time to write a nice thank you! Hope the project goes well!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 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

    7 Experts available now in Live!

    Get 1:1 Help Now