[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

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:
 
 
       22/04/2009                                                                           
 
             CLIENTA
 
      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            
 
 
 
 
 
 
 
 
 
 
         22/04/2009                                                                           
 
             CLIENTB
 
      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

0
ndidomenico
Asked:
ndidomenico
  • 5
  • 5
1 Solution
 
therealmongooseCommented:
Can you post an example of the header as it is in the real text file? This will make the logic easier to build!
0
 
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.

 
0
 
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
therealmongooseCommented:
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.AddNew
                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)))
                rstData.Update
            Else
                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)
               Else
                'this is the total line - ignore data
               End If
            End If
        End If
    Loop
    
    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
    Else
        'client does not exist - add record to client table and return new id
        'MsgBox "Creating new client"
        rst.Close
        Set rst = dbs.OpenRecordset("tblClient")
        rst.AddNew
        rst!ClientName = varHeaderData(4)
        rst!clientAddress = varHeaderData(5)
        'add more of the client details to the client table here.....
        lngClientID = rst!clientID
        rst.Update
    End If
    
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    
fncGetClient_exit:
 
    fncGetClient = lngClientID
    Exit Function
    
fncGetClient_error:
 
    If Err.Number = 3021 Then 'client does not exist
        booFound = False
        Resume Next
    Else
        MsgBox "oops something went wrong... " & Err.Number & " - " & Err.Description
        Resume fncGetClient_exit
    End If
 
End Function

Open in new window

importtext.mdb
ee-importfile.txt
0
 
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 ?

Thanks
0
 
therealmongooseCommented:
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....>
 
		Else
                	'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.addnew
			rstTotals!ClientId = lngClientID
			rstTotals!Date = strHeader(0)
			rstTotals!Data1 = cDbl(mid(strRead,1,12)
			rstTotals!Data2 = cDbl(mid(strRead,13,12)
			'etc
			rstTotals.Update
 
 
		End If

Open in new window

0
 
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.
0
 
therealmongooseCommented:
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
 
 
'..............
 
 
  		Else
                    '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

importtext.mdb
0
 
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
0
 
therealmongooseCommented:
You're most welcome, and thanks for taking the time to write a nice thank you! Hope the project goes well!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now