loading excel sheets into sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

loading excel sheets into sql

Author

Topic

inform2tuhin
Starting Member

2 Posts

Posted - 2012-06-15 : 03:19:59

i m having three excel files and each file contains 4 sheets of different month names which contains some data of that particular month. I want to load the current month data contained in different files into one table in SQL Server. How can i do it with the help of SSIS. Would you

please tell the solution. Thanks in Advance.

Satendra Singh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-15 : 05:25:30

Are you restricted to using ssis only?
Try adding a excel connection and accessing the spreadsheet and see how you get on.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inform2tuhin
Starting Member

2 Posts

Posted - 2012-06-15 : 06:07:20

Thanks a lot Nigelrivett...actually m having three excel files excel1,excel2,excel3.

In Excel1 file there are three sheets:-

April May June
Jeevan Rajesh Prince
kamal Mohit kashish

another excel files also contains the similar kinds of sheets and the corresponding data. I want to load the Current Month(June) data into the SQL Server database. I dont know what script i need to write in the script task. If it is possible then please tell me the steps by steps solution to rectify this problem. Actually i m new to ssis and facing this kind of issues. Please guide me, m very thankful to you.

Thanks
Satendra Singh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-15 : 06:52:47

Load business intelligence studio.
Create a new SSIS project
Right click on the connections pane (bottom of window)
New connection - Excel. Select yoour excel spreadsheet (make sure it is closed)
Do the same for an ole db connection and set to your server/database
In big window - add a new dataflow
From toolbox drag excel source connection - properties - set to excel connection and select first worksheet
Drag ole db destination - set to the sql server connection and select the table yoou want to load into (can also create a new one)
Set the mappings between source and destination and run it.

Now do the same for the other worksheets.

You will probably want an sql task to delete a staging table before this runs. That is in the control flow window.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-15 : 12:20:26

I don't know you're level of Powershell experience - but Powershell has very good handling on Excel spreadsheets .
Good options to manipulate data - before SQL Server import

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources