July 17, 2015 at 1:13 pm
There seems to have been many discussions on this but I couldn't find something specific to what I am looking for. I am trying to use a query to import data from Excel to an existing table in SQL Server. My Query is:
INSERT INTO DailyRawData
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=NO;
Database=C:\Users\home\Desktop\SQLImportTrim.xls',
[data$]);
I get the following error:
Msg 7314, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "data$". The table either does not exist or the current user does not have permissions on that table.
I don't think this is a permission issue for me as I am set up as Sys Admn. I am wondering if the error is due to the last part of the query, "[data$]", since this is what the error msg initially refers to. FYI the name of the excel file is "SQLImportTrim" and the tab that contains all my data is named "data". There is no table named "data" in my excel file. Is my query correct?
July 17, 2015 at 1:18 pm
Is the file in the server? The path refers to the server, not the client.
Does the Service account have permissions to the file? Remember that it's not your account, but the account for SQL Server.
More information can be found here: https://msdn.microsoft.com/en-us/library/ms175915.aspx
July 17, 2015 at 1:34 pm
Both the SQL server and Excel are on my C:/
Not sure what you mean by client. Sorry I'm not a IT professional. Trying to learn this.
July 17, 2015 at 1:35 pm
Also, not sure what you mean by "it's not your account, but the account for SQL Server."
July 17, 2015 at 1:45 pm
The client is the computer with the user interface (most commonly Management Studio). In this case, the client and the server are the same computer.
SQL Server uses service accounts to perform all its processes. These account could be your user accounts or different accounts depending on how you configured SQL Server. You can review this configuration in the SQL Server Configuration Manager.
To use OPENROWSET, the account depends on the kind of authentication used in SQL Server. If you use SQL Server login, OPENROWSET will use SQL Server process account. If you use Windows Authentication, it should use your account.
July 17, 2015 at 1:49 pm
Thanks for the clarification. I'm using Windows authentication.
July 17, 2015 at 1:50 pm
After reading again your original post, I guess that the problem is effectively with [data$], that should be either a named table in Excel or the name of a spreadsheet (which by default are Sheet1, Sheet2, etc). The name needs to have the $ sign appended at the end.
July 17, 2015 at 2:11 pm
My data was on "Sheet 1" which I had renamed to "data". I changed it to "data$" and reran the query but got the same error. I also tried to name the table to "data$" but that was rejected by Excel as an invalid name.
July 17, 2015 at 2:14 pm
i've run into issues where the \users folders are extra protected;
i always use something not under mydocuments\mydownlaods\mydesktop, like C:\Data\SQLImportTrim.xls instead of C:\Users\home\Desktop\SQLImportTrim.xls',
can you try moving the file to a non-protected folder,andn see if that helps?
Lowell
July 17, 2015 at 2:24 pm
I moved it out of the Users folder to C:/. Same error.
July 17, 2015 at 2:31 pm
ok, this is tried and true code for an ACE driver example i just retested.
the Sheet in excel is named AK.
does modifying either of these s to be your document and sheet name work for you?
SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
UID=admin;
UserCommitSync=Yes;
Threads=3;
SafeTransactions=0;
ReadOnly=1;
PageTimeout=5;
MaxScanRows=8;
MaxBufferSize=2048;
FIL=excel 12.0;
DriverId=1046;
DefaultDir=C:\Data\BlockGroups_2010;
DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',
'SELECT * FROM [AK$]')
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\BlockGroups_2010\AKblockgroup.xls',
'SELECT * FROM [AK$]')
Lowell
July 17, 2015 at 2:47 pm
I am trying to import the data in a specific table. Not sure how to modify this in order to do that.
July 17, 2015 at 2:58 pm
MilesToGoBeforeISleep (7/17/2015)
My data was on "Sheet 1" which I had renamed to "data". I changed it to "data$" and reran the query but got the same error. I also tried to name the table to "data$" but that was rejected by Excel as an invalid name.
When trying to read an Excel sheet named "data", you need to reference it as "data$". The sheet itself should not have the $ suffix (though I must admit that I've never tried it)
From SSMS, run this:
EXECUTE xp_create_subdir 'C:\ExcelTest';
This will create a folder by the SQL Server agent - giving that account the proper permissions.
Next, copy your Excel spreadsheet to this folder.
Then change your code to:
INSERT INTO DailyRawData
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=NO;Database=C:\ExcelTest\SQLImportTrim.xls', [data$]);
If this doesn't work, remark out the INSERT (leave the select) and let us know if you get the same error.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 17, 2015 at 3:11 pm
Yes same error with and without the INSERT INTO language
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy