I have an excel sheet that has Four Columns
SaleID, SaleDate,PersonID, ProductID, SaleQuantity,UnitRate
I want to import this data in one to many relation table such that each person sale should be recorded saparately. My tables are as follows
Sale Master
SaleID,SaleDate,PersonID
SaleDetail
SaleID,productID,SaleQuantity,UnitRate
I am trying to follow the instruction from here. I have tried the following query to get started
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=d:\tmp.xlsx', 'Select * from [aa$]')
But it is giving me following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Office Access database engine could not find the object 'aa$'. Make sure the object exists and that you spell its name and the path name correctly.".
Is there any other better way to do that