6

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.

8

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.

| improve this answer | |
  • where do I mention the table name? – Piyush S. Wanare May 25 '18 at 5:00
  • 1
    @PiyushS.Wanare You can replace EXCEL_IMPORT with your own table name. – Geoff Dawdy Jun 5 '18 at 21:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.