473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

access97: parsing data question

hello

i'm trying to parse out some data from the below format. first of all,
i import the text file into access97 table and the try to parse out.i
have a code written but when i run it, it does not go to the next "FM
TX#" string and there could be a maximum of 1000+. The code only
parses out the first "FM TX#" string set and then exits.what am i
doing wrong with the code and why does it not move to the next
instr(rst![field3].value, ""FM TX#"") ?? am i missing some objects?
any suggestions would be greatly appreciated :)
thanks in advance - jung

here are the variables that should be extracted:
FM TX #
Oper #
Name/add
Int#
Changed
From
To
Entered by

+++text file to parse out data+++++
FM TX # 11/22/03-001 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ADDRESSLINE3
From 8821 COLUMBIA ROAD
To 7642 PRODUCTION DRIVE
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-002 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_CITY
From MAINEVILLE
To CINCINNATI
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-003 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ZIP
From 45039
To 45237
Entered by PYMT at 07:41 AM
+++++++++++++++++++++++++++++++++++

Here is the code:

' Return reference to current database.
Set db = CurrentDb
' Open recordset on Orders table.
Set rst = db.OpenRecordset("DENVER", dbOpenDynaset)
Set rstclean = db.OpenRecordset("CleanDataDenver", dbOpenDynaset)

' Do until end of file.
rst.MoveFirst
'Do Until rst.EOF
'Open Current Record for editing
x = InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS ")
' Do While InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS
") <> 0

rst.MoveNext
rst.MoveNext
Do While InStr(rst![field3].Value, "FM TX #") <> 0
y = InStr(rst![field3].Value, "FM TX #")
If InStr(rst![field3].Value, "FM TX #") <> 0 Then
'Do Until IsNumeric(x) = False
rstclean.AddNew
rst.Edit

fmtxnum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "FM TX # ") + 8, 13))
'opernum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "Oper #") + 6, Len(rst![field3].Value) - 1)
- InStr(rst![field3].Value, "Oper #") + 6)
rst.MoveNext
nameaddress = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "Name/add ") + 9, 15))
'internalnum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, " Name/add ") + 9, 15))

rst.MoveNext
changedinfo = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "Changed ") + 9, 30))

rst.MoveNext
fromwhat = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "From ") + 5, 30))

rst.MoveNext
'If InStr(rst![Field].Value, " To ") <> 0 Then
' towhat = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "To ") + 2, 30))
'End If

rst.MoveNext
enterbywhom = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "Entered by ") + 11, 30))

rstclean![FMTX#].Value = fmtxnum
'rstclean![Oper#].Value = opernum
rstclean![Name/add].Value = nameaddress
'rstclean![Int#].Value = internalnum
rstclean![Changed].Value = changedinfo
rstclean![From].Value = fromwhat
'rstclean![To].Value = towhat
rstclean![Entered by].Value = enterbywhom

rstclean.Update
End If
rst.MoveNext

'x = Len(rst![field3].Value)
Loop
rst.MoveNext
Nov 12 '05 #1
7 2783
JMCN wrote:
hello

i'm trying to parse out some data from the below format. first of all,
i import the text file into access97 table and the try to parse out.i
have a code written but when i run it, it does not go to the next "FM
TX#" string and there could be a maximum of 1000+. The code only
parses out the first "FM TX#" string set and then exits.what am i
doing wrong with the code and why does it not move to the next
instr(rst![field3].value, ""FM TX#"") ?? am i missing some objects?
any suggestions would be greatly appreciated :)
thanks in advance - jung

here are the variables that should be extracted:
FM TX #
Oper #
Name/add
Int#
Changed
From
To
Entered by

+++text file to parse out data+++++
FM TX # 11/22/03-001 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ADDRESSLINE3
From 8821 COLUMBIA ROAD
To 7642 PRODUCTION DRIVE
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-002 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_CITY
From MAINEVILLE
To CINCINNATI
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-003 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ZIP
From 45039
To 45237
Entered by PYMT at 07:41 AM
+++++++++++++++++++++++++++++++++++

Here is the code:

' Return reference to current database.
Set db = CurrentDb
' Open recordset on Orders table.
Set rst = db.OpenRecordset("DENVER", dbOpenDynaset)
Set rstclean = db.OpenRecordset("CleanDataDenver", dbOpenDynaset)

' Do until end of file.
rst.MoveFirst
'Do Until rst.EOF
'Open Current Record for editing
x = InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS ")
' Do While InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS
") <> 0

rst.MoveNext
rst.MoveNext
Do While InStr(rst![field3].Value, "FM TX #") <> 0
y = InStr(rst![field3].Value, "FM TX #")
If InStr(rst![field3].Value, "FM TX #") <> 0 Then
'Do Until IsNumeric(x) = False
rstclean.AddNew
rst.Edit

fmtxnum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "FM TX # ") + 8, 13))


You use this expression often, although you have set a variable to it
above. Bettter use the variable.

But, after import, there is no telling what the order of the records is
in table DENVER.

I feel it is better to use code for the *import*, writing it into
correct records straight away. Do you want to figure that out all by
yourself? then see help on Open, FreeFile, Line Input # and EOF()

If you want me to have a first shot, feel free to mail me. I don't have
time now to write a complete procedure as my kids want my attention, but
later tonight I can work things out if you like.
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #2
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<c1**********@news2.solcon.nl>...
JMCN wrote:
hello

i'm trying to parse out some data from the below format. first of all,
i import the text file into access97 table and the try to parse out.i
have a code written but when i run it, it does not go to the next "FM
TX#" string and there could be a maximum of 1000+. The code only
parses out the first "FM TX#" string set and then exits.what am i
doing wrong with the code and why does it not move to the next
instr(rst![field3].value, ""FM TX#"") ?? am i missing some objects?
any suggestions would be greatly appreciated :)
thanks in advance - jung

here are the variables that should be extracted:
FM TX #
Oper #
Name/add
Int#
Changed
From
To
Entered by

+++text file to parse out data+++++
FM TX # 11/22/03-001 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ADDRESSLINE3
From 8821 COLUMBIA ROAD
To 7642 PRODUCTION DRIVE
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-002 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_CITY
From MAINEVILLE
To CINCINNATI
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-003 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ZIP
From 45039
To 45237
Entered by PYMT at 07:41 AM
+++++++++++++++++++++++++++++++++++

Here is the code:

' Return reference to current database.
Set db = CurrentDb
' Open recordset on Orders table.
Set rst = db.OpenRecordset("DENVER", dbOpenDynaset)
Set rstclean = db.OpenRecordset("CleanDataDenver", dbOpenDynaset)

' Do until end of file.
rst.MoveFirst
'Do Until rst.EOF
'Open Current Record for editing
x = InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS ")
' Do While InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS
") <> 0

rst.MoveNext
rst.MoveNext
Do While InStr(rst![field3].Value, "FM TX #") <> 0
y = InStr(rst![field3].Value, "FM TX #")
If InStr(rst![field3].Value, "FM TX #") <> 0 Then
'Do Until IsNumeric(x) = False
rstclean.AddNew
rst.Edit

fmtxnum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "FM TX # ") + 8, 13))


You use this expression often, although you have set a variable to it
above. Bettter use the variable.

But, after import, there is no telling what the order of the records is
in table DENVER.

I feel it is better to use code for the *import*, writing it into
correct records straight away. Do you want to figure that out all by
yourself? then see help on Open, FreeFile, Line Input # and EOF()

If you want me to have a first shot, feel free to mail me. I don't have
time now to write a complete procedure as my kids want my attention, but
later tonight I can work things out if you like.


yes that would be great to see what you come up with since i feel that
i am in a dead end with my code :)
thanks - jamie
Nov 12 '05 #3
pi******@yahoo.fr (JMCN) wrote in message news:<27**************************@posting.google. com>...
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<c1**********@news2.solcon.nl>...
JMCN wrote:
hello

i'm trying to parse out some data from the below format. first of all,
i import the text file into access97 table and the try to parse out.i
have a code written but when i run it, it does not go to the next "FM
TX#" string and there could be a maximum of 1000+. The code only
parses out the first "FM TX#" string set and then exits.what am i
doing wrong with the code and why does it not move to the next
instr(rst![field3].value, ""FM TX#"") ?? am i missing some objects?
any suggestions would be greatly appreciated :)
thanks in advance - jung

here are the variables that should be extracted:
FM TX #
Oper #
Name/add
Int#
Changed
From
To
Entered by

+++text file to parse out data+++++
FM TX # 11/22/03-001 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ADDRESSLINE3
From 8821 COLUMBIA ROAD
To 7642 PRODUCTION DRIVE
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-002 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_CITY
From MAINEVILLE
To CINCINNATI
Entered by PYMT at 07:41 AM

FM TX # 11/22/03-003 Oper # 54
Name/add RUTH ELDRIDGE / Int# 69888
Changed NMAD_ZIP
From 45039
To 45237
Entered by PYMT at 07:41 AM
+++++++++++++++++++++++++++++++++++

Here is the code:

' Return reference to current database.
Set db = CurrentDb
' Open recordset on Orders table.
Set rst = db.OpenRecordset("DENVER", dbOpenDynaset)
Set rstclean = db.OpenRecordset("CleanDataDenver", dbOpenDynaset)

' Do until end of file.
rst.MoveFirst
'Do Until rst.EOF
'Open Current Record for editing
x = InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS ")
' Do While InStr(rst![field3].Value, "MAINTENANCE TRANSACTIONS
") <> 0

rst.MoveNext
rst.MoveNext
Do While InStr(rst![field3].Value, "FM TX #") <> 0
y = InStr(rst![field3].Value, "FM TX #")
If InStr(rst![field3].Value, "FM TX #") <> 0 Then
'Do Until IsNumeric(x) = False
rstclean.AddNew
rst.Edit

fmtxnum = Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "FM TX # ") + 8, 13))


You use this expression often, although you have set a variable to it
above. Bettter use the variable.

But, after import, there is no telling what the order of the records is
in table DENVER.

I feel it is better to use code for the *import*, writing it into
correct records straight away. Do you want to figure that out all by
yourself? then see help on Open, FreeFile, Line Input # and EOF()

If you want me to have a first shot, feel free to mail me. I don't have
time now to write a complete procedure as my kids want my attention, but
later tonight I can work things out if you like.


hi
i think the reason why i use the "fmtxnum =
Trim(Mid(rst![field3].Value,
InStr(rst![field3].Value, "FM TX # ") + 8, 13))" expression is because
i need to parse out the data. when i import it into access i have a
total of three fields and each line is imported as a new line. when i
get to the rstclean![FMTX#].Value = fmtxnum, the value of FMTX# =
11/22/03-001 and same goes for [opernum#], etc..

i hope i'm explained better but if not, please let me know. i'm still
trying to figure out how to efficiently parse out data from importing
ugly looking text files.

thanks again!
jung
Nov 12 '05 #4
hi
i forgot to finish my explanation:

normally i take the route of importing the ugly
text file as "fixed width" and then try to write a routine to parse out
the data. it worked for me last time with a less complex text file.

okay, i'm going to try your suggestion because it does seem more
efficient than my approach. may i email you with my results? also would you
use sql to import and parse out the data???

thanks again - jung
Nov 12 '05 #5
normally i take the route of importing the ugly
text file as "fixed width" and then try to write a routine to parse out
the data. it worked for me last time with a less complex text file.

okay, i'm going to try your suggestion because it does seem more
efficient than my approach. may i email you with my results? also would
you
use sql to import and parse out the data???

thanks again - jung

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #6
JMCN wrote:
hi
i forgot to finish my explanation:

normally i take the route of importing the ugly
text file as "fixed width" and then try to write a routine to parse out
the data. it worked for me last time with a less complex text file.

okay, i'm going to try your suggestion because it does seem more
efficient than my approach. may i email you with my results? also would you
use sql to import and parse out the data???

Yes, mail about the result. My news reader bogs down now and then, I
can't seem to reliably post answers.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #7
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<c1**********@news2.solcon.nl>...
JMCN wrote:
hi
i forgot to finish my explanation:

normally i take the route of importing the ugly
text file as "fixed width" and then try to write a routine to parse out
the data. it worked for me last time with a less complex text file.

okay, i'm going to try your suggestion because it does seem more
efficient than my approach. may i email you with my results? also would you
use sql to import and parse out the data???

Yes, mail about the result. My news reader bogs down now and then, I
can't seem to reliably post answers.


hi - i ended up finding a solution to my original question. thanks
again for the advice!
cheers jung
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Chris Dugan | last post by:
Hi, has anybody come across a solution to running an Access97 database on Windows XP. The situation I have is an Access 97 database that runs perfectly from Win95/98 clients running Access97, the...
0
by: Rebecca | last post by:
I've posted this in the middle of an older thread but it seems to have gone unnoticed. I apologize for repeating myself. We are converting an existing Access97 split database to an Access front...
0
by: Bob Hynes | last post by:
Hi All, In one of my Access97 db applications I created a report which I output to a RichTextFormat file attach it to an Email and send it out, that has worked just fine for a couple of years now....
0
by: Jack Gordon via AccessMonster.com | last post by:
I am having a problem with charts on reports and forms in Access97. When a runtime version is created with Sagekey's MSI Wizard for Access97 and then installed on another computer and real data...
0
by: lesperancer | last post by:
I've got a sql server view that returns data, as a linked table to access97 (qryPlannedPurPT) this link table is used in an access query (qryPlannedPUR) that uses all of the linked table fields...
10
by: lesperancer | last post by:
you start with a small application in access97, then you have more modules and more... and you reach the point where tables like 'item' and 'employee' reach the limit and you know there's more...
1
by: lesperancer | last post by:
currently using access97 to link to sql7 tables sql7 is running on NT4 and terminal server 2000 in the process of upgrading hardware and software to latest stable versions citrix - windows 2003...
13
by: Chris Carlen | last post by:
Hi: Having completed enough serial driver code for a TMS320F2812 microcontroller to talk to a terminal, I am now trying different approaches to command interpretation. I have a very simple...
2
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.