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();
After along delay I am revisiting this.
jeffw8713, I installed the ACE drivers on this SQL Server.
The package first inserts to a table, then it moves the .xlsx file and renames it.
All is working except the Execute SQl Task doesn't insert any rows to the table. No package errors either.
This is the query for the Execute SQl Task:
declare @dynamicBulkInsert varchar(max)
set @dynamicBulkInsert =
'BULK INSERT Database.dbo.[Table]
FROM ''' + ? + '''
WITH
(
FIRSTROW = 2
, FIELDTERMINATOR =''|''
, ROWTERMINATOR = ''oxoa''
)';
exec(@dynamicBulkInsert)
I don't know if the FIELDTERMINATOR and ROWTERMINATOR values are correct, but the task doesn't throw any errors. But no rows are inserted to table.
I'm sure it's something simple. Any ideas?
And thanks very much. John
Why are you using this to load the data? Since you now have the Excel drivers - you can create an Excel connection and read the file directly.
A standard data flow will suffice - you would setup an Excel source, a data type conversion or derived column transformation (if needed - to support unicode to ansi) and a SQL Server destination (OLEDB).
If you actually have a delimited file - which it appears you do - then your source is even easier. Just use the flat file connection manager to setup the source as a delimited file.
No need to try an bulk insert from within SSIS - that is counter to how SSIS is designed by accessing SQL Server to pull data from a file when SSIS has direct access to the file already.
1 Like
Thanks for the suggestions, Jeff. The Excel Connection may work if it can be a Dynamic connection. I have a dozen or so files to loop thru each month. That part was working in my original solution (the looping thru all files).
Yes it can be dynamic, the same as any other connection. In the for each loop set a variable for the file and use that for the connection.
1 Like
I'm making some progress. BTW the development is being done solely on this 2014 SQL Server, not a laptop. Office is NOT installed, only the ACE drivers.
the file I'm trying to import is .xlsx and it is the first excel file we have attempted
When I run:
execute master.dbo.xp_enum_oledb_providers
It shows:
But the package is giving this error in Design mode:
the requested ole db provider microsoft.ace.oledb.12.0 is not registered
To develop - the x86 driver needs to be installed and for deployment the x64 driver needs to be installed. It seems you don't have the x86 driver installed on this server - and it also appears you are trying to use an older version of the driver.
I am still a bit confused...are you trying to load an Excel file or a pipe-delimited file? Your bulk insert statement appears to be looking for a pipe-delimited file.
In SSDT when you select the Excel connection it defaults to a lower version - make sure you are selecting the latest version so you are using the latest driver.
Yes - this is an Excel file. The extension is .xlsx
I'm going to uninstall both ACE drivers and install ACE 15 x86 and x64 then see if the situation improves.
I assume the columns are tab-delimited by default when the file is .xlsx ?
Thanks for your time on this, Jeff. John
Excel files are not delimited - you cannot open an Excel file in Notepad for example...if you try you will see a lot of 'special' characters with no formatting.
You cannot bulk insert an Excel file - but you can use OPENROWSET through a linked server to that Excel file. However, that isn't needed here...you just need to use the Excel source in SSIS to read the file. To do that you need the x86 driver available on the machine where you are running SSDT...and for deployment you need the x64 driver available on the server where the package will be run.
1 Like
JeffW8713 - With your help, I was able to get my complete package working. It now loads all of the excel files into the table. Woohoo. Basically, all I did was to uninstall both ACE drivers - 32 bit and 64 bit.
I reinstalled the 32 bit and the package works. Next I'll install the 64 bit and hope that doesn't break anything. The Agent job will run on this same server thus the need for the 64 bit driver. We normally don't develop and execute on the same SQL Server but that's the way this one is.
Just wanted to say a big Thank You for taking an interest in my project.
Take care and Merry Christmas/Happy Holidays.
That is good to hear - you can install both drivers, you just need to use the /quiet parameter from the command line on the second install. That way you will have both of them installed and available on that machine.
I quickly found that the 2 drivers cannot co-exist on the same server. No problem, I just configured the job step (which executes the SSIS package) to run in 32 bit mode. It works fine. Thanks
Both the x86 and x64 drivers can co-exist on the same system - to install you just need to use the /quiet parameter on the command line to install in the background.
Yes I tried installing with the /quiet parm but it still failed. No worries, it is working well running in 32 bit mode.
Not sure what the problem is - I have both installed on my workstation and on several servers.