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

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

Who is Participating?
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

Can you post an example of the header as it is in the real text file? This will make the logic easier to build!
ndidomenicoAuthor Commented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ndidomenicoAuthor Commented:
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.
ndidomenicoAuthor Commented:
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 ?

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

ndidomenicoAuthor Commented:
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.
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

ndidomenicoAuthor Commented:
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
You're most welcome, and thanks for taking the time to write a nice thank you! Hope the project goes well!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.