luke_hippe
asked on
Import Excel data into a SQL database
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RiteshShah
once you are done with linked server, you can simply create SELECT...INTO query and copy data from excel to sql table
The easiest solution for me was to buy Navicat. It lets you import pretty much anything into SQL and is super easy to use.
ASKER
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
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
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.
ASKER
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!
ASKER
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.
ASKER
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.
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.