How to solve Export/import data from Excel into a table with queries?

504 Asked by CelinaLagunas in Salesforce , Asked on Jul 12, 2021

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.

Answered by Chris EVANS

I've used Openrowset a number of times for this task. import data from excel to sql server using query code will create a table in SQL.

  SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:ExcelSpreadsheet.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.

Hope this helps!

How to solve Export/import data from Excel into a table with queries?

Your Answer