Import Excel into MS SQL

Thanks all.

It will be done on a regular basis and by people who won't have access to the SQL server, so a CF interface is what I am looking for.

I found Ben's info on POI, but found it a bit overwhelming.  Maybe that's what I need to read and see if I can figure it out.

Thanks

The solution is simple, first save file as .CSV (open excel/save as/other formats/select CSV format - it should be possible ) and then simply import CSV file directly into your database. Here is what I use in MySQL:

load data local infile 'C:\yourfile.csv' into table yourtable
fields terminated by ','
enclosed by '"'
lines terminated by ' '
(column1,column2,column3,column4,column5)

keep in mind that column names in csv must be the same as column names in your database. Also you must have the same number of columns.

Did you find this article?  http://support.microsoft.com/kb/321686

Some general notes on importing data are:

Database tools are often more appropriate than Cold Fusion apps.

It is often wise to import into a working table first, then doing whatever validation/processing is necessary, then inserting into your real tables.

The best answer would be to more or less ignore ColdFusion.

MSSQL understands MS Excel just fine.  If you can work directly with the database it should be fairly simple to set up an importation from the excel file.  If this is something that needs to be done repeatedly and through a web interface then all CF needs to do is to receive the file and move it to a convenient location for the database and finally trigger the database import routine.

Otherwise, for CF8 you are probably looking at the POI utility (Lots of Internet search results for "ColdFusion POI").  This would allow you to parse the data out of the excel file with some work.