• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

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
0
mlcktmguy
Asked:
mlcktmguy
  • 7
  • 5
1 Solution
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now