Hi all,

I'm going to be getting a weekly "dump" from our mainframe at work (in
the form of a pipe delimited file). This dump would include item
specific information about every product that we sell in our stores.

I have an access database that does a bunch of reporting on this data.
The mainframe will dump (for example's sake) approximately 10 fields
about each item (height, width, depth, weight, etc). My access database
houses all of those fields for each item, plus about 10 more for audit
purposes. The primary key in the database is the Item_Number for each
product. The mainframe will provide the associated item number for
every product in the weekly dump.

Is there a way for me to update the 10 fields for each item that the
mainframe will provide, while leaving the data in the other 10 fields
as is? Basically, I want to leave the audit data alone so that we can
continue to report on it, but I would like to update the master data
from the mainframe every week because it is always changing.

Any initial thoughts? Have I been too vague in my inquiry?

TransferText will not do this.  You will have to either import the data into
a temporary table or link to the file as a table.  In either case, an Update
query can be used to copy the data into the production table.  You will
probably also need an Append Query to add those items in the mainframe dump
that are not in the Access table.

Hi,

I give below the complete code which may solve your needs.

You can paste the code in a new standard module.

Please read the comments given in the code and make changes wherever
necessary before testing.

'=====================    START CODE   ======================================
Option Compare Database
Option Explicit
'Declare module level variables
Dim tabNo As Integer
Dim tab1 As Integer
Dim tab2 As Integer
Dim tab3 As Integer
Dim tab4 As Integer
Dim tab5 As Integer
Dim tab6 As Integer
Dim tab7 As Integer
Dim tab8 As Integer
Dim tab9 As Integer

'In order to use the code, the following conditions
'are to be met.
'1.Set a reference to "Microsoft Scripting Runtime" (SCRRUN.DLL) library
'This library provides maximum flexibility in
'handling text files using FileSystemObject(fso)
'and TextStream object.
'Users can have complete control in
'processing a text file.
'The fso simplifies the task of dealing with
'any type of file input and output and allows
'you to interrogate,create,delete, and
'manipulate folders and text files.

'2.Change dirpath, text file name, Access table name,
'field names, field data types and data type conversion functions
'as appropriate in your machine.

'3.Add error handling code

'This procedure reads a text file line by line
'Extract the values separated by delimiter pipe
'Assign to mapped field variables
'Then update or append in the Access table
'For demonstartion purpose, only five values are
'assumed in a line in the text file.

Public Sub ReadText()
   'Declare variables.
   Dim fso As New FileSystemObject
   Dim ts As TextStream
   
   Dim strLine As String
   Dim LineNo As Integer
   
   
   Dim strItemNo As String
   Dim strHeight As String
   Dim strWidth As String
   Dim strDepth As String
   Dim strWeight As String
   
   Dim dirPath As String
   Dim strFilePathAndName As String
   
   Dim itemNoLen As Integer
   Dim HtLen As Integer
   Dim widthLen As Integer
   Dim depthLen As Integer
   Dim weightLen As Integer
   
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   
   Dim cnn As ADODB.Connection
   Set cnn = CurrentProject.Connection
   
   'Replace with actual table name in your code
   rst.Open "tblTextFileDemo", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable
   
   'assign the text file to be read with complete path
   'Replace with appropriate path and file name
   'in your code.
   dirPath = "C:\surendran\Communities\Text Files"
   strFilePathAndName = dirPath & "\TextFile.txt"
   
   'Open file.
   Set ts = fso.OpenTextFile(strFilePathAndName)
   'Loop while not at the end of the file.
   'Read line by line.
   'Get values between delimiters.
   'Assign to appropriate field variables.
   'Convert to proper data type
   'of fields in Access table
   'Find the itemNo in table using rst.find
   'If found update else add the itemNo
   
   'Assuming the data in text file is
   'something like shown below.
   'Actual data may be with or without headers
   'The numbers shown in first line
   'is the delimiter number which is given
   'to follow the code and logic used.
   
   '             1          2         3          4
   'itemNo!Height!Width!Depth!Weight
   '123       !20       !25      !30      !200
   
   LineNo = 0
   
    Do While Not ts.AtEndOfStream
        'assign the line being read.
        strLine = ts.ReadLine
        'Incement LineNo to know the line number being read
        LineNo = LineNo + 1
       
        'A procedure is called to assign
        'the delimiters position to module
        'level variables tab1,tab2,etc which are
        'used to extract the values between
        'delimiters using mid function.
        'The code for the procedure is
        'given below separately.
        Call getDelimiterPosition(strLine)
       
        itemNoLen = tab1 - 1
        HtLen = tab2 - tab1 - 1
        widthLen = tab3 - tab2 - 1
        depthLen = tab4 - tab3 - 1
        'weightLen = tab5 - tab4 - 1
       
        If LineNo = 1 Then
            'If headers are in first line leave it.
            'If there are no headers and want to read from
            'first line, remove if else then structure.
        Else
            strItemNo = Trim(Mid(strLine, 1, itemNoLen))
            strHeight = Trim(Mid(strLine, (tab1 + 1), HtLen))
           
            strWidth = Trim(Mid(strLine, (tab2 + 1), widthLen))
            strDepth = Trim(Mid(strLine, (tab3 + 1), depthLen))
            'The last field value weight is read from
            '(last delimiterposition plus one) to the end of line
            strWeight = Trim(Mid(strLine, (tab4 + 1)))
           
           'Check the table has some records
           If Not rst.BOF Or Not rst.EOF Then
               rst.MoveFirst
               If Len(strItemNo) > 0 And IsNumeric(strItemNo) Then
                   rst.Find "ItemNo = " & CLng(strItemNo)
               
                   If Not rst.EOF Then
                       'The itemNo is found in the table.
                       'Update the field values.
                       'Assuming the fields Height,Width
                       'Depth and Weight is double data type
                       'convert the string values to double
                       'before assigning and update.
                       'Sometimes the values may be empty
                       'or may not be appropriate for conversion.
                       'Now checking for one incidence like
                       'the value may be blank or nil
                       'Add more checking as appropriate
                       'like isNumeric etc.
                       If Len(strHeight) > 0 Then
                           rst!Height = CDbl(strHeight)
                       End If
                       If Len(strWidth) Then
                           rst!Width = CDbl(strWidth)
                       End If
                       If Len(strDepth) Then
                           rst!depth = CDbl(strDepth)
                       End If
                       If Len(strWeight) Then
                           rst!Weight = CDbl(strWeight)
                       End If
                       rst.Update
                   Else
                       'The itemNo is not found.
                       'So add the new itemno
                       'The itemNo field is assumed to be
                       'Auto Number. So not included.
                       rst.AddNew
                       If Len(strHeight) > 0 Then
                           rst!Height = CDbl(strHeight)
                       End If
                       If Len(strWidth) Then
                           rst!Width = CDbl(strWidth)
                       End If
                       If Len(strDepth) Then
                           rst!depth = CDbl(strDepth)
                       End If
                       If Len(strWeight) Then
                           rst!Weight = CDbl(strWeight)
                       End If
                       rst.Update
                   End If
               End If
           End If
        End If
      'Debug.Print ts.ReadLine
    Loop
    'Close the text file.
    ts.Close
   'Close the recordset and release resources.
    rst.Close
    Set rst = Nothing
   

End Sub
'========================================
Public Sub getDelimiterPosition(stLine As String)
   tabNo = 0
   Dim stLen As Integer
   Dim i As Integer
   'On Error Resume Next
   stLen = Len(stLine)
   'The ASCII value for !(pipe) character is 33
   'To know the ASCII values refer to the
   'following resources.
   '1.Access help - search for "character set"
   '2.http://www.lookuptables.com/
   
   'If delimiter is different say comma, tab etc.
   'then replace the value 33
   'with 9 for tab and 44 for comma.
   
   'Get the delimiter index position in the line
   'and assign to module level variables.
   'This code will assign upto 9 delimiters
   'i.e. for upto 10 values in a line
   
   For i = 1 To stLen
       If Asc((Mid(stLine, i, 1))) = 33 Then
           tabNo = tabNo + 1
           'Debug.Print tabNo & vbTab & i & vbTab & stLen
       
           If tabNo = 1 Then
               'Debug.Print tabNo & vbTab & i & vbTab & stLen
               tab1 = i
           ElseIf tabNo = 2 Then
               tab2 = i
           ElseIf tabNo = 3 Then
               tab3 = i
           ElseIf tabNo = 4 Then
               tab4 = i
           ElseIf tabNo = 5 Then
               tab5 = i
           ElseIf tabNo = 6 Then
               tab6 = i
           ElseIf tabNo = 7 Then
               tab7 = i
           ElseIf tabNo = 8 Then
               tab8 = i
           ElseIf tabNo = 9 Then
               tab9 = i
           End If
       End If
   Next i
   
End Sub

'============================== END CODE   ==================================
Good luck,
Surendran

Thanks folks, for your help with this. I was able to construct what I
needed!