Brian, If your data in Excel is consistent, then you can use SQL to read the Excel file. You can even use SQL and Jet to query both Excel and SQL Server data and join them together in a single query.
To start with, create a blank Access database. Then create a linked table from the Access database to your SQL Server database table. Then create a CF DSN for the Access database. With this set up you can query the Excel file using Jet's special IN clause and insert the data directly into the SQL Server table. INSERT INTO SQL_Server_Table (Field1, Field2, Field3) SELECT Field1, Field2, etc. FROM Sheet1 IN 'c:\wherever\myfile.xls' For more details about using SQL to read from Excel, you can take a look at the October (current) issue of Access-VB-SQL Advisor magazine. I have an article in there outlining four methods of working with Excel documents (Automation, Jet, Properties, and XML). Examples are in VB, but the concepts apply equally to CF. HTH, Sam BTW, if the stuff you got from CFComet uses Automation (cfobject) to read the Excel file, I'd advise you to ignore it. Excel is not safe for unattended execution and should not be instantiated on the server except in a very controlled environment (i.e., you have a dedicated server just used for Excel automation, talk to it through a message queue, and use a scheduled task or probe to reboot the server if Excel stops responding). Date: Tue, 24 Sep 2002 13:51:40 -0400 From: Brian Ferrigno <[EMAIL PROTECTED]> Subject: Cold Fusion and Excel question Message-ID: <005B1CF6DC6FD511936D00A0CC5AF52F2A98BA@SB-EXCHANGE> I need some help in having CF and excel play nice together. Basically I need to import data from an excel spreadsheet into cf, have my application read in the column headers from the first worksheet then try to match the data in each column with a particular column in a SQL Server table. The SQL bit is easy enough, what I need more information on is how to do the excel step. I've already been to cfcomet and can get the file to open in CF. What I need information on is the next few steps involved. This is the first time I am attempting this so any help would be great. Thanks. Brian ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
