Import Excel Data to SQL Table

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

SSIS and Import/Export (2005)

Import Excel Data to SQL Table

Author

Topic

isihalin
Starting Member

6 Posts

Posted - 2009-12-21 : 04:36:38

Hi,
I have the Data in Excel sheet. Now that i want to load the excel data into my SQL table . How do i do them,i am not that good in Sql .

Can some one help me on this.

Thanks
Diwakar.

Thanks In Advance.
isihalin

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-21 : 04:44:17

Hi

You can use any one of the following.

1.SQL Server Data Transformation Services (DTS)
2.Microsoft SQL Server 2005 Integration Services (SSIS)
3.SQL Server linked servers
4.SQL Server distributed queries

Use a Linked Server

Example


SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')

SQL Server distributed queries

Example


SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

-------------------------
R...

isihalin
Starting Member

6 Posts

Posted - 2009-12-21 : 05:35:30

Hi

Thanks for the reply. In the above select statements 'XLImport3' is the table name in SQL ? also how do i create [ams_employee$] if my table name is ams_employee in sql

Thanks In Advance.
isihalin

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-21 : 05:41:27


SELECT * INTO ams_employee FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

-------------------------
R...

behrman
Yak Posting Veteran

76 Posts

Posted - 2009-12-24 : 09:59:32

You can create a dataflow task in an SSIS package with an Excel file source and do any manipulation you need to and pump it into your SQL table as well.
Regards,
behrman.

RAQ Report: Web-based Excel-like Java reporting tool

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 01:08:56

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources