Importing Excel into SQL Server… for Spotfire!

Consider this mouthful: you have a file on a network server, that you want to put into SQL server, that you want to put into Spotfire! I’ll show you this process below along with the common issues that enjoin it.

Create a Linked Server

We need a way to connect to your Excel file from SQL Server. For this script, change the URL below:

DECLARE @RC int

DECLARE @server     nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider   nvarchar(128)

DECLARE @datasrc    nvarchar(4000)

DECLARE @location   nvarchar(4000)

DECLARE @provstr    nvarchar(4000)

DECLARE @catalog    nvarchar(128)

-- Set parameter values

SET @server =     'COMPLETIONSLINK'

SET @srvproduct = 'Excel'

SET @provider =   'Microsoft.ACE.OLEDB.12.0'

SET @datasrc =    'C:\Users\Administrator\Desktop\CompletionData.xlsx'

SET @provstr =    'Excel 12.0 Xml'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

The gotchas/solutions are outlined below:

Configure the Linked Server

Once you’re setup, you need to log into SQL Management Studio and change the following settings:

1. Open Server Objects > Linked Servers > Providers and then select Microsoft.ACE.OLEDB.12.0.
2. Right-click and select Properties.
3. Check Allow inprocess.
4. Press OK.

Create the Stored Procedure

Now create the stored procedure to upload the file:

CREATE PROCEDURE [dbo].[LoadCompletions]

AS

BEGIN

drop table if exists CompletionWellHeader;

SELECT * INTO CompletionWellHeader FROM COMPLETIONSLINK...[Data$];

END

Imported! Now all you need to do is run the stored procedure to import the file.

Icing on the Cake

You can setup a SQL Agent Job to run the stored procedure as a nightly job, importing the file at night. It will be important for everyone interacting with the file to be closed out before the day is over.