Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
[Reply To] RE: parsing help for a slow learner
Author: Ben Forta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32888#165027
Seamus,
This is a little trickier now. The simple solution would be to treat it as a
list delimited by line breaks, but it is not as you have some fields that
contains breaks in it, and no "safe" end field markers, so this needs to be
a bit manual (as in loop line by line and see if it is a new field or not).
So, you loop through record by record (what you already have), and then line
by line within each record constructing the fields, and then doing something
with them. It may be best to turn the data into a query, so for each record
you add a row, and then for each field you set a cell.
Here is code that shows this:
<!--- Get all records --->
<CFSET records=REFindAll("(BOOS\|).*?(BOOE\|)", myData)>
<!--- Create a query --->
<CFSET
myQuery=QueryNew("BOOS,BOOK,TRAN,ANAM,TNAM,PBLS,SUB1,DESC,PRIC,UPDT,UPTM,BCA
T,ABLE,UBID,STAT,PPRC,PDSC,BOOE")>
<!--- Loop through them --->
<CFLOOP INDEX="i" FROM="1" TO="#ArrayLen(records.pos)#">
<!--- Add a row to the query --->
<CFSET QueryAddRow(myQuery)>
<!--- Loop through records --->
<CFSET record=Mid(myData, records.pos[i], records.len[i])>
<!--- Initialize field --->
<CFSET field="">
<!--- Loop through lines in this record --->
<CFLOOP INDEX="line" LIST="#record#" DELIMITERS="#Chr(10)##Chr(13)#">
<!--- If has a | in it, then treat it as start of new field --->
<CFIF line CONTAINS "|">
<!--- New field, save to "field" --->
<CFSET field=line>
<CFELSE>
<!--- Next line of field, append to "field" --->
<CFSET field=field&line>
</CFIF>
<!--- Now that we have the field, split it, before | is name, after is
value --->
<CFSET field_name=ListFirst(field, "|")>
<CFSET field_value=ListRest(field, "|")>
<!--- And save it to the query --->
<CFSET QuerySetCell(myQuery, field_name, field_value)>
</CFLOOP>
</CFLOOP>
Use this with the function I gave you earlier. You'll then end up with a
query named "myQuery" (change the name to something more descriptive). The
query will have the fields in your record (same names), and can be used like
any other ColdFusion query. To display it you could do this:
<!--- Show query contents --->
<CFDUMP VAR="#myQuery#">
The next step would be to turn this all into a function, pass it a file (or
file contents) and get back a query.
And you should be all set.
--- Ben
PS Thanks for asking, little challenges like this are good practice. :-)
_____
Sent: Sunday, May 30, 2004 6:20 PM
To: CF-Talk
Subject: [Reply To] RE: parsing help for a slow learner
Thanks Ben. That's a wonderful piece of code (most of which I'm
still trying to understand!)
I feel really stupid now but I'm still stuck.
I added your code and can now get the below.
I can't see how to get each field out of each record to input
into the database. I can see each record is a list with delimiters
of BOOK|, TRAN| ANAM| etc but I can't see how to use these
delimiters to get each field (does that make sense?)
Any help or clues would be great.
++++++++++++++++++++++++
BOOS| BOOK|000004 TRAN|D ANAM|ROTH, WALTER E. TNAM|NORTH QUEENSLAND
ETHNOGRAPHY Bulletin No.4 : GAMES, SPORTS AND AMUSEMENTS PBLS|Home
Secretary's Department Brisbane, 1902 SUB1|ABORIGINAL CULTURE,
AUSTRALIA HISTORY, NATIVE MONOGRAPHS, 19TH CENTURY SPORTS
DESC|Good. 1st. 13.25 x 8.25. a Monograph of 39pp, illustrated by
photograph and drawings, the endpapers have browned and the
exceedingly plain covers have surface tarnish but a bright
interior on superior paper. Walter E.Roth was Queensland's
"Northern Protector of Aboriginals" previously he was "Demy of
Magdalen College, Oxford". PRIC|35 UPDT|10/05/2004 UPTM|12:44:38
PM BCAT|Sold ABLE|1 UBID|1 STAT|Sold PPRC|0 PDSC|QLD BOOE|
_____
Author: Seamus Campbell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32888#165025
Hi Aaron and thanks
I tried your method (as I can understand lists better than regex
and I have different numbers of fields for each record) but got
the below error (I can't see why it doesn't like <CFSET ValueList
= "">)
Any clues please
==========================================
Invalid CFML construct found on line 9 at column 26.
ColdFusion was looking at the following text:
=
The CFML compiler was processing:
an expression beginning with "ValueList", on line 9, column
16.This message is usually caused by a problem in the expressions
structure.
a CFSET tag beginning on line 9, column 10.
a CFSET tag beginning on line 9, column 10.
a CFSET tag beginning on line 9, column 10.
The error occurred in
C:\Inetpub\wwwroot\Booksmith\update_records_list.cfm: line 9
7 : <CFIF Meta EQ "BOOS">
8 : <CFSET FieldList = "">
9 : <CFSET ValueList = "">
10 :
11 : <CFELSEIF Meta EQ "BOOE">
==========================================
================ You wrote ================
Hi Seamus
>>
>>You have a few people helping you already, but I thought I would
share how I would do this for a different perspective: basically,
I ask myself "how would I do this manually", then map the process
to code. I guess it helps to be able to think like a computer, but
I digress.
>>
>>An alternative to the dynamic SQL generation is provided using [
] instad of < >.
----- Excess quoted text cut - see Original Post for more -----
on a separate line (Chr(13)&Chr(10)), and that on each line we
have the metadata (BOOS, ANAM, TNAM, etc), separated from the data
with a | character. NB: I treat things (everything!) as a list. I
don't/can't do regex in my head, I do lists.
----- Excess quoted text cut - see Original Post for more -----
fields/data), we will dynamically create the insert SQL statement
as we go. To do this we will need 2 strings - the field list and
the value list. We may as well initialize them at the start of a
record.
>>
>>NB: to keep it simple, we are also going to treat all fields as
string. You can modify this process as you see fit.
----- Excess quoted text cut - see Original Post for more -----
database...
----- Excess quoted text cut - see Original Post for more -----
(#ValueLIst#)
>> </CFQUERY>
>>
>> [Do your data insert here, using the key/value data from
the Record struct]
----- Excess quoted text cut - see Original Post for more -----
assumed to be a field: build up the field and value lists
accordingly...
----- Excess quoted text cut - see Original Post for more -----
"'#FieldData#')>
----- Excess quoted text cut - see Original Post for more -----
AND
>>AMUSEMENTS
>>PBLS|Home Secretary's Department Brisbane, 1902
>>SUB1|ABORIGINAL CULTURE, AUSTRALIA HISTORY, NATIVE MONOGRAPHS,
19TH
>>CENTURY SPORTS
>>DESC|Good. 1st. 13.25 x 8.25. a Monograph of 39pp, illustrated by
----- Excess quoted text cut - see Original Post for more -----
,
>>DRAMATIC VISUAL ART
>>DESC|Illustrated Card. Very Good. 1st. Softcover. 8.5 x 9.25.
50pp
>>with triple expanding heart "111 Kleurprints". A major art work
by
>>an icon Dutch photgrapher, covers a little rubbed. "In all my
work
>>I have wanted to involve the framework in the representation so
as
----- Excess quoted text cut - see Original Post for more -----
7.25
----- Excess quoted text cut - see Original Post for more -----
----- Excess quoted text cut - see Original Post for more -----
>>the ideas running through this book is that if a student can make
----- Excess quoted text cut - see Original Post for more -----
Author: Seamus Campbell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32888#165016
Thanks Ben. That's a wonderful piece of code (most of which I'm
still trying to understand!)
I feel really stupid now but I'm still stuck.
I added your code and can now get the below.
I can't see how to get each field out of each record to input
into the database. I can see each record is a list with delimiters
of BOOK|, TRAN| ANAM| etc but I can't see how to use these
delimiters to get each field (does that make sense?)
Any help or clues would be great.
++++++++++++++++++++++++
BOOS| BOOK|000004 TRAN|D ANAM|ROTH, WALTER E. TNAM|NORTH QUEENSLAND
ETHNOGRAPHY Bulletin No.4 : GAMES, SPORTS AND AMUSEMENTS PBLS|Home
Secretary's Department Brisbane, 1902 SUB1|ABORIGINAL CULTURE,
AUSTRALIA HISTORY, NATIVE MONOGRAPHS, 19TH CENTURY SPORTS
DESC|Good. 1st. 13.25 x 8.25. a Monograph of 39pp, illustrated by
photograph and drawings, the endpapers have browned and the
exceedingly plain covers have surface tarnish but a bright
interior on superior paper. Walter E.Roth was Queensland's
"Northern Protector of Aboriginals" previously he was "Demy of
Magdalen College, Oxford". PRIC|35 UPDT|10/05/2004 UPTM|12:44:38
PM BCAT|Sold ABLE|1 UBID|1 STAT|Sold PPRC|0 PDSC|QLD BOOE|
Author: Seamus Campbell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32888#165015
No - it's Access and has to be done regularly
seamus
================ You wrote ================
What database? Does this have to be done with CF on a recurring
basis, or is
>>it a one-time thing? You should be able to do it with DTS if
you're using
----- Excess quoted text cut - see Original Post for more -----
AND
>>AMUSEMENTS
>>PBLS|Home Secretary's Department Brisbane, 1902
>>SUB1|ABORIGINAL CULTURE, AUSTRALIA HISTORY, NATIVE MONOGRAPHS,
19TH
>>CENTURY SPORTS
>>DESC|Good. 1st. 13.25 x 8.25. a Monograph of 39pp, illustrated by
----- Excess quoted text cut - see Original Post for more -----
,
>>DRAMATIC VISUAL ART
>>DESC|Illustrated Card. Very Good. 1st. Softcover. 8.5 x 9.25.
50pp
>>with triple expanding heart "111 Kleurprints". A major art work
by
>>an icon Dutch photgrapher, covers a little rubbed. "In all my
work
>>I have wanted to involve the framework in the representation so
as
----- Excess quoted text cut - see Original Post for more -----
7.25
----- Excess quoted text cut - see Original Post for more -----
----- Excess quoted text cut - see Original Post for more -----
>>the ideas running through this book is that if a student can make
----- Excess quoted text cut - see Original Post for more -----
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
