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.

2 Likes

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};"               
               "Server=localhost;"
               "Database=sqlteam;"
               "Trusted_Connection=yes;")
cursor = conn.cursor()


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

read_excel();