Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excel Data to SQL Server table

Author  Topic 

junior6202
Starting Member

45 Posts

Posted - 2014-07-22 : 17:14:19
Hi All,

I wanted to see if i could get some guidanace for this task that I am working on. I've task with creating a query that will import data from an Excel .csv file. I wrote a simple query using an

INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.csv', [Sheet1$])


but I am having no luck. I know that you can use the SQL query wizard to import an Excel Data to SQL but I'm planning on making this task in to a macro and making it automatic. Thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 17:16:06
You'll need to describe "but I am having no luck". Are you getting an error? If so, please post it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

junior6202
Starting Member

45 Posts

Posted - 2014-07-23 : 08:37:20
I am getting an error. I think my problem is my file path, I'm using Excel 2010, this is the error i'm getting

Msg 7308, Level 16, State 1, Line 4
OLE DB provider 'Microsoft.JET.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 12:31:13
The file path must be from the database server's perspective and not your client machine. Does D:\test.csv exist on the database server?

Also you need to run this:

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

And check this as you might have the wrong connection string: http://stackoverflow.com/questions/22032222/ole-db-provider-microsoft-jet-oledb-4-0-cannot-be-used-for-distributed-queries

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -