IMPORTING MULTIPLE LINE TEXT

[Raptor136]

Hi All. I have a real brain buster here. It's complicated so I am not looking for a solution, more like advice and iff possible internet resourses for learning.

My database is for equipment serviceing. I receive a multi-line report from the mainframe daily in the form of a .txt file. It looks something like this:

(the top two lines tell how to read the report the next lines are data. As you can see the parts go with a control number but there isn't a number printed. Each record starts with a control number.)

SHOP SECTION 5 SUB SHOP P PAGE 1 (This is the shop assignment)

CNTR# MODEL SER# DATEIN STATUS
RCVD CODE PN NAME STATUS
AB123 FORD 4567UI78 2215 AWTNG PTS
P 10101JKIOU BKORDERED
R 562LOBIDER RECEIVED
C 6548POPEYE CANCELLED
BA321 CHEVY 899999UH 2234 AWTNG PTS
P 8675309DAD SHIPPED
R 90210STCOM RECEIVED

I can run two seperate imports based on columns, but cannot figure out how to associate the parts to the cars. And I can't figure out how to add the shop / subshop to the car record.

This is pretty simplified, actually the report is 133 char wide, averages about 100 pages printed. Advice is appreciated. BTW a solution will earn you my first born or drink of choice. Thanks, -- Rap

 

[VBslammer]

Since we can only guess what the schema of your database is it would be speculative to try and give you a solution, however, when faced with this type of issue I try to get out my diplomacy bag and talk to the main-framers to see if they can provide me with a report that better suits my needs. The only problem with this solution is that you must do your homework first to figure out what the ideal format is before you approach them.

If they won't cooperate or the system can't be modified, you'll probably have to write a VB procedure to read the text file one line at a time and update the tables without using an import - a fairly common practice when dealing with legacy systems.

Can you query the mainframe directly? Most large systems use some kind of DBMS reporting facility like 'Informix' or 'Impromptu' so you can extract the data using a query. We have used this technique to extract data into a spreadsheet format more conducive to database importing.

My last client used Informix to query the mainframe and publish the data file on one of our servers in a binary file with no extension, and I set up the database with a custom import button to pull in the data using VB. To track the imports I made 2 tables, 1 with fields to provide the possible import instructions such as ServerPath, SourceFile, DestTable, BackupExt, etc. and another to post log information such as User, DateImported, TimeImported, Status, RecordCount, etc.

1 of the imports dealt with audit information and sometimes contained over 80,000 records for a single week, but it still processed in less than a minute or so. Once the import concludes successfully I rename the source file as a backup so the import can't be performed twice. I also make a copy of the original table(s) before running the import so I can restore them if something goes wrong.

Again, this is all done through code so novice users can perform the imports, providing me with more time for coffee breaks and surfing.

If you need some code examples let us know,



VBSlammer

 

[Raptor136]

VBSlammer -- Thanks. I don't know what you mean by "schema". If you mean "what does the database do?" then it tracks open work orders by shop/subshop, reconciles which orders still has parts assigned to them that didn't come in, and allows supervisors from the shop floor up to production control prioritize the workflow.

No deal with the mainframers because the whole agency uses the same reports.

I'm not scared of VBA or SQL. So pointers would be appreciated. I can actually write a simple code to mid() the characters, but as to putting the parts in thier own table and adding the control numbers....currently beyond me. And I still don't know how to associate the shop/subshops either. Any help is great help. Thanks for the thoughts. -- Rap

 

[Raptor136]

vbslammer,
Do you have an example of code that does the line-by-line comparison you were hinting at? Once I see it, and play with it, I am pretty sure I can adapt it. thanks -- Rap raptor136@adelphia.net

 

[VBslammer]

The easiest way is to use the newer 'FileSystemObject' instead of the old 'Open' file method. To use it you need to reference it in your project. While in the VB editor, click TOOLS --> REFERENCES and check 'Microsoft Scripting Runtime.'

Here is a sample of how to open the report and read it. You'll have to add recordset code to update the tables. Call the code using:

ReadReport("C:\myMainFrameReport.txt&quot


'*******************************************************************
' PURPOSE : Function to read / parse report files.
' DATE : 9/11/03 (VBSlammer)
' NOTES :
'*******************************************************************
Public Function ReadReport(ByVal strReportName As String) As Boolean
On Error GoTo ErrHandler
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim strBuffer As String
Dim strSection As String
Dim strSubShop As String

Set ts = fspenTextFile(strReportName, ForReading)
Do Until ts.AtEndOfStream
strBuffer = ts.ReadLine

' If line is a header, do header processing.
If GetHeaderInfo(strBuffer, strSection, strSubShop) = True Then
Debug.Print strSection
Debug.Print strSubShop
Else

'Process data lines...

End If

Loop

ReadReport = True

ExitHere:
On Error Resume Next
Set ts = Nothing
Set fso = Nothing
Exit Function
ErrHandler:
MsgBox "Error " & Err & " - " & Err.Description
Resume ExitHere
End Function

'*******************************************************************
' PURPOSE : Function to process header information.
' DATE : 9/11/03 (VBSlammer)
' NOTES :
'*******************************************************************
Private Function GetHeaderInfo(ByVal strLine As String, ByRef strSection As String, _
ByRef strSubShop As String) As Boolean
On Error GoTo ErrHandler

Dim intSection As Integer
Dim intSubShop As Integer

' Check minimum header length.
If Len(strLine) >= 32 Then

' See if this is a header line
intSection = InStr(UCase(strLine), "SUB SHOP&quot
If intSection > 0 Then
' Found section, assign to ByRef argument.
strSection = Trim(Left(strLine, intSection - 1))

' Look for end of SUB SHOP field.
intSubShop = InStr(UCase(strLine), "PAGE&quot
If intSubShop > 0 Then
' Found sub shop, assign to ByRef argument.
strSubShop = Trim(Mid(strLine, intSection, intSubShop - intSection))
' Success, set return value.
GetHeaderInfo = True
End If
End If
End If

ExitHere:
Exit Function
ErrHandler:
MsgBox "Error " & Err & " - " & Err.Description
Resume ExitHere
End Function

Good Luck,
VBSlammer