Creating Import Report - Array?

  • Thread starter Thread starter Steven Britton via AccessMonster.com
  • Start date Start date
S

Steven Britton via AccessMonster.com

I need some help, mostly direction on how to create code so that I can
import a report. The report is a Fixed Length Text file that comes out of
another system; I have no control over the other system and can't change
the format of the report.

It has headers and blank lines between each of the records. The problem I
am having is that the first line of the record will contain the Part # and
other items, and then the very next line or lines could contain a Purchase
Order. Not ever part has a PO after it and some part have more than one PO
after it. Therefore I don't know how to identify the specific PO to there
corresponding parts.

Anyone with sample code or helpful logic out there?

Thanks again -

Steve
 
Steve,

If you don't know how to identify which PO goes with which parts there's
no way you or anyone can write code to do it. So the first thing is to
understand the data (a) in terms of the real-world entities that the
report refers to; (b) in terms of how it's laid out in the report; and
(c) how these relate to the tables in your database (or are you
importing the report to a new table or tables?)

When you're at that stage, you'll know that (for instance) the first
line of each multi-line record must correspond to a record in one table,
and subsequent lines to records in another table.

Then it's just a matter of writing code to read the file a line at a
time, parsing each line and deciding what to do with it. that's not a
trivial task - but there's no point starting it until you know at a
higher level what you need to achieve.

Without knowing more about the structure of the text file and the
structure of your database it's not possible to be much more specific.
 
John,

Thanks for your reply, I guess I need to clarify further. I do know how to
identify the PO that goes to each part. Part is record one and the PO
would be the next line or next several lines. If a part doesn't have any
open PO there is still the next line, but without the PO information.

Below is how I ended up parsing out the info I needed, but I just wasn't
sure if this was the fasest or best design. To me the report is fairly
large, 100,000+ records to look through. So I was looking for a quick
simple design. Thanks again for your input, I'll try to be more specific
next time.

-Steve

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String, strQtyOrder As String
Dim strQtyRcvd As String, strBalanceDue As String
Dim strData As String, strPart As String
Dim strDesc As String, strPO As String, varData As String
Dim strInvQty As Double, lngPart As Double
Dim varPOInfo As Variant

Sub InventoryRpt()

Set db = CurrentDb()

db.Execute "DELETE FROM Report"
db.Execute "DELETE FROM tblReport"

'Opens Window to allow user to select their Inventory Status Report MM510R
Call ImportSpecs

DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qryAppendImport"
DoCmd.SetWarnings (True)

'Select the records and sorts them by Record ID
strSQL = "SELECT * FROM tblReport ORDER BY tblReport.ID;"
Set rs = db.OpenRecordset(strSQL)

With rs
.MoveFirst

Do While Not .EOF
Do While IsNull(!Field1)
.MoveNext
Loop

'Assign import to variable
strData = !Field1
'Extract Part Number
strPart = Left(Trim(Mid(strData, 3, 9)), 9)
'Extract Description
strDesc = Trim(Mid(strData, 55, 45))
'Checks for Inv. Qty - if available assigns it
If IsNumeric(Mid(strData, 140, 15)) = True Then
strInvQty = CDbl(Mid(strData, 140, 15))
Else: strInvQty = 0
End If
'Go to next record, reassing variable
.MoveNext
strData = !Field1
'Locates if record contains a PO
varData = Mid(strData, 124, 1)

Select Case varData

'With Out PO
Case ""
.Edit
!PartNumber = strPart
!Part_Desc = strDesc
!Inv_Qty = strInvQty
.Update
.MoveNext

'With PO
Case "P", "T", "R"
Do While varData = "P" Or varData = "T" Or varData = "R"
strPO = Mid(strData, 129, 7)
'Makes Array of PO Line, removes excess spaces between fields.
varPOInfo = SplitWords(strData, " ")
.Edit
!PartNumber = strPart
!Part_Desc = strDesc
!Inv_Qty = strInvQty
!PONumber = strPO

If InStr(varPOInfo(UBound(varPOInfo)), ".") = 1 Then
!QtyOrder = varPOInfo(UBound(varPOInfo) - 2)
!QtyRcvd = varPOInfo(UBound(varPOInfo) - 1)
Else
!QtyOrder = varPOInfo(UBound(varPOInfo) - 3)
!QtyRcvd = varPOInfo(UBound(varPOInfo) - 2)
!DueDate = varPOInfo(UBound(varPOInfo))
End If

.Update
.MoveNext
If IsNull(!Field1) Then
Exit Do
End If
strData = !Field1
varData = Mid(strData, 124, 1)
Loop

End Select

Loop
End With

Set rs = Nothing
Set db = Nothing

MsgBox "DONE!"

End Sub
 
Back
Top