|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
"pwizzle[ at ]gmail.com" wrote:
[Quoted Text] > 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?
>
>
|
|
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
pwizzle[ at ]gmail.com wrote:
[Quoted Text] >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?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200607/1
|
|
Thanks folks, for your help with this. I was able to construct what I
needed!
|
|
|