Solved

Import Excel data into a SQL database

Posted on 2009-04-15
10
2,627 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tsql query 6 22
SQL server vNext 18 29
Create a datatable in vb.net dynamically 1 15
Excl VBA Find last column in disjointed range selection 18 22
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Viewers will learn how the fundamental information of how to create a table.
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.

803 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