Can a spreadsheet be imported directly into a SQL table from a Stored Procedure?

Can a spreadsheet be imported directly into a SQL table from a Stored Procedure?

I am trying to automate a conversion of a cient's legacy data.  Their data is supplied in different formats, one of which is a spreadsheet.

Some of the data is in fixed length text files and I am using SQL bulk copy to load them.

I am fairly new to SQL Server and wonder if there is a way to import a spreadsheet directly into a SQL table from within a stored procedure.

I am currently importing the spreadsheet directly into an Access table but that is way too slow.

I created the table in SQL by importing the MS Access table that I'm currently importing the spreadsheet into.  I don't know the mechanics of importing the spreadsheet or even if it is possible.

This is the table definition:
CREATE TABLE [dbo].[Balance_Work](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SkipOrig] [nvarchar](255) NULL,
	[Skip] [nvarchar](255) NULL,
	[LegalFlagC] [nvarchar](255) NULL,
	[TaxType] [int] NULL,
	[BlockLot] [nvarchar](255) NULL,
	[TieBreaker] [nchar](10) NULL,
	[Full_Int_Add_TypeID] [int] NULL,
	[SequenceNo] [int] NULL,
	[Priority] [float] NULL,
	[MuniCode] [int] NULL,
	[Owner] [nvarchar](255) NULL,
	[Addr1] [nvarchar](255) NULL,
	[Addr2] [nvarchar](255) NULL,
	[Addr3] [nvarchar](255) NULL,
	[Year] [float] NULL,
	[EnterDate] [date] NULL,
	[IntDate] [date] NULL,
	[Face] [float] NULL,
	[Penalty] [float] NULL,
	[Interest] [float] NULL,
	[Cost] [float] NULL,
	[Comm] [float] NULL,
	[TotalDue] [float] NULL,
	[Pmts] [float] NULL,
	[Balance] [float] NULL,
	[Area1] [nvarchar](255) NULL,
	[Area2] [nvarchar](255) NULL,
	[FacePaid] [float] NULL,
	[PltyPaid] [float] NULL,
	[IntPaid] [float] NULL,
	[CostPaid] [float] NULL,
	[CommPaid] [float] NULL,
	[SoldFlag] [float] NULL,
	[PostageExpenses] [float] NULL,
	[AttyFees] [float] NULL,
	[RecordCosts] [float] NULL,
	[FaceBal] [float] NULL,
	[IntBal] [float] NULL,
	[PltyBal] [float] NULL,
	[CostBal] [float] NULL,
	[CommBal] [float] NULL,
	[BalDueWithoutExpenses] [float] NULL,
	[BalDueWithExpenses] [float] NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [Balance_Work$ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

MJOTestSummaries-20171231---Copy.xlsx
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elisha koech™Programmer and DBACommented:
Hi yes you can
use the simple querry like one below
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database='Excel location path\Data.xlsx', [Data$]);
GO

refer this link for further information  https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql
0
mlcktmguyAuthor Commented:
Thanks, here's my attempt:
USE JTSConversion
GO
Select * INTO Balance_Work
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database='I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOTestSummaries 20171231 - Copy.xlsx', [Library$]);
Go

Open in new window


The location and name of the spreadsheet is:
I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOTestSummaries 20171231 - Copy.xlsx

the table I want to input into is Balance_Work


'Library' is the worksheet name so I used [Library$]

I got the following errors
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'I:'.
Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string ', [Library$]);
0
mlcktmguyAuthor Commented:
Are there any ideas out there on how to get this done?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark WillsTopic AdvisorCommented:
Yep, the openrowset approach *should* work.

I will set up a little demo and get back to you...
0
Mark WillsTopic AdvisorCommented:
OK, so this works for me
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\mrwtest\test.xlsx', [Library$]) 

Open in new window

test.xlsx

Will investigate yours,

OK, had to edit sheet name and then ran
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\mrwtest\test folder\MJOTestSummaries-20171231---Copy.xlsx', [Library$]) 

Open in new window

and got the results
SkipOrig	Skip	LegalFlagC	BlockLot	Priority	MuniCode	Owner	Addr1	Addr2	Addr3	Year	Enter Date	Int Date	Face	Penalty	Interest	Cost	Comm	Total Due	Pmts	Balance	Area1	Area2	FacePaid	PltyPaid	IntPaid	CostPaid	CommPaid	SoldFlag	PostageExpenses	AttyFees	RecordCosts	FaceBal	IntBal	PltyBal	CostBal	CommBal	BalDueWithoutExpenses	BalDueWithExpenses
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2002	2004-12-30 00:00:00.000	2004-12-30 00:00:00.000	207.36	0	271.3	0	47.87	526.53	153.21	373.32	Main Center	NULL	0	0	139.28	0	13.93	0	0.49	0	0	207.36	132.02	0	0	33.94	373.32	373.81
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2003	2004-12-30 00:00:00.000	2004-12-30 00:00:00.000	207.36	0	271.3	13	49.17	540.83	0	540.83	Main Center	NULL	0	0	0	0	0	0	0.49	0	0	207.36	271.3	0	13	49.17	540.83	541.32
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2004	2007-12-18 00:00:00.000	2007-12-18 00:00:00.000	140.73	0	141.9	0	28.26	310.89	0	310.89	Main Center	NULL	0	0	0	0	0	0	0.49	0	0	140.73	141.9	0	0	28.26	310.89	311.38
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2005	2007-12-18 00:00:00.000	2007-12-18 00:00:00.000	207.36	0	209.09	0	41.65	458.1	0	458.1	Main Center	NULL	0	0	0	0	0	0	0.49	0	0	207.36	209.09	0	0	41.65	458.1	458.59
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2006	2007-12-18 00:00:00.000	2007-12-18 00:00:00.000	207.36	0	209.09	13	42.95	472.4	0	472.4	Main Center	NULL	0	0	0	0	0	0	0.49	0	0	207.36	209.09	0	13	42.95	472.4	472.89
NULL	NULL	NULL	1234-B-3457	5	0101	Mike Smith	NULL	123 Main St	AnyTown, ZZ 99999	2007	2010-02-23 00:00:00.000	2010-02-23 00:00:00.000	207.36	0	164.16	0	37.15	408.67	0	408.67	Main Center	NULL	0	0	0	0	0	0	0.49	0	0	207.36	164.16	0	0	37.15	408.67	409.16

Open in new window


So, first step, try saving my simple spreadsheet somewhere locally, change the path in my first example above and give that a try....
0
Mark WillsTopic AdvisorCommented:
Oh, cannot SELECT * INTO <table> FROM ...

if <table> already exists

Have to use INSERT <table>  SELECT ...  Or, insert into a staging table then update your <table>
INSERT Balance_Work  -- should spell out column names
SELECT *                    -- should spell out column names
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOTestSummaries 20171231 - Copy.xlsx', [Library$])

Open in new window

Or
if object_id('tmp_Balance_Work','U') is not null drop table tmp_balance_work

Select * INTO tmp_Balance_Work
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOTestSummaries 20171231 - Copy.xlsx', [Library$])

Open in new window

AND found your error, there is a single quote between "database=" and "i:\my...."

Removed in the above examples
0
mlcktmguyAuthor Commented:
Mark,
I tried yours but got this message

Msg 15281, Level 16, State 1, Procedure spzBogus, Line 19
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
0
mlcktmguyAuthor Commented:
Found the resolution to that message by setting the options.  Now my routine looks like this

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO  

if object_id('tmp_Balance_Work','U') is not null drop table tmp_balance_work

Select * INTO tmp_Balance_Work
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOTestSummaries 20171231 - Copy.xlsx', [Library$])


Go

Open in new window


Now I am getting this error:
Msg 7302, Level 16, State 1, Line 16
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Line 16 is
Select * INTO tmp_Balance_Work

Open in new window

0
Mark WillsTopic AdvisorCommented:
OK, lets go back to basics. Try saving my test.xlsx into a local directory and lets select * from that.

And...  Make sure you arent still in the spreadsheet, And... You might need to do
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

Open in new window

0
mlcktmguyAuthor Commented:
I get the same error.
Msg 7302, Level 16, State 1, Line 14
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I found some explanation and suggestions at this link
LinkIMporting Spreadsheet Issue

It suggests
If your windows is 64 bits, you will need to download and install “Microsoft Access Database Engine 2010 Redistributable”

For 64 bits
AccessDatabaseEngine_x64.exe

I am using the 32 bit version of Microsoft Office Professional.  I am concerned that installing the 64 bit version of the AccessDatabase engine might cause issues with all my other applications

Sorry but I have to turn in for the night.  I have a very early day tomorrow.  I'll have to pick this up tomorrow.
0
Mark WillsTopic AdvisorCommented:
I had a problem when moving to a new WIN 10 machine. 64 bit machine. What I was unaware of is the version of office being pre-istalled was 32 bit.

Big problems ensued

But went to the MS Office site and upgraded to 64 bit

Scary as it was, it ended up being quite straight forward. For me at least because I was more concerned about the SQL Server side of things (and was already 64 bit), and if I had to forfeit anything, it would be the Office side (even though, I have quite a few Access, Excel etc significant projects).

Anyway, bit the bullet, backed up everything, copied disks, (and still had my old machine). Logged in to Microsoft MyAccount as per instructions, did  the 'reinstall' with 64-bit versions, and was pleasantly surprised how well it went.

https://support.office.com/en-us/article/download-and-install-or-reinstall-office-365-or-office-2016-on-a-pc-or-mac-4414eaaf-0478-48be-9c42-23adc4716658?ui=en-US&rs=en-US&ad=US#installsteps=pc_step-by-step

Some reading for you - the one that started it off for me : https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

Now, I dare say, the reason why it worked for me is because I had options, and possible given my disposition toward SQL 64 bit over Office 32 bit, it made the Office reinstall to 64 bit a lot easier decision for my personal environment.

Very tough decision otherwise.

Your other choice would be to export from Excel and then import into SQL Server rather than link directly to Excel.

How big and How many and How often do you need to import - understanding that this is part of a conversion process ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
I took a chance and tried to install the 64 bit redistributable.  The system showed an error that it could not install the 64 bit version because the 32-bit version was already installed.  So that option is out.

This is a related to the one time conversion.  There are 4 spreadsheets of varying sizes.  The largest has 557,000 lines.  The smallest has 40,000 lines.

I am hesitant to export the spreadsheet into another format for fear that something may be lost or not exported correctly.  

That seems to leave me out of options for processing this information, in spreadsheet format, directly into SQL.
0
mlcktmguyAuthor Commented:
"Your other choice would be to export from Excel and then import into SQL Server rather than link directly to Excel."

This turned out to be the only option on my system.  Thanks,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.