SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help - Import data from Excel to SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

santana
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 11/24/2009 :  05:59:47  Show Profile  Reply with Quote
Hi,
I am using the Microsoft SQL Server Management Studio Express and I need to import the data from Excel file to table (SQL Server 2005).
I don't know how I am doing this, I don't have any other tool.

Could you help me, please?

Regards,

Edited by - santana on 11/24/2009 08:36:19

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 11/24/2009 :  08:41:04  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
Hi

SQL Server linked servers
Example

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]


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...
Go to Top of Page

santana
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 11/24/2009 :  10:03:06  Show Profile  Reply with Quote
Hi,

I tried those, but I received error:

- SELECT * INTO [finance].[dbo].[fin_history] FROM 'C:\Historical.xlsx'['2007-2009 Total']
error: Incorrect syntax near 'C:\Historical.xlsx'.

-SELECT * INTO [finance].[dbo].[fin_history] FROM OPENQUERY('C:\Historical.xlsx',
'SELECT * FROM [2007-2009 Total$]')
error: Incorrect syntax near 'C:\Historical.xlsx'.

What is [Customers$] in your example?

My excel file is in the same server to the sql server. But I didnt have Excel tool in the same server, only in
another one.

Could you help me again, please?

path/file - C:\Historical.xlsx
only one sheet - ['2007-2009 Total']
table - [finance].[dbo].[fin_history]

Thank you!

Regards,
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 11/24/2009 :  12:19:52  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
quote:
Originally posted by santana

Hi,

I tried those, but I received error:

- SELECT * INTO [finance].[dbo].[fin_history] FROM 'C:\Historical.xlsx'['2007-2009 Total']
error: Incorrect syntax near 'C:\Historical.xlsx'.

-SELECT * INTO [finance].[dbo].[fin_history] FROM OPENQUERY('C:\Historical.xlsx',
'SELECT * FROM [2007-2009 Total$]')
error: Incorrect syntax near 'C:\Historical.xlsx'.

What is [Customers$] in your example?

My excel file is in the same server to the sql server. But I didnt have Excel tool in the same server, only in
another one.

Could you help me again, please?

path/file - C:\Historical.xlsx
only one sheet - ['2007-2009 Total']
table - [finance].[dbo].[fin_history]

Thank you!

Regards,



[Customers$] shee name.

i think its .xls file




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

Edited by - rajdaksha on 11/24/2009 12:22:19
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/25/2009 :  01:20:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also try
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

santana
Yak Posting Veteran

United Kingdom
72 Posts

Posted - 11/25/2009 :  05:06:47  Show Profile  Reply with Quote
Hi,

thanks for all replays.

I received that msg/error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I don't know how can I fix this!

Regards,







Edited by - santana on 11/25/2009 07:13:49
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/25/2009 :  07:30:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Read all the replies posted in the link I have given

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

behrman
Yak Posting Veteran

76 Posts

Posted - 11/28/2009 :  07:21:52  Show Profile  Visit behrman's Homepage  Reply with Quote
Hi.

SQL Server 2005 Management Studio provides the ability to copy and paste columns directly from Excel to SQL Server when the table and column names match up. Let's walk through a simple example. Assume you have a database called 'Test' with a table called 'MyTable' consisting of two columns (MyID, MyDesc). In addition, you have an Excel worksheet where you want to load data directly from Excel to SQL Server. Let's walk through setting up and testing that scenario.
For detailed operations, please refer to http://www.mssqltips.com/tip.asp?tip=1430.

Regards,
behrman.

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

Edited by - behrman on 11/28/2009 07:22:46
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/28/2009 :  07:26:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by behrman

Hi.

SQL Server 2005 Management Studio provides the ability to copy and paste columns directly from Excel to SQL Server when the table and column names match up. Let's walk through a simple example. Assume you have a database called 'Test' with a table called 'MyTable' consisting of two columns (MyID, MyDesc). In addition, you have an Excel worksheet where you want to load data directly from Excel to SQL Server. Let's walk through setting up and testing that scenario.
For detailed operations, please refer to http://www.mssqltips.com/tip.asp?tip=1430.

Regards,
behrman.

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


I think OP may want it to automate

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000