Note that cfhttp can do this automatically. You need to make sure your CSV is valid -- qualifiers around any fields containing the delimiter in the data (i.e. quotes around any data containing commas), and escape any qualifiers in the data (i.e. double up any quotes in the data).
http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/h tml/wwhelp.htm?context=ColdFusion_Documentation&file=00000272.htm >From the docs.... Building a query from a delimited text file The cfhttp tag can create a ColdFusion query object form the response body. To do so, the response body must consist of lines of text, with each line having fields that are delimited by a character that identifies the column breaks. The default delimiter is a comma (,). The response data can also use a text qualifier; the default is a double-quotation mark ("). If you surround a string field in the text qualifier, the field can contain the delimiter character. To include the text qualifier in field text, escape it by using a double character. The following line shows a two-line request body that is converted into a query. It has three comma-delimited fields: Field1,Field2,Field3 "A comma, in text","A quote: ""Oh My!""",Plain text Run the following code to show how ColdFusion treats this data: <cfhttp method="Get" url="127.0.0.1:8500/tests/escapetest.txt" name="onerow"> <cfdump var="#onerow#"><br> Column names can be specified in three ways: By default, ColdFusion uses the first row of the response as the column names. If you specify a comma-delimited columns attribute, ColdFusion uses the names specified in the attribute as the column names. Set firstRowAsHeaders="no" if the first row of the response contains data. Otherwise, ColdFusion ignores the first row. If you do not specify a columns attribute and set firstrowasheaders="no", ColdFusion generates column names of the form Column_1, Column2, etc. The cfhttp tag checks to ensure that column names in the data returned by the tag start with a letter and contain only letters, numbers, and underscore characters (_). ColdFusion checks for invalid column names. Column names must start with a letter. The remaining characters can be letters, numbers, or underscores (_). If a column name is not valid, ColdFusion generates an error. -----Original Message----- From: Ken [mailto:[EMAIL PROTECTED] Sent: Tuesday, 19 July 2005 10:26 a.m. To: CF-Talk Subject: Daily DB updates from CSV files Hi. I building a coldfusion app to upload the contents of a huge csv file (9 cols x 22000 rows). I am facing a few issues though. 1. A lot the cells in the csv are blank at times, producing 2 commas together, that messes up my entire array. 2. the prsence of commas in my data also creates an issue when trying to sparate individual pieces if data to be uploaded to the DB. Any ideas, please. Example Code preferred. - Ken ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212175 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
