SQLTeam.com | Weblogs | Forums

Importing Excel Files When Excel is Not Installed

Hi experts,
I have a file set that is all Excel files. There are so many files, monthly, that it is not practical to save each file as .csv.
So is it possible to build a package which can import Exel .xlsx files when Excel is not installed on the SQL Server?
Thanks for any tips.

Yes - you need to install the ACE drivers from https://www.microsoft.com/en-us/download/details.aspx?id=54920 (both x86 and x64).

You need to install both - install the first one normally and you can then install the other using the command line and /quiet parameter.

The x86 driver is needed for working with SSDT and building your package. The x64 is needed for access from SQL Server and SQL Server Agent.


Or you can use other scripting languages such as python. then using python you can also push data into sql server. or powershell
--SQL Part

create table jbeats(daterow datetime, 
Tmax decimal(5,2), 
Tmin decimal(5,2))
import pyodbc 
import pandas as pd

conn = pyodbc.connect("Driver={SQL Server};"               
cursor = conn.cursor()

def read_excel():
    df = pd.read_excel (r'data.xlsx', index=False)
    for i in df.index:
        INSERT INTO dbo.jbeats(daterow,Tmax, Tmin)  
        VALUES (?,?,?)""",
        df['Dates'][i], df['Tmax'][i], df['Tmin'][i])