access97: parsing data question

**Bas Cost Budde** · Nov 12 '05, 08:47 PM

Re: access97: parsing data question

JMCN wrote:
[color=blue]
> 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_ADDRESSLIN E3
> 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.OpenRecordse t("DENVER", dbOpenDynaset)
> Set rstclean = db.OpenRecordse t("CleanDataDen ver", dbOpenDynaset)
>
> ' Do until end of file.
> rst.MoveFirst
> 'Do Until rst.EOF
> 'Open Current Record for editing
> x = InStr(rst![field3].Value, "MAINTENANC E TRANSACTIONS ")
> ' Do While InStr(rst![field3].Value, "MAINTENANC E 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))[/color]

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

**JMCN** · Nov 12 '05, 08:48 PM

Re: access97: parsing data question

Bas Cost Budde <bas@heuveltop. org> wrote in message news:<c186ri$u0 5$7@news2.solco n.nl>...[color=blue]
> JMCN wrote:
>[color=green]
> > 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_ADDRESSLIN E3
> > 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.OpenRecordse t("DENVER", dbOpenDynaset)
> > Set rstclean = db.OpenRecordse t("CleanDataDen ver", dbOpenDynaset)
> >
> > ' Do until end of file.
> > rst.MoveFirst
> > 'Do Until rst.EOF
> > 'Open Current Record for editing
> > x = InStr(rst![field3].Value, "MAINTENANC E TRANSACTIONS ")
> > ' Do While InStr(rst![field3].Value, "MAINTENANC E 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))[/color]
>
> 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.[/color]

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

**JMCN** · Nov 12 '05, 08:49 PM

Re: access97: parsing data question

picarama@yahoo. fr (JMCN) wrote in message news:<2772ee20. 0402211943.2f2f d480@posting.go ogle.com>...[color=blue]
> Bas Cost Budde <bas@heuveltop. org> wrote in message news:<c186ri$u0 5$7@news2.solco n.nl>...[color=green]
> > JMCN wrote:
> >[color=darkred]
> > > 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_ADDRESSLIN E3
> > > 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.OpenRecordse t("DENVER", dbOpenDynaset)
> > > Set rstclean = db.OpenRecordse t("CleanDataDen ver", dbOpenDynaset)
> > >
> > > ' Do until end of file.
> > > rst.MoveFirst
> > > 'Do Until rst.EOF
> > > 'Open Current Record for editing
> > > x = InStr(rst![field3].Value, "MAINTENANC E TRANSACTIONS ")
> > > ' Do While InStr(rst![field3].Value, "MAINTENANC E 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))[/color]
> >
> > 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.[/color]
>[/color]

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

**JMCN** · Nov 12 '05, 08:49 PM

Re: access97: parsing data question

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

**Jamie McCarthy** · Nov 12 '05, 08:49 PM

Re: access97: parsing data question

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!

**Bas Cost Budde** · Nov 12 '05, 08:49 PM

Re: access97: parsing data question

JMCN wrote:[color=blue]
> 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???
>[/color]
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

**JMCN** · Nov 12 '05, 08:53 PM

Re: access97: parsing data question

Bas Cost Budde <bas@heuveltop. org> wrote in message news:<c1b45f$ag 6$1@news2.solco n.nl>...[color=blue]
> JMCN wrote:[color=green]
> > 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???
> >[/color]
> Yes, mail about the result. My news reader bogs down now and then, I
> can't seem to reliably post answers.[/color]

hi - i ended up finding a solution to my original question. thanks
again for the advice!
cheers jung

access97: parsing data question

access97: parsing data question

Comment

Comment

Comment

Comment

Comment

Comment

Comment