Import Excel into MS SQL

[ctreeves]

I have an MS Excel document, not a csv, that I want to import into my MS SQL table.
I'm using CF8.
I can find articles on importing a csv, but it would be easier if I could import an Excel file directly.It's a very simple three column table with headers.Any suggestions on learning how to do this?Thanks

[ilssac]

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.

[Dan_Bracuk]

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.

[pirula08]

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.

[ctreeves]

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

[ilssac]

ctreeves wrote:

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.

Note the users don't need access to the SQL server.  It isn't that hard to create a SQL server solution that is triggered by a ColdFusion User Interface.  The point being that ColdFusion doesn't parse the excel file.  It just receives the file, passes the file to SQL server, and triggers the SQL server process to read and parse the excel file.  The point being that databases are often much better and parsing data then application servers like ColdFusion.

[ctreeves]

Thanks, this sounds like an interesting solution.

Could you point me in the right direction to learn how to do this?

[Reed_Powell-ttnmOb]

Here is what we do for exactly this situation:  1) Our users use a CF form to submit their XLS file.  2) CF Form processing page downloads the file and saves it on a network drive that both the CF and SQL server can access (this means both are running under a Windows account and not a local service account). 3) The CF Form processing page puts an entry into a table with the information about the filename and other details that the SSIS package will need to know. 4) On the SQL server there is a job that runs every 15 minutes that looks at the above table for an entry that hasn't been processed yet, and if it finds anything to do it does it, then marks the entry in the table as complete. We've actually generalized this to handle a variety of spreadsheet formats that have specific SSIS packages by including a column in that table to indicate which package should look at the entry; when each package's schedule job wakes up, it only looks for entries that it can handle.

We looked into trying to trigger the SSIS package in real time from CF, and gave up because it was turning into a goobered-together solution with CFEXECUTES of SQL command line statements of SP calls.  So long as you and your users can live with the 15-minute latency (max) between pushing the button on the form and having something happen with their data, the polling solution works really well.  Some of the SSIS packages send a notification back to the user when their their spreadsheet is processed, or if it has a problem with the contents of the spreadsheet.

SSIS is very capable at handling XLS and CSV files and processing them about a zillion times faster than CF is able to, and can handle all of the validation of the spreadsheet - that's important since you want to protect yourself and your data from users changing the spreadsheet layout.

-reed

[Dan_Bracuk]

I written some things that do the same sort of thing you are describing, but with different details.   One part of the job is to put files into a certain spot. The other part is to regularly check that spot and process any files that are there.