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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
