0

My objective is to read an Excel sheet and load the data into a SQL Server table. I don't have Microsoft Office installed on the machine where I am trying to import the data. Is it required in order to read Excel data and import the data into SQL Server?

(I've used SAP Business Objects where I never installed MS Office but was able to load Excel data into that.)

Leigh
  • 28,765
  • 10
  • 55
  • 103
Balaji
  • 21
  • 4

2 Answers2

1

Finally I found the answer to my question.

Yes it is mandatory either to install MS Office or install Office drivers:Data Connectivity Components in the server we are trying to read Excel data. These drivers can be downloaded from the link http://blog.danbrust.net/2013/10/10/installing-microsoft-office-data-connectivity-components/#.WQnD_hOGPRZ

Balaji
  • 21
  • 4
  • Note: the drivers are free and are recommended to be installed on servers. MS Office is not free and should not be installed on a server – Nick.Mc May 03 '17 at 13:26
0

You can import Excel data into SQL Server from the SQL Server Management Studio. Expand the server you want to bring data into in the Object Browser, right click your database, go to Tasks then select Import Data. Find the Microsoft Excel option under the data-source property.

Here's a quick guide on using the Import data wizard: http://searchsqlserver.techtarget.com/feature/The-SQL-Server-Import-and-Export-Wizard-how-to-guide

You can also import delimited text files like a .CSV directly into SQL using a BULK INSERT command. https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

Let me know if this is helpful.

EDIT: A potentially useful SO thread: Import Excel spreadsheet columns into SQL Server database

Community
  • 1
  • 1
EMUEVIL
  • 502
  • 3
  • 14
  • Thank you for your response Emuevil, So to answer your query, there are 2 points to consider. 1. I tried the import command from management studio which lead to an error "Microsoft.ACE.OLEDB.15.0' provider. when I tried to execute to install those drivers, they got executed successfully but the same error pops up when I try to import the file. 2nd point. I want to do this through batch scripts so that i can give that to customer where a simple click will do the job rather than all this manual intervention. – Balaji May 02 '17 at 03:39
  • one test case I did was, I have used the same import command in another machine where MS Office was installed and it was successful.so my question is, is it mandatory to have Office installed or is there any work around to bypass Office and import data from Excel to SQL Server? – Balaji May 02 '17 at 03:45
  • @Balaji Perhaps you could look into a Powershell script. Powershell has a ton of control over basically anything windows. You might run into the same problems with not having the newer version of office installed though because maybe SQL Management studio requires them to be able to read in the new XML based excel formats. Here's a quick google result for importing data to SQL server from an excel with Powershell. Let me know if you find it useful: http://poshcode.org/1098 – EMUEVIL May 02 '17 at 13:24
  • 1
    I got the solution for my query which I will answer it here. But then you gave me a new option (Powershell script). I am totally new to this so I would love to give it a try with Powershell script also. Thanks so much :) – Balaji May 03 '17 at 11:38