WOW - great ideas, I never thought of your approach, yes the input/update way should work. This db will be read only, so you're correct that I will not have to modify the data via the app.
Thank you, thank you. Thanks, Mark -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 12:55 PM To: CF-Talk Subject: RE: Where to Begin CFHTTP will get you the data from the remote host, then you'll have to parse it into INPUT (or UPDATE) statements for your database. I'm guessing that the information you recieve will never be modified via your app. If that's the case, I'd vote for deleting all records and then reinserting them each time you update. That'll save you the trouble of needing to remove obsolete records, update existing records, and insert new records; you insert everything. However, that might not be possible. List functions won't really help much, because they don't handle missing elements nicely. I'd take a different approach using replace() combined with the listToArray() function. Start by doing this: records = listToArray(replace(CFHTTP.filecontent, "'", "''", "all"), "#chr(10)##chr(13)#") which will get you an array of records with single quotes already escaped. Then for each one do this: insert into table (... values '#preserveSingleQuotes(replace(records[i], chr(9), "', '", "all"))#' That'll replace the tabs (chr(9)) with commas, and surround every field value with single quotes, which will handle the strings. Just make sure your DB will automatically cast strings to numbers if needed (ie pass in '3' to a INTEGER column, and i'll convert it to 3 automatically). That's the approach I'd take, and it should work for most cases. If you have a specific DB, then it might be easier to write the file to disk, perhaps with some formatting changes, and then use the DB to import the data into the tables for you. However, the exact method will vary greatly between systems. HTH, barney > -----Original Message----- > From: Mark Leder [mailto:[EMAIL PROTECTED] > Sent: Monday, March 17, 2003 9:38 AM > To: CF-Talk > Subject: Where to Begin > > > Hi All, > At regular date intervals, I will be provided (or be able to retrieve) > via web a tab-delimited list of employee names, addresses, etc. This > .txt file has a header row, and the position of the fields will not > change. (ie, ID, employee type, first name, last name, ...) There are > about 500 employee records in this file. Some records may have empty > individual fields. > > What I want to do is automatically port this to a database, such as > Access or SQL 2000, then be able to do searches, based on a field > name. I know how to do everything after getting it ported to the > database. I guess I don't know where to begin on this, CFHTTP, using > list functions, etc. Any ideas, > methodology or links to articles / tutorials is greatly appreciated. > > Thanks, Mark > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
