I have a spreadsheet that we use to as a template for input in to our SQL database. The way the spreadsheet is designed allows for easier input by a certain department but creates a nightmare for us in the IT department to bring it in to the database.
I have attached a sample excel file, this is a real one but with most of the data taken out so it's quicker to upload to here. I have also added the table structure for the table the data from the excel file goes in too.
These are the three sheets in the file
This is a list of products, in reality the items that appear on the next two sheets should only equal the items listed in here
Listing and Schedule per Class
This is the sheet the users fill in. This has to be 'transposed' in to the ScheduleImport sheet. You can see that the colums satVMF, sunVMF, monVMF etc have the word VMF in the cell. These are our services but we can't bring the data in like this, it needs to look like ScheduleImport
This is the sheet we use to bring all the data from Listing and Schedule per Class sheet. Each day and serviceType has to be listed for each different day and ech different serviceType.
This is what takes the time as I have to copy and paste a lot of times, sometimes we have 30-40 thousand rows to complete and it simply takes too long!
Any help anyone can give will be much appreciated.
CREATE TABLE [dbo].[scheduleN](
[pk] [bigint] IDENTITY(1,1) NOT NULL,
[upcCode] [nvarchar](30) NOT NULL,
[countryCode] [nvarchar](3) NOT NULL,
[cityCode] [nvarchar](2) NULL,
[classCode] [nvarchar](1) NOT NULL,
[vendorCode] [nvarchar](3) NULL,
[storeType] [nvarchar](1) NULL,
[mslPromo] [nvarchar](1) NULL,
[serviceDay] [nvarchar](9) NULL,
[serviceType] [nvarchar](5) NULL,
[live] [nvarchar](1) NULL,
CONSTRAINT [PK_scheduleN] PRIMARY KEY CLUSTERED