[Reply To] RE: parsing help for a slow learner

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 -----

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|

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|   _____  

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 -----