import a single spreadsheet into several SQL Server tables

Question

I need to import a single spreadsheet that has several columns into tables SQL Server 2005. There are many steps I need to take, i.e., create a new database on SQL server and create many tables' structures in this database before importing.

I will need to break down the spreadsheet to store data into appropriate fields of appropriate tables. One of my keys is StudentID. How can I do that? Do you have any information or link to show me how to design a database in this situation.

Thank you in advance for your help. I appreciate it.
 

Answer 1

You can use SSIS to do this import for you.

But first you have to Design your database and tables depends on your business requirement. Any Sample given to you on how to design a database may not met your business requirement.

If you want you can post your spreadsheet column names in here and also your desired table structure and people could give you some ideas on how to implement it.

 Any possibilitiy is to use OPENROWSET() and select your data from Excel and distribute it between appropriate tables in SQL Server.

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

Hi tim,

The basic process of transfering data is known as ETL or extract transform and load

basically you do not copy the data directly into the "destination database"

here is what you can do:

1. Copy the excel data into the "Imports database" (this is known as the extract process)

2.  between the "imports database" and "destination database" is the "staging database"

When the data reach the "staging database" it is already clean and confirming to the "destination database" 

(This procss is known as transform)

3.  Once you have the data is staged and cleaned you have to copy that to the destination table (this is now know as the load)

Now to answer your question.  You should have the "destination database"  designed manually.

regards,

joey

 

 

---

http://joeydj.com

Answer 3

Thank you, Arbi and Joey, for giving me an idea about this topic. Here is my spreadsheet column name:
CustomerID (1)/CustName(1)/CustAddress(1)/ContractStartDate(2)/ContractEndDate(2)/EstimateRate(3)/ActualRate(3)/Weekschedule(2)/BuildingCode(4)/Unitsupport(5)/Status(6).

Those numbers next to the field's names are tables that I plan to put the fields in SQL Server.  My question is how can I "break" these columns into my fields in the SQL server with the primary key is the CustomerID.
Thank you for your help!

 

Answer 4

Tim,

Please check following sample. I have a test Excel file which has four columns in it and I will read from Excel file and then spread between different tables.

EXEC sp_addlinkedserver EXCEL,
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'C:\TestExcel\Book1.xls', 
   NULL,
   'Excel 5.0;'
Go

Select * From EXCEL...Sheet1$

Create Table Table1 (Col1 Int, Col2 Varchar(10))
Create Table Table2 (Col1 Int, Col3 DateTime) 
Create Table Table3 (Col1 Int, Col2 Varchar(10), Col4 SmallMoney)

Insert Into TestDB.dbo.Table1 
Select Col1, Col2 
From EXCEL...Sheet1$


Insert Into TestDB.dbo.Table2
Select Col1, Col3 
From EXCEL...Sheet1$

Insert Into TestDB.dbo.Table3
Select Col1, Col2, Col4 
From EXCEL...Sheet1$


Drop Table TestDB.dbo.Table1
Drop Table TestDB.dbo.Table2 
Drop Table TestDB.dbo.Table3

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 5

Thanks a lot, Arbi. I appreciate it. Just a small dumb question. Why do you use drop table at the end? Doesn't it seem to delete those tables you just create them?

Answer 6

I drop them because they were as TEST table. In real world you do not need to drop them, instead of Table1, Table2, Table3 you have to use your real tables and column.

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.