troubleshooting Question

Import excel spreadsheet into SQL

Avatar of dij8
dij8Flag for New Zealand asked on
Microsoft SQL Server
11 Comments1 Solution982 ViewsLast Modified:
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. :-)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros