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:
[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:
COST OF FOOD
COST OF UNITS
ADJ FOOD COST
NUMBER OF UNITS
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. :-)