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:
> 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
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331770
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
