How to export/import data from Excel into a table with queries?
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 As I am importing an excel file into sql server 2014, 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 am keen to learn their procedure. How do I import an Excel file into SQL Server 2014?
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:ExcelSpreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]');Ideally, you want to create the table first and then use INSERT INTO instead of SELECT INTO. That way you can control the column data types. The quickest way to get your Excel file into SQL is by using the import wizard:
- The quickest way to get your Excel file into SQL is by using the import wizard:
- Open SSMS (Sql Server Management Studio) and connect to the database where you want to import your file into.
- Import Data: in SSMS in Object Explorer under 'Databases' right-click the destination database, select Tasks, Import Data.