1

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 SysAdmn. I am wondering if the error is due to the last part of the query [data$] since this is what the error msg 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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MilesToGoBeforeISleep
  • 183
  • 1
  • 2
  • 17

1 Answers1

2

you don't use [data$] you use the name of the sheet, so the standard starting point is usually [Sheet1$] if you haven't renamed it.

ImCrimson
  • 146
  • 1
  • 1
  • 10