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 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
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 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
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
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!
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |