listgetat problem

Hi all - I'm trying to (unsuccessfully) import a comma delimited text file (from an MLS service) into a MySQL db and looping over the file using listgetat in this manner: ... '#listgetAt('#index#',4, ',')#', '#listgetAt('#index#',5, ',')#', '#listgetAt('#index#',6, ',')#' ... I'm suspecting that the format of the text file is breaking my code. Here's a sample of the text file. "RES","A","AUN",776082,877,"","ST","RACINE","HOFFMAN TOWN",80011,3,2,"RES","1051","","","KELLER WILLIAMS REALTY LLC","CHARMING RANCH STYLE HOME, 3 BEDROOMS, 2 FULL BATHS, CONCRETE EXTENDED DRIVEWAY (ISSUES), NO FHA !!",5,"",,"","AURO",1681, Note that text items have quotes around them and number items don't. Also, text items can and do include commas within.  Am I improperly using the listgetat function?  If so, how can I rewrite it to get around these issues? Many thanks in advance, Les

The CSV format is not a comma-delimited list per line.  It's richer than that.  So you can't just use the list functions on it, you need an actual CSV parser.  One isn't hard to write (and there are several valid approaches), but the easiest course of action is to just grab one that is already written.  I've used http://ostermiller.org/utils/CSV.html on a few occasions with great success.  It's Java and token-based, but it's simple enough. cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

listGetAt() sees the "," inside the double-quotes as a list item.  So it sees this piece of text as two items in the text lstText:     <cfset lstText = "this is one, this is another one" />     <cfoutput>#listLen(lstText, ",")#</cfoutput> Instead of doing this via CF, try leveraging MySQL's text importer.  It will be much faster. MySQL has to have access to the file.  Here is the command:     LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name     FIELDS TERMINATED BY ','     ENCLOSED BY '"'     LINES TERMINATED BY '\n'; MySQL will load the file data.txt into the table tbl_name, so the table needs to exist.  The delimiter is specified by FIELDS TERMINATED BY.   ENCLOSED BY will tell it that all imported data are surrounded by double-quotes.  LINES TERMINATED BY will specify the row delimiter.  On windows, change this to '\r\n' (carriage return + line feed) Les Irvin wrote: ----- Excess quoted text cut - see Original Post for more -----

this better illustrates what I meant     <cfset lstText = '"this is one, this is another one", "this should be second item"' />     <cfoutput>#listLen(lstText, ",")#</cfoutput> It will output 3 rex wrote: ----- Excess quoted text cut - see Original Post for more -----

Hi, I currently have the same problem. Any solution regarding this? Regards, Christian

>>I currently have the same problem. Same as what?

replace commas with another arbitrary separator? <cfset lstText = '"this is one, this is another one", "this should be second item"' />     <cfoutput>#listLen(replacenocase(lsttext,',','||'), '||')#</cfoutput>

> replace commas with another arbitrary separator? Doesn't solve the problem. > <cfset lstText = '"this is one, this is another one", "this should be second item"' /> > > <cfoutput>#listLen(replacenocase(lsttext,',','||'), '||')#</cfoutput> That just replaces the first , - in the "string" with || and so you get a listLen() of 2: "this is one|| this is another one", "this should be second item" And multiple characters do not make a single delimiter. You actually have three elements there but the second - between the two | characters - is empty and CF ignores empty elements in lists. As Barney says, use a proper CSV parser that someone has taken the time and effort to write and debug. -- Sean A Corfield -- (904) 302-SEAN Railo Technologies, Inc. -- http://getrailo.com/ An Architect's View -- http://corfield.org/ "If you're not annoying somebody, you're not really alive." -- Margaret Atwood