Thanks Chris, I can agree that letting SQL do the job is a better way of doing this, I'll just need to find someone with a little stored proc or more SQL experience than me to help write the code.
Thanks, Rob -----Original Message----- From: Peterson, Chris [mailto:[EMAIL PROTECTED] Sent: 16 April 2007 14:02 To: CF-Talk Subject: RE: Parsing CSV I belive you can write an import script in SQL 2K5 to import it directly without having to parse in CF. I know you can do it manually with a right-click - import on the database, and you might be able to save that and invoke it with CF to import a file. That would probably be the fastest. Chris Peterson -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Monday, April 16, 2007 8:53 AM To: CF-Talk Subject: Parsing CSV Hello Guys, I'm looking to get some advice on the best method to parse a CSV into my SQL Server 2k5 database. The idea is that the data will be passed in as a string to a web service. In the past I've spoken to people who seem to think that the DTS is the best method for getting the text into the database but to be honest it seems less fitting now the data is passed into a web service, by the time the server has placed it into a file and then DTS has auctioned it, I may as well use a Query or a StoredProc. What are your thoughts on this? The data comes in as something like this. GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1 GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1 GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:10:00, 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:20:00, 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:22:00, 1 GF:34:00:3F:FD, 6, 5, 2007-01-01 13:26:00. 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:29:00, 1 GF:34:00:3F:FD, 6, 5, 2007-01-01 13:29:00, 1 GF:34:00:3F:FD, 6, 1, 2007-01-01 13:30:00, 1 GF:34:00:3F:FD, 6, 2, 2007-01-01 13:32:00, 1 Ideally this data would be placed into two tables, the first table would store all the MAC address's that are listed in the first column of the CSV, the other elements of the log would then be stored in a second table which would reference the MAC address using a foreign key. I'd be interested to hear your thoughts, these would most likely come in through the web service a few times an hour and be perhaps a hundred records long at tops. Thanks, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275338 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
