0

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

enter image description here

Tassadaque
  • 7,831
  • 12
  • 51
  • 86
  • Firstly, can you not use SSIS? Secondly, are you 100% sure the worksheet name is aa? And the file name is tmp.xlsx in d:\ ? Is the Excel file open while you're trying this query? – Sean Jul 10 '12 at 07:46
  • Is the sheet really called "aa" ? – stb Jul 10 '12 at 07:47
  • @stb Yes I renamed the sheet as it was not working – Tassadaque Jul 10 '12 at 08:15
  • @SeanW I can use SSIS. If you can guide in this regard – Tassadaque Jul 10 '12 at 08:16
  • try designing your package by using a data flow task. Inside the task, use an excel source, maybe a data conversion task and a destination task to store your data. – stb Jul 10 '12 at 08:27

1 Answers1

1

Okay, I'm not sure if I did this as you wanted. Since I saw that I could use SSIS, I used SSIS. First I created a sample Excel file. You said it has four columns but I see six columns:

SaleID, SaleDate,PersonID, ProductID, SaleQuantity,UnitRate

And on my test DB I created two tables to import data to:

CREATE TABLE SalesMaster (
SalesID INT NOT NULL,
SalesDate DATE,
PersonID INT
    CONSTRAINT PK_SalesMaster_SalesID
        PRIMARY KEY (SalesID))


CREATE TABLE SalesDetail (
ProductID INT NOT NULL,
SalesQuantity INT,
UnitRate MONEY,
SalesID INT
    CONSTRAINT PK_SalesDetail_ProductID
        PRIMARY KEY (ProductID),
    CONSTRAINT FK_SalesDetail_SalesMaster_SalesID
        FOREIGN KEY (SalesID)
            REFERENCES SalesMaster(SalesID))

Then I created a SSIS package to the task. First on the Control Flow, I used two Data Flow tasks. The first one is for populating the SalesMaster table and the second one is for populating the SalesDetail table.

Each data flow task will have the Excel Source adaptor and OLE DB Destination adaptor. When you configure the Excel source, make sure that you map the columns in the Excel file correctly. Then run the package and it should do the job.

Hope this was what you are looking for. Thanks!

Infravision
  • 109
  • 6