Batch Inserting from Excel into SQL Server

Question

Is there a way to do a batch update on SQL Server from a row of data in Excel? We have excel documents that contain 2000+ plus rows and need to be imported in SQL Server. Is there a way to do a batch insert of these guys without calling the database over and over to insert one row at a time?

Solution 1

SQL Server Integrations Services offers a wizard based import which can help you easily set up a package to import an excel file. You can even save the package and schedule it to repeat the import in the future.

You have other options, as well. If you save the excel file to a comma or tab delimited text file, you can use the BULK INSERT t-sql command. See an example on the sqlteam.com forums.

Another T-SQL option is SELECT INTO. Excel is a valid OLEDB or ODBC data source from T-SQL. Here's an example.

There's also a command line import tool included with Microsoft SQL Server called BCP. Good documentation on BCP and the other options can be found on MSDN at: http://msdn.microsoft.com/en-us/library/ms187042.aspx

Solution 2

Yes! Use the import/export wizard of the SSMS! Use an Excel-source and a SQL Server destination. You can also create a SSIS-Package in the BIDS or use the BULK INSERT-statement from T-SQL, if you convert your Excel-sheets in to CSV-files.

Solution 3

You can create an SSIS package to read your Excel file. When you create your task, you can select a connection type of "Excel", and then it helps you create an "Excel Connection Manager". Then you can easily send the data to your SQL Server table. Here's a tutorial on how to import an Excel file into SQL Server (2005). Give it a look.

Comments

[Aaron]

+1 for BCP. I used it once in a pinch, and I was amazed at how fast it imported about a half-million rows.

[Mike Flynn]

Will look into these links. These options are automated, correct? What I mean is a user uploads an Excel file and it gets processed on the spot.

[Tom Resing]

@MikeFlynn I'm not sure where your user is uploading the Excel file, but if you can control that, you should be able to schedule a job to check the location regularly and process the files in queue.

[Mike Flynn]

So a queue would be my best bet, instead of doing it on the fly. It would just take to long for a response. I assume I can send an email off after the import is done stating it is complete.

[Aaron]

@MikeFlynn it sounds like what you're after might be better solved with an "external table". Unfortunately, that's an Oracle-based feature and (AFAIK) SQL Server doesn't have anything like it. All the the solutions that we're discussing here are those that you'd have to automate yourself.

[Nick.McDermaid]

All this automation can be built but the issue is any Excel file which can be manually edited by a user is guaranteed to contain rubbish that will break the import process, so you need to build in a lot of workflow/reject/retry type logic which is a pain, i.e. alphas in a numeric field, and DATES

[Nick.McDermaid]

BCP is great but will only import CSV's (not Excel). Can you confirm these are actually XLS type files, not CSV (CSV <> Excel). Also SQL Server has OPENROWSET which is similar to Oracle external tables, but still has all the same issues with data quality.