Import from Excel (.xlsx) to SQL Server 2014




SELECT * 
--into aNewTable
FROM
OPENROWSET (      
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=c:\temp\test.xlsx;HDR=YES;IMEX=1', 
    [Sheet1$]
  )
  WHERE [F2]<>''
  --Order By [F3]

    --If your excel file is open, you may see this error. It happens to me quite often.
 /*
 Msg 7399, Level 16, State 1, Line 11
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
 
 
 */


 ---Import only part of an Excel Sheet and use one row as header row to use the text in each column  as table column if we create new table

SELECT *
--into mytest8 
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\temp\test.xlsx;Extended Properties=Excel 12.0;HDR=Yes;IMEX=1'
,'select * From [Sheet1$A3:E20]'  )
 


 

Very good resource for working between Excel and SQL Server.
https://www.simple-talk.com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

Advertisements

One Comment on “Import from Excel (.xlsx) to SQL Server 2014”

  1. Saurabh Banerjee says:

    Thanks , it worked. However,
    If I apply the same code in the SQL job (Agent) it is failing with same above error, but running the query is working fine as mentioned above …


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s