asked on

Import excel spreadsheet into SQL

I have an Excel spreadsheet with four blocks of a weeks worth of data. Each block has a week ending date. I can grab each block and create a new spreadsheet however I want. I can set the date and week number (the two fixed values for each weeks worth of entries).

I have two SQL tables. One is a table of stores with an ID (FranchiseDetailsId (int) IDENTITY) as an autoincrementing number which is the primary key. This table also, among other things, has a store code (StoreCode (varchar(50)) which is unique for each store. The other table is the one that needs the data entered into. This table is made up like so:
TABLE [KPIDetails]
      [KPIDetailsId] [int] IDENTITY (1, 1) NOT NULL ,
      [FranchiseDetailsId] [int] NULL ,
      [WeekEnding] [datetime] NULL ,
      [WeekNumber] [int] NULL ,
      [NetSales] [decimal](19, 2) NULL ,
      [CostOfFood] [decimal](19, 1) NULL ,
      [CostOfUnits] [decimal](19, 1) NULL ,
      [AdjustedFoodCost] [decimal](19, 1) NULL ,
      [Labour] [decimal](18, 1) NULL ,
      [Product] [decimal](19, 2) NULL ,
      [Factor] [decimal](19, 2) NULL ,
      [Ticket] [decimal](19, 2) NULL ,
      [Units] [bigint] NULL ,
      [CustomerCount] [bigint] NULL ,
      [RentPercent] [decimal](18, 1) NULL ,
      [RentDollars] [decimal](19, 2) NULL ,

My Excel spreadsheet has all the necessary details to enter into this table but there are some limitations. Not all stores are yet in the database so I need to check that first. I need to get the FranchiseDetailsId from the FranchiseDetails table by searching for the StoreCode. If the FranchiseDetails entry does not exist then ignore that line in the Excel spreadsheet. For each week the week number is fixed and the wee kending data is fixed (obviously). The Rent % is calculated by Net Sales / Rent $ (*100 to get the percentage I think).

My spreadsheet has the following columns (these are not named in any way but if I need to do that then I can, although I may need help with that as well, I am not familiar with naming areas in Excel) in the order as shown:
STORE NUMBER
STORE NAME
NET SALES
COST OF FOOD
COST OF UNITS
ADJ FOOD COST
LABOR %
PROD
FACTOR $
TICKET $
NUMBER OF UNITS
CUSTOMER COUNT
RENT %
RENT $

I need a way of importing this. I had a look at the import data function but I didn't understand what I needed to do. Especially when it came to working out the FranchiseDetailsId. I am guessing there is a way I can do this as a SP or using Query Analyzer.

I need the complete syntax for achieving this though. That would include opening the spreadsheet, setting the week ending date (formatted in such a manner as NZ format (dd/mm/yyyy) or US (mm/dd/yyyy) is irrelevant (is yyyy/mm/dd universal?)) and week number, getting the FranchiseDetailsId from the StoreCode, and inserting the new KPIDetails entry for each row in the spreadsheet. Remembering of course that the StoreCode may not exist and if so then that row in the spreadsheet can be ignored. The rent % is already calculated in the spreadsheet or I can do this as part of the import function, either way is fine as long as it works. In the spreadsheet the table cell is formatted as a percentage, in the database it is a decimal.

I hope I have given enough information. I am guessing mostly it is a matter of matching the cells up with the table. Opening the spreadsheet and finding the FranchiseDetailsId may be a little more complicated. I have no idea. Whatever means is necessary is fine, SP, Import Wizard, Query Analyzer, anything else that might work. Whatever it is though I need to be able to duplicate it as some form of method that I can take with me to the office.

This is extremely urgent. It is almost midnight now and I have to be at work at 8am with a working solution. I would like some sleep between now and then too. :-)

Aneesh

http://support.microsoft.com/Default.aspx?kbid=321686

Aneesh

I think it is better to Import the data to an intermmediate table using import export wizard; or OPENQUERY.. (hope the above link will help you )
Then you need to check for the StoreID; ==> delete from yourIntermmediate table where StoreNumber NOT IN (SELECT StoreNumber from FranchiseDetails ..)

dij8

ASKER

I don't really understand. I have written the below knowing it will need adjustments according to my best understanding from your suggested link. Adjustments like how do I specify the first column in the spreadsheet if that is what I am getting the FranchiseDetailsId from?

INSERT INTO KPIDetails
(FranchiseDetailsId, WeekEnding, WeekNumber, NetSales, CostOfFood, CostOfUnits, AdjustedFoodCost, Labour, Product, Factor, Ticket, Units, CustomerCount, RentPercent, RentDollars)
VALUES (SELECT FranchiseDetailsId FROM FranchiseDetails, SELECT Column[0] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')), '2006/01/31', 4, SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]'))

The other alternative you suggested is to make a temporary table. And then importing the temporary table into the existing table. How do I do that. Again, I really need syntax on this. Working it out isn't my best of qualities.

dij8

ASKER

I have updated my SQL in Query Analyzer so I don't get any errors now. But it doesn't insert any entries. I am guessing my WHERE clause is wrong. Can you amend this?

INSERT INTO KPIDetails
(FranchiseDetailsId, WeekEnding, WeekNumber, NetSales, CostOfFood, CostOfUnits, AdjustedFoodCost, Labour, Product, Factor, Ticket, Units, CustomerCount, RentPercent, RentDollars
) VALUES (
            (SELECT FranchiseDetailsId
                  FROM FranchiseDetails
                  WHERE (SELECT A
                        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
            ),
            '2006/01/31',
            4,
            (SELECT * FROM
                  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
      )
WHERE (
      SELECT FranchiseDetailsId
            FROM FranchiseDetails
            WHERE (SELECT A
                  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
      )
      IS NOT NULL

dij8

ASKER

Now I get errors. I don't know what is happening. I did miss in my WHERE clause the StoreCode part. Updated as follows returns fours errors:
INSERT INTO KPIDetails
(FranchiseDetailsId, WeekEnding, WeekNumber, NetSales, CostOfFood, CostOfUnits, AdjustedFoodCost, Labour, Product, Factor, Ticket, Units, CustomerCount, RentPercent, RentDollars
) VALUES (
(SELECT FranchiseDetailsId
FROM FranchiseDetails
WHERE StoreCode = (SELECT A
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
),
'2006/01/31',
4,
(SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
)
WHERE (
SELECT FranchiseDetailsId
FROM FranchiseDetails
WHERE StoreCode = (SELECT A
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\DiJ8LtdServer\Users\Peter\Desktop\Book1.xls'), 'SELECT * FROM [Sheet1$]')
)
IS NOT NULL

Server: Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near ')'.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dij8

ASKER

I still can't get this to work. Below is the query I have:

CREATE TABLE [tempRecSet] (
      [StoreCode] [varchar](50) NULL ,
      [StoreName] [varchar](50) NULL ,
      [NetSales] [decimal](19, 2) NULL ,
      [CostOfFood] [decimal](19, 1) NULL ,
      [CostOfUnits] [decimal](19, 1) NULL ,
      [AdjustedFoodCost] [decimal](19, 1) NULL ,
      [Labour] [decimal](18, 1) NULL ,
      [Product] [decimal](19, 2) NULL ,
      [Factor] [decimal](19, 2) NULL ,
      [Ticket] [decimal](19, 2) NULL ,
      [Units] [bigint] NULL ,
      [CustomerCount] [bigint] NULL ,
      [RentPercent] [decimal](18, 1) NULL ,
      [RentDollars] [decimal](19, 2) NULL
)
GO

INSERT INTO tempRecSet
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c:\Book1.xls', 'Select * from [Sheet1$]')

DELETE FROM tempRecSet
WHERE StoreCode NOT IN (SELECT StoreCode FROM FranchiseDetails)

SELECT * FROM tempRecSet

DROP TABLE tempRecSet

Currently it is returning the error "The Microsoft Jet database engine could not find the object 'Sheet1$'."

I created a new spreadsheet so by default the first sheet is called "Sheet1".

dij8

ASKER

I figured out my issue was that Ithe file was local but the SQL server is not. The file needs to be stored on the same server as the SQL server.

After some playing around I am still getting an error.Not a helpful one at all. I have named each column and created a row with the content in each cell being the name of the field I want to retrieve. Before I did that it complained it couldn't find any columns. Now all I get from a simple query is:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=e:\Book1.xls;HDR=YES;', 'Select * from [KPIDetails$]')

Returns:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

This of course means I can't test the rest of it as this is kind of the first step. I have no idea what might be going on here. Could it be some kind of permissions thing. Althgouh I think I have all the right permissions.

dij8

ASKER

Next issue resolved. I think it was a permissions thing. I moved the file into the same folder as the database:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=e:\Database\MSSQL\Data\Book1.xls;HDR=YES;', 'Select * from [KPIDetails$]')

Now I get the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [KPIDetails$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [KPIDetails$]'].

How am I supposed to format the Excel file?

Aneesh

What is the name of the sheet ? is this [KPIDetails$]... otherwise replace this part with the name of the sheet. Also make sure that it is not open...

dij8

ASKER

I don't know what the issue was in the end but I have finally sorted it.

I created the temp table in my database. I then ran the Import Wizard to populate that table from the spreadsheet. Whatever the issue with my query, the wizard didn't have the same problem (although, now you mention it and I think about it, the file may have been open). Once the temporary table was populated I could delete the irrelevant entries, populate the right table with the data nad the FranchiseDetailsId, then empty the temp table. By saving the Import and the query I could go through each week quite easily.

After the import the query I ran was as follows:
--Remove KPI details for stores not yet entered in database.
DELETE FROM tmpKPIDetails
WHERE tmpKPIDetails.StoreCode NOT IN (SELECT FranchiseDetails.StoreCode FROM FranchiseDetails)

--Declare variables for use while inputing data from temporary table
DECLARE @FranchiseDetailsId [int], @WeekEnding [datetime], @WeekNumber [int], @NetSales [decimal](19, 4)
DECLARE @CostOfFood [decimal], @CostOfUnits [decimal](19, 0), @AdjustedFoodCost [decimal](19, 0)
DECLARE @Labour [decimal](18, 3), @Product [decimal](19, 0), @Factor [decimal](19, 4), @Ticket [decimal](19, 4)
DECLARE @Units [bigint], @CustomerCount [bigint], @RentPercent [decimal](18, 3), @RentDollars [decimal](19, 4)
DECLARE @StoreCode [varchar](50), @StoreName [varchar](50)

--set week and week number according to data from spreadsheet.
SET @WeekEnding = '2006/01/31'
SET @WeekNumber = 5

--Input data.
DECLARE curTempRecData CURSOR
FOR SELECT * FROM tmpKPIDetails

OPEN curTempRecData

FETCH NEXT FROM curTempRecData
INTO @StoreCode, @StoreName, @NetSales, @CostOfFood, @CostOfUnits, @AdjustedFoodCost, @Labour, @Product,
       @Factor, @Ticket, @Units, @CustomerCount, @RentPercent, @RentDollars, @FranchiseDetailsId

WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT 1 FROM FranchiseDetails WHERE StoreCode = @StoreCode )
BEGIN

SELECT @FranchiseDetailsId = FranchiseDetailsId
FROM FranchiseDetails WHERE StoreCode = @StoreCode

INSERT INTO KPIDetails(
      [FranchiseDetailsId] ,
      [WeekEnding] ,
      [WeekNumber] ,
      [NetSales] ,
      [CostOfFood] ,
      [CostOfUnits] ,
      [AdjustedFoodCost] ,
      [Labour] ,
      [Product] ,
      [Factor] ,
      [Ticket] ,
      [Units] ,
      [CustomerCount] ,
      [RentPercent] ,
      [RentDollars]
)VALUES(
      @FranchiseDetailsId, @WeekEnding, @WeekNumber, @NetSales,
      @CostOfFood, @CostOfUnits, @AdjustedFoodCost,
      @Labour, @Product, @Factor, @Ticket, @Units, @CustomerCount,
      @RentPercent, @RentDollars
)

END
FETCH NEXT FROM curTempRecData
INTO @StoreCode, @StoreName, @NetSales, @CostOfFood, @CostOfUnits, @AdjustedFoodCost, @Labour, @Product,
       @Factor, @Ticket, @Units, @CustomerCount, @RentPercent, @RentDollars, @FranchiseDetailsId

END
CLOSE curTempRecData
DEALLOCATE curTempRecData

--Empty temporary table
DELETE FROM tmpKPIDetails

Thank you very much aneeshattingal. Your help has been invaluable.