Thanks again. I tried the approach below which was recommended on cf411.com.
It works as long as the file extension is .txt. When using the .csv
extension, it throws an error "Variable onerow does not exist".
I am fine with just using the .txt extention but if anyone has any clue why
or how I could use a .csv extension, I would love to hear it.
Thanks again for the replies. It always helps to hear others opinions.
Dusty
----------------------------------------------------
*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>
On Mon, Mar 9, 2009 at 2:11 PM, Mischa Uppelschoten ext 10 <
[email protected]> wrote:
>
>
> : My other thought would be ask if you think it would be ok to just use the
> : files themselves as the database rather than import and use MS SQL. This
> way
> : the files could just be uploaded to the server and overwritten when they
> need
> : to be updated. There are basically 5 files with some location data in it
> that
> : are used for a specialized dealer locator app. My thought was maybe I
> could
> : just read the files into a select query and then loop the query to
> filter the
> : records to be displayed. There will never be more than several hundred
> : records in the excel files. However the site that will use it may have a
> fair
> : amount of traffic so I didnt know if reading the files might become a
> : problem. Perhaps I could read them into a cached query to solve that
> problem?
> :
> I have a similar situation where my excel file updates a few times per week
> and I need to read it just once and the format does not change: you can
> define an ODBC DSN to the file and read that from CF like any other true
> database. After the definition is done and is working (inspect the
> schema.ini file, it will make sense once you look at it), you could even
> just replace the excel file with a new version without doing anything else.
> Only drawback I can think of is that it's slightly more difficult to
> combine your excel data with any data housed on your db server if you don't
> truly insert it in a table.
> /m
> ------------------------------------------------------------- To
> unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform For more info, see
> http://www.acfug.org/mailinglists Archive @
> http://www.mail-archive.com/discussion%40acfug.org/ List hosted by
> http://www.fusionlink.com-------------------------------------------------------------
--
Dusty Hale
Website: www.DustyHale.com
Email: [email protected]
Phone (Atlanta): 404.474.3754
