Sql-server – Export/import data from Excel into a table with queries

sql serversql server 2014sql-server-2008-r2t-sql

I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it.

I've come across some scenario's where I would have an Excel document with 70,000 rows and about 10-12 columns imported into SQL Server (a table), make use of it to compare/insert/modify existing data in other tables. I'm currently using the manual feature available in GUI (right click >> Tasks >> Import) to import and export when needed; however, I've got informed by one of my colleagues that the same procedure can also be done through script/queries.

Can someone let me know what is the correct way of writing a query to import XLS, XLSX, CSV format files into a table?

P.S.: I've also tried reading some posts about OPENDATASOURCE and OPENROWSET where I've not been successful, always getting some errors about which I have no clue. So, looking to see if there are any other ways, and keen to learn their procedure.

Best Answer

I've used Openrowset a number of times for this task.

This code will create a table in SQL.

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. That way you can control the column data types.

Related Question