Solved

Import Excel data into a SQL database

Posted on 2009-04-15
10
2,632 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
[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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

630 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