Solved

Import Excel data into a SQL database

Posted on 2009-04-15
10
2,625 Views
Last Modified: 2012-05-06
This is a two part question.

At our company we keep all our inventory on an excel spread sheet.  It is getting very hard to work with, as our numbers are growing.  I have designed a relation database in SQL and would like to know how to populate the data from the excel spreadsheet into the database.  I am creating an application to interact with this database.  I am using the .net framework 3.5 and code mainly in VB.  The excel data is on one sheet, but the database has multiple tables.  This is the first database that I have designed, and although I am familiar with the concept of relational databases, I would like to know if my database is designed properly.  

1.  What is the best way to populate a multiple table database with data from an excel sheet?

2.  Is my database appropriately designed?  Am I making it too complicated or am I missing important concepts? (I have attached a .bmp of the database design.)

Thanks for any help you guys can give me!
DataBase-Diagram.bmp
0
Comment
Question by:luke_hippe
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 200 total points
ID: 24150032
you can create linked server of excel file in your database, once your do it, you will have full command over excel data and can copy it to your sql tables. here is one example of linked server.

http://www.sqlhub.com/2009/04/create-linked-server-with-excel-2007.html

as long as your structure concern, it seems ok. however, you can make it bit normalize but than you will have problem managing data as you are new to this concept.
0
 
LVL 5

Accepted Solution

by:
bprojoe earned 300 total points
ID: 24150079
I would create a windows form that opens the excel file(s).  From there you could set up a process that users can import the data into sql.  This way you wouldn't have to import the data manually using sql import wizard.
The tables look good to me.  I usually mark the primary keys with a PK and the foreign keys with an FK so they are easily identified.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24150080
once you are done with linked server, you can simply create SELECT...INTO query and copy data from excel to sql table
0
 

Expert Comment

by:cpsirvine
ID: 24150084
The easiest solution for me was to buy Navicat.  It lets you import pretty much anything into SQL and is super easy to use.  
0
 

Author Comment

by:luke_hippe
ID: 24150173
RiteshShah, could you explain the linked server a little more please?

bprojoe, are you suggesting that I handle the data with code and pluck it from excel and update it into sql?  

cpsirvine, I am running MS SQL server 2008, does Navicat work for that too? I look on the site and saw nothing in that vein.  

thanks for the speedy responses guys!

Luke
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24150378
have you read my article? you can set your excel as linked server,  once you set it, you can access all tables in excel in sql server with query

eg:

if you set link server name as "deptExcel" and you have data in sheet than you can fire one query like below.

select * into tempTable FROM [DeptExcel]...sheet1$

it will create one new table named tempTable in sql server. once you have table in sql server, you can transfer data to your sales table. like

insert into salesInfo select salesid, custname, currentstat, map from tempTable
0
 
LVL 5

Expert Comment

by:bprojoe
ID: 24151009
Yes, I am currently working on a project and we have a similar situation where people collect data in excel and need to import it into the database.  Since the user of the system needs to be able to import the data we just create processes that will import the excel spreadsheet table through our Windows Forms application.  You could probably use a combination of these ideas too.
0
 

Author Comment

by:luke_hippe
ID: 24151090
I have code that currently uses an excel spreadsheet as a datasource, so I can use this as coded solution to the problem, but I think I am going to try the linked server solution.  I will get back ASAP to let you know what worked.  Thanks guys!
0
 

Author Comment

by:luke_hippe
ID: 24226769
I am loading the execl files into a dataset, then loading the values into the dataset from the SQL database, then updating.  Maybe not the easiest, but the most straightforward method.  Thanks guys.
0
 

Author Closing Comment

by:luke_hippe
ID: 31570552
I had a hard time following the article RiteshShah posted for me, but I awarded points both for introducing me to the linked server concept and the feedback concerning my database structure.
Bprojoe's responses confirmed my inital idea.  I decided to quit wasting time looking for another method and just code the transfer.  His feedback on my database was also helpful.  My thanks to both of you for your input.  
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now