comma Delimited file

[fyrehed]

Hello,

I have a need to insert a csv file into a table. I am having a problem with the insert query, three of the 132 fields commonly have commas in the text within the field and making the query barf. Please help.

Matt

[philh]

You need another delimiter. There is no way to differentiate the commas in values from the comma delimiters.

[tmschmitt]

A CSV that contains text really should use a qualifier like double-quotes, as you can't always control what characters will be in the data, then you won't have that issue.
You would have to modify your CF insert code or use one of the many UDFs/CFCs that already exist and can handle the qulaifier.

[fyrehed]

phil,

So, the comma delimited file which is produced from Multiple Listing Service, should be something other than comma delimited. Problem is; This software lets you have any format you want, as long as its comma delimited :). Can anyone point me to a direction of converting the comma delimited to another delimiter with Coldfusion?

Matt

[philh]

Try using "," (that is, quote-comma-quote) as the delimiter. That of course wouldn't help if you have that string in a value ;^).

Have you tried setting up an ODBC connection to the file?

I'll get on MLS this evening and see what I can do with it.

[Dan_Bracuk]

quote:

---

Originally posted by: fyrehed
phil,

So, the comma delimited file which is produced from Multiple Listing Service, should be something other than comma delimited. Problem is; This software lets you have any format you want, as long as its comma delimited :). Can anyone point me to a direction of converting the comma delimited to another delimiter with Coldfusion?

Matt

---

If your csv file has a text qualifier, such tmschitt stated, you don't even have to go looking for udfs/cfcs. The cfhhttp tag can be used to create a query object and then you're off to the races. I'm not sure, but it might even work without the qualifiers.

Details are in the cfml reference manual. If you don't have one, the internet does.

[fyrehed]

Phil,

When you say "ODBC", are you referring to setting up a datasource in the ColdFusion administrator? The administrators will not make a datasource to this file. If there is another way, please let me know.

Thanks for your help.