Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel file reconfiguration for import into database

Posted on 2011-09-25
4
Medium Priority
?
193 Views
Last Modified: 2012-05-12
Hi,

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

MSL
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

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.

Regards,

Ken
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

Open in new window

ExpertsExchangeHelp.xls
0
Comment
Question by:kenuk110
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 36596776
This is what your company uses.
http://exceldatareader.codeplex.com/
With this code you can design a small app. to read any excel file with mutli-sheets (xls or xlsx)
with a dataset you can read each column or row of data and move it into your server with no problems.
0
 

Author Comment

by:kenuk110
ID: 36597921
Hi,

I have read the link you provided, it's a little beyond me to be honest as I'm no Excel expert! I'll keep reading it though and see if I can muddle through.

Thanks for the link though, I really appreciate it.

Regards,

Ken
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 36598326
build a simple front end in access instead with ODBC or using a webpage - either way you can put validation on the fields?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question