Solved

Importing a text file(s)

Posted on 2002-03-14
4
174 Views
Last Modified: 2010-05-02
One of our systems produces a text file that list rejected records.  These records can reject for any reasons.  The structure is the same always of the file – with different data of course.  The structure is as follows:

Row #     Field
1     Date
2     MQManager Error #:
3     Desc:
4     System Error #:
5     Desc:
6     MQ-Label:
7     Severity:
8     Table:
9     Action:
10     MQ-Body:
11     48 Dashes


Every record has this format.  The length of MQ-Body can be up to maybe more then 1500 Characters.  The longest I have found so far is 1497.

I do feel I can parse the fields without much of a problem, but my concern is how do I start looping through the text file and then write out the records to a table of this format:

tblRejections

Field             Data Type     Matching Row
MQ_Error     Varchar(20)     Row  2
SystemError     Long             Row  4
UpdateTable     VarChar(30)     Row  5
Server             VarChar(20)     Row  6
FormatTable     VarChar(30)     Row  8
Values             VarChar(100)     Row 11

I want to assign to 6 variables the values need to be written to the table.  I would have to start the loop on the date, then scroll through the text file and gather the values in variables.  Once I reach the 48 dashes, I would then write the record to the table.  If not at EOF and see another date the process needs to start over.

Any ideas on how to do this?  It is the looping through the records in the last part is what I am looking for help in.  

Thanks.
0
Comment
Question by:JTD3
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 6865696
If I'm understanding correctly, each of the 11 Rows listed above are actual new lines in the text file?  That 1 "record" consists of all 11 lines, and then starts a new "record"

Will all records each have all 11 lines, or is it that some might have 8 lines, some have 10 lines?

Do the rows of text in the text file actually contain the row#?  Or just the data for that row?

My approach would be to have a loop that starts as:

While Not EOF(fhandle)
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
      Line Input#.....
Wend


Then, do a Line Input # for each of the 11 rows, assuming that all records always have all 11 rows.

If the row contains the row# you have to parse this out of the string and assign the significant text to a variable.  Once you've gone through all 11 lines (or EOF) then take the variables you need and update the DB.  Then, loop back to the start of the While statement.

You can get a little fancier if you want to create a user defined data type, or a class that holds the fields.  So, instead of doing:

Dim MQ_Error as string
dim SystemError as long
Dim UpdateTable as string
Dim Server as string
Dim FormatTable as string
Dim Values as string

You'd do this:

Type Reject
   MQ_Error as string
   SystemError as long
   UpdateTable as string
   Server as string
   FormatTable as string
   Values as string
End Type

Dim oReject as Reject
Dim sDummy
While Not EOF(fhandle)
      Line Input# fhandle, sDummy ' 1
      Line Input# fhandle, sDummy ' 2
      oReject.MQ_Error = sDummy
      Line Input# fhandle, sDummy ' 3
      Line Input# fhandle, sDummy ' 4
      oReject.SystemError = CLng(sDummy)

... etc
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
      Line Input# fhandle, sDummy
Wend
0
 

Author Comment

by:JTD3
ID: 6878394
mdougan ,

1) all 'records' contain the same number of rows
2) the row numbers are not there - I placed them hoping to provide some more clarity.
3) there is acutally 2 more ros for ever record a space before the date and space after dashes - so 13 rows.

If i understand correctly this routine then will pull 13 records from the text file to proccess on each loop?

I am going to try this out in the tomorrow ( I hope), then I will let you know how it works.

Thank you for your input.

John
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6881569
Yes, if you know that you will have a standard number of rows, then you can just issue that number of Line Input# statements and you will know that you have read all the rows for each record... just to be sure, you can check the contents of certain rows, like you might want to check that row 12 has ---- in it or whatever.

Then, figure out which rows are important to you, and take the value read in from the Line Input# statement and put it either into a local variable, or into a structure like I mentioned.  Structures are nice, because after you collect the data into a structure, you could call another procedure to actually insert the data into the database, and just pass the variable defined as the user defined data type as a parameter....
0
 

Author Comment

by:JTD3
ID: 6882326
I started working on it late yesterday...so I am hoping to get back to you on the results today.  But I think I might have this one now :=).

Never knew about using multiple input lines...That's what I love about all this - continue to learn something new.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question