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!
A bitmap of the database diagram for question 2.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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
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
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.
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.
Business Accounts
Answer for Membership
by: RiteshShahPosted on 2009-04-15 at 09:38:36ID: 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.c
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.