breaking a text file into individual records 1

[qwzx]

I have a text config file that I have down-loaded from a device. The file lists each port on the device and the port’s configuration parameters. The name and number of parameters varies according to the type of port. I want to come up with a routine to read the text file and assign one record per port. I also want to have the fields on each record match the parameters of the port.
I have done something similar before using “open file for input as” and then using string functions to pick out what information I needed. But I have only done this for fields on the same record.
I don’t know how to break one text file into multiple records. i.e. As the procedure is reading in the text file, when it detects certain characters, it starts a new record.
Thanks in advance for any thoughts.

 

[JeremyNYC]

ECW,

Post a snippet of one of the text files, just a few records.

I would think the way to go about it would be the same, just using instr() a whole lot. Maybe you could post a bit of the code you used for the other stuff, so we can get a sense of where you're at with this stuff, too.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MichaelRed]

use advanced search for "basGrabFile" in these fora. There are multiple threads which include this routine, and at least a few of them also include a "demo" routine which shows how to break up a file in to records and hte records into fields.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[qwzx]

Thank you gentlemen for your responses. I am inferring by your posts that this was the wrong forum to ask this question. I only program for my own use. I am a telcom tech by trade and have not been formally trained. I apologize for taking up the forum's time.
I will look for those threads

 

[JeremyNYC]

ECW,

No I don't think this is the wrong forum at all. There's no need to apologize. Michael's response was just saying that it's been covered, and rather than putting more of his time into it, he was giving you a way to take a look at what others have said about it. I was just asking for more information, so that we could help you better. I would take a look at the stuff that comes up from that search (I didn't read those threads myself, so I don't know what's in them). If you're still having trouble, come back to this thread and post a bit more. You might want to read the thread I link to at the bottom of my posts, so that you get a better idea of the kind of information people generally look for in these threads, when they help folks. But you've done nothing wrong here at all.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[qwzx]

I looked at the threads that resulted from the search for ”basGrabFile“. The info I need may be there but I’m not getting it. I don’t think I explained my problem correctly though. I don’t need to know how to get the file into Access. I have done that. My problem is how to move to the next record.
I will try to explain. When I was in school we used Basica and Quick Basic. They worked with single values if you will. X= this or Y= that. Only one X or one Y. Now I am dealing with X= some value on record #1 then X= some value on record #2. I need the code to move to record #2
i.e.
Input data
Do string functions to fill fields on record #1
String Function returns a particular value
Based on that value open new record “this is the step I need.”
Fill fields on the new record
Continue until EOF

I think of a database as a book full of pages of empty fields. You can only look at one page at a time. I know how to get the data to fill the fields of the page I am looking at.
What I don’t know how to do, is to turn to the next page. I don’t know what that process is called so I don’t know how search for it.
It may be that this is not that simple and that this is why those of us who don’t program, hire people who do.
I realize we are all busy and if this is beyond the scope of simple help, please say so. My job does not depend on solving this. I just think there should a simple method or procedure tell Access to start a new record.

Thanks for letting me ramble. Any solutions, thoughts etc. would be appreciated.

 

[bboffin]

If you have already imported the data into a table you need to open that table as a Recordset. Once you have opened the recordset, some of the commands used to move through the records are MoveFirst, MoveNext and MovePrevious.

Thus a basic loop might look like:

Dim MyRecordset as Recordset
Set MyRecordset = CurrentDb.OpenRecordset("MyTablename")
MyRecordset.MoveFirst
Do While Not MyRecordset.EOF
    ' process each record in turn
    MyRecordset.MoveNext
Loop

Note that this is a very simplified example and, depending on what you are trying to achieve, may not be the best way to process your data.

Hope this helps you to get started anyway.

 

[qwzx]

Thanks for your fast response. I have looked at recordset. To me tho a recordset or a table is a group of records. What I have is one string varible full of data that I want to break into pieces or records.

 

[bboffin]

Could you post an example of the data you are trying to read in and parse? This would help to clarify your problem.

In general you can read in lines of text using the Line Input statement having opened the text file using the Open statement.

See this example from MS Access Visual Basic Help

Dim TextLine
Open "TESTFILE" For Input As #1    ' Open file.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, TextLine    ' Read line into variable.
    Debug.Print TextLine    ' Print to the Immediate window.
Loop
Close #1    ' Close file.

You would then only need to understand how to parse each line which would be done where the Debug.Print statement is.

However if your data conforms to some well known common formatting rules there are better ways to read the data which is why it would be useful to see some sample data.

 

[qwzx]

Finally had a chance to get back to this.
Below is a part of the text file I am trying to deal with. For simplicity, assume each record has only one field. I want to populate that field with all of the text that appears on the lines after the line that starts with the word “DATE”. I am looking for the code snip-it that amounts to
IF STRING =”DATE” THEN OPEN A NEW RECORD. PUT ALL OF THE LINES OF TEXT FOLLOWING THE LINE THAT STARTS WITH "DATE" INTO FIELD#1. WHEN THE NEXT LINE APPEARS THAT STARTS WITH THE STRING “DATE” CLOSE RECORD #1 AND OPEN RECORD #2 AND PROCEED TO DO THE SAME THING AGAIN AND SO ON UNTIL END OF STRING VARIBLE.
What I have done in the past and what people have graciously shown me again was how to get the text file into a one string variable and then sort that string into pieces that I could put in one or more fields on the same record. My questions is how do I put pieces of one variable into multiple records?
Thank you all for your patience in this.


DATE 18 APR 2000

TN 009 17
TYPE TIE
CUST 0
TRK DTI
PDCA 1
PCML MU
NCOS 2
RTMB 10 2
ATDN 8200
TGAR 2
SIGL EM4
STRI/STRO WNK WNK
SUPN YES
AST NO
IAPG 0
CLS CTD DTN CND ECD WTA LPR APN THFD HKD
P10 VNL
TKID
DATE 18 APR 2000

TN 009 18
TYPE TIE
CUST 0
TRK DTI
PDCA 1
PCML MU
NCOS 2
RTMB 10 3
ATDN 8200
TGAR 2
SIGL EM4
STRI/STRO WNK WNK
SUPN YES
AST NO
IAPG 0
CLS CTD DTN CND ECD WTA LPR APN THFD HKD
P10 VNL
TKID
DATE 18 APR 2000

TN 009 19
TYPE TIE
CUST 0
TRK DTI
PDCA 1
PCML MU
NCOS 2
RTMB 10 4
ATDN 8200
TGAR 2
SIGL EM4
STRI/STRO WNK WNK
SUPN YES
AST NO
IAPG 0
CLS CTD DTN CND ECD WTA LPR APN THFD HKD
P10 VNL
TKID
DATE 18 APR 2000

 

[bboffin]

I'm not clear what you mean by '... put pieces of one variable into multiple records'. Could you give an example using your sample data?

Looking at the data each line appears to be a field name and a value separated by on or more spaces. In some cases there are multiple values for a field name. Is this correct?

If so then you should really be splitting up each line as you come to it and assigning the value of the field to a column of the corresponding name in the record.

So I would expect the table to be defined as something like:

Column Name  Type
DATE         Date/Time
TN           text
TYPE         text
CUST         text
.
.
.

You would start by opening the table as a recordset (CurrentDb.OpenRecordset) with the option adOpenDynamic. When you find the first DATE line you would call the AddNew method of the recordset.

Then as each line is decoded you would assign the value to the appropriate field using
MyTable("ColumnName&quot.value = MyValue

When you reach the next DATE column you commit the new record by calling MyTable.Update and then create another new empty record using AddNew.

If the data should be separated into more than one table then you simply use more recordsets and you add the required field values to each one as appropriate remembering to Update each recordset each time another DATE line is found.

Does that help at all?

 

[qwzx]

You are correct that my final goal is have each line as a field value. What I want to do is a combination of your 2 posts. Do I understand you to say that I can open a text file as a recordset? I thought the text had to already be in table form to do that. If I can open a text file as a recordset then I have my question answered. Your second post has the info I need. THANK YOU!!!!!

 

[bboffin]

You can't open a text file in this format as a recordset.

The recordset is the 'output file'.

Whet you need to do is have a loop that reads each line from the text file using Input Line.

You can split the line read in into 'column name' and 'value' using the Split function if you are using Access 2000 or later.

The 'value' part is placed into the appropriate column of the record using the 'column name' to tell you which column it is.

When you reach another DATE line you save the record using the Update method of the recordset and then call its AddNew method to get a new empty record for the next set of data.

If you reach the end of the file call Update to save any current record.

The code might look like:

Dim MyRS as RecordSet
Dim strLine as String
Dim strCol as String
Dim strValue as String
Dim vSplit as Variant
Dim blnRecord as Boolean

blnRecord = false
Open "textfile.txt" For Input As #1
Set MyRS = CurrentDb.OpenRecordset("MyTable", adOpenDynamic)

Do
    Line Input #1, strLine
    vSplit = Split(strLine, " ", 2)
    If UBound(vSplit) = 1 then
        strCol = Trim(vSplit(0))
        strValue = Trim(vSplit(1))
        Select Case UCase(strCol)
        Case "DATE"
            If blnRecord Then
                MyRs.Update
            End If
            MyRs.AddNew
            blnRecord = True
            MyRs.Fields("DATE").Value = strValue
        Case "TN"
            MyRs.Fields("TN").Value = strValue
.   repeat for each different column
.   this code will automatically reject ignore invalid lines
.
.
        End Select
    End If
Loop Until EOF(1)
If blnRecord Then
    MyRS.Update
end if

MyRs.Close
Close 1

 

[qwzx]

That makes alot more sense. I think you have given the info I need. Thanks to every one for their responses. I think this thread needs to end now.
Thanks again.

 

[dweis]

bboffin, thanks so much. I know I didn't start this thread, but I gave you a star anyway. I was looking for a way to chop up an EDI document to get only the info that I need and you were able to give me some inspiration with your code. Here's what I've done so far, still have some field mapping to do but it will work for what I need, so Kudos to you - thanks!

My code snippet:

Dim MyRS As Recordset
Dim strLine As String
Dim strCol As String
Dim strValue As String
Dim vSplit As Variant
Dim blnRecord As Boolean

blnRecord = False
Open "c:\204.txt" For Input As #1

Set MyRS = CurrentDb.OpenRecordset("edi_imp", , adOpenDynamic)

Do
Line Input #1, strLine
vSplit = Split(strLine, "*")
If UBound(vSplit) > 1 Then

strCol = Trim(vSplit(0))

Select Case UCase(strCol)
Case "ISA"

strValue = Trim(vSplit(7))

If blnRecord Then
MyRS.Update
End If
MyRS.AddNew
blnRecord = True
MyRS.Fields("ISA").Value = strValue
Case "N9"
If Trim(vSplit(1)) = "BM" Then

strValue = Trim(vSplit(2))
MyRS.Fields("BM").Value = strValue
End If
If Trim(vSplit(1)) = "V3" Then

strValue = Trim(vSplit(2))
MyRS.Fields("V3").Value = strValue
End If
If Trim(vSplit(1)) = "SI" Then

strValue = Trim(vSplit(2))
MyRS.Fields("SI").Value = strValue
End If
If Trim(vSplit(1)) = "ZZ" Then
If Len(Trim(vSplit(2))) = 2 Then

strValue = Trim(vSplit(2))
MyRS.Fields("N9_code").Value = strValue
Else

strValue = Trim(vSplit(2))
MyRS.Fields("n9_note").Value = strValue
End If
End If

Case "N1"
If Trim(vSplit(1)) = "PU" Then
'strCol = Trim(vSplit(1))
strValue = Trim(vSplit(2))
MyRS.Fields("n1pu").Value = strValue
End If
If Trim(vSplit(1)) = "DZ" Then
'strCol = Trim(vSplit(1))
strValue = Trim(vSplit(2))
MyRS.Fields("n1DZ").Value = strValue
End If


End Select
End If
Loop Until EOF(1)
If blnRecord Then
MyRS.Update
End If

MyRS.Close
Close 1


End Sub

 

[JeremyNYC]

qwzx,

[It's really fine if a thread gets long--people get huffy when it deteriorates into noise, but there's plenty of signal here. I _definitely_ appreciate you being cautious of your actions as you enter into a new community, but what's happening here is exactly what folks in Tek-Tips like: questions and answers, you did some research on your own, came back with more questions, someone else jumped in with more answers...it's all good.]

It looks like you've gotten pretty close to a solution, so what I'm offering may not be needed at all, but, I do want to offer up one very low-tech solution that can work with data sets formatted like the one you presented, _IF_ it's not a HUGE file and _IF_ each record has a "label" for every field--that is, if all records are represented by the same number of lines, with the same labels in the same order. If all of that is true, try using Word to do this! First off, make several backups of your file, because there are sure to be several mis-steps, and you DON"T want to hammer your original data. Next, just do a series of find-and-replaces, replacing the each heading value with a semicolon, leaving the first heading value ("^p^pTN") for last, just replacing that with "^p". If it all works out, you'll end up with a semi-colon delimited text file, which you'll be able to import directly into an Access table.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org