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
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/12/2007 :  01:55:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
I also want to thank madhivanan for all the posts - they have been very helpful.
>>

Thanks. You are welcome

Madhivanan

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

Di73
Starting Member

USA
2 Posts

Posted - 11/23/2007 :  13:27:29  Show Profile  Click to see Di73's MSN Messenger address  Reply with Quote
i have a question to you...
Shall I?

Can i export from excel2007 to sql2000?

is it the same code?

you can answer me to dianap0@hotmail.com

thank you very much!
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/23/2007 :  13:32:15  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
No, you cannot export from 2007 to 2000..there are some backward compatibility issues.



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

Di73
Starting Member

USA
2 Posts

Posted - 11/23/2007 :  13:32:28  Show Profile  Click to see Di73's MSN Messenger address  Reply with Quote
quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail








can i do this with excel 2007??
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/23/2007 :  13:44:51  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
You would have to change the reference to Excel 12.0 instead of Excel 8.0



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/26/2007 :  01:17:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Di73

i have a question to you...
Shall I?

Can i export from excel2007 to sql2000?

is it the same code?

you can answer me to dianap0@hotmail.com

thank you very much!


Yes. As said, change the connection and see

Madhivanan

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

gabulish
Starting Member

4 Posts

Posted - 11/27/2007 :  14:14:50  Show Profile  Reply with Quote
I am really struggling getting this code to work. I would like to import excel spreadsheet data to a SQL Server table. It doesn't get past the part where I assign the OPENROWSET to the statement variable. Below is my code:

Dim objCN
Dim statement

Set objCN=CreateObject("ADODB.Connection")

connectionstring="Data Source=Servername;Initial Catalog=dbName;User ID=ID;Password=PWD;Provider=Microsoft.Jet.OLEDB.4.0;"

objCN.Open connectionstring
if(err.number<>0) then
WScript.Echo "Connection Failed"
else

statement = "Insert into DB_Table_Name Select column_name FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0'," & _
"'Extended Properties=Excel 8.0;Data Source=C:\Test\Test.xls;HDR=YES'," & _
"'SELECT column_name FROM [WrkShtname$]')"

objCN.Execute(statement)

end if
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/28/2007 :  01:04:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
print statement and see what it prints. Also make sure that the file exists in the server and not at the client. Otherwise you need to use universal path like \\system\c$\....

Madhivanan

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

gabulish
Starting Member

4 Posts

Posted - 11/28/2007 :  16:25:44  Show Profile  Reply with Quote
OK, I added the universal path but still no luck.

I just realized when I use "Microsoft.Jet.OLEDB.4.0" as the Provider in the connection string I can't connect? If I use the "SQLOLEDB" as the provider it works. Which connect string should I be using? Also, I have a few other general questions?

1) Does the SQLOLEDB driver need to be installed on the Server?
2) Does Excel need to be installed on the the SQL server?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/29/2007 :  01:09:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I would say "yes" to both of the questions

Madhivanan

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

gabulish
Starting Member

4 Posts

Posted - 11/29/2007 :  09:11:20  Show Profile  Reply with Quote
What should my provider be defined as: Microsoft.Jet.OLEDB.4.0 or SQLOLEDB?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/30/2007 :  01:40:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by gabulish

What should my provider be defined as: Microsoft.Jet.OLEDB.4.0 or SQLOLEDB?


Use SQLOLEDB as it would give you clear error message if there is any error

Madhivanan

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

gabulish
Starting Member

4 Posts

Posted - 12/03/2007 :  11:45:09  Show Profile  Reply with Quote
The initial connection succeeds but when the insert attempts to execute I get the below error message. One thing to note: I do not have the SQLOLEDB driver installed but in connects anyway. Any thoughts/assistance would be great. I'm stumped!!

Error: OLE DB Error trace [Non-interface error: Provider Not registered.]
Code: 80040E14
Source: Microsoft OLE DB Provider for SQL Server


------------------------------------------------------
My vbScript:

Dim objCN
Dim statement

Set objCN=CreateObject("ADODB.Connection")
connectionstring="Provider=SQLOLEDB;User ID=ID;Password=PASS;Initial Catalog=DBNAME;Data Source=SVRNAME"

objCN.Open connectionstring

if(err.number<>0) then
WScript.Echo "Connection Failed"

else

wscript.echo "Connection Succeeded"


statement = "Insert into sql_svr_db_table_name Select column_id FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0'," & _
"'Extended Properties=Excel 11.0;Data Source=\\pathtoexcelfile\myexcelfile.xls;HDR=YES'," & _
"'SELECT column_id FROM [wrk_sht_name$]')"


objCN.Execute(statement)

wscript.echo "Insert Complete"

end if
-------------------------------------------
Versions and Specs:

Excel 2003 11.5612
SQL Server 2000 DB on Windows Server 2003 - Standard Edition - SP1
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/06/2007 :  07:27:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try using Microsoft.Jet.OLEDB.8.0 provider for EXCEL 2003

Madhivanan

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

vishu_av
Yak Posting Veteran

69 Posts

Posted - 12/20/2007 :  01:24:53  Show Profile  Reply with Quote
Hi Madhivanan,

Can i generate an excel sheet using SQL server 2000?

Thanks in advance!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/20/2007 :  03:03:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by vishu_av

Hi Madhivanan,

Can i generate an excel sheet using SQL server 2000?

Thanks in advance!!


Did you mean creating a new EXCEL file?

Madhivanan

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

natzol
Starting Member

USA
2 Posts

Posted - 12/20/2007 :  11:07:43  Show Profile  Reply with Quote
Does anyone can help? The data is arriving in the Excel file but... the numbers are being stored on the sheet as text. Unfortunately the formatting of the TEMPLATE file does not work!

Any other ideas???


Have the same problem while trying to export from SQL server to the
Excel. See simplified sample below.


DECLARE @fn varchar(500)
Select @fileFullPath = 'C:\SQL\Test.xls'


-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fileFullPath + ';HDR=YES'


-- Executing the OPENROWSET Command for copying the select contents to
Excel sheet # 2
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString
+ ''',''SELECT Name,Total FROM [Sheet1$]'') ' +
'SELECT NAME, Total from Orders'


Go to Top of Page

frtslgn
Starting Member

3 Posts

Posted - 12/24/2007 :  07:17:40  Show Profile  Reply with Quote
how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
A jan ---
B jim ---
C jon ---
D jen ---
E jun ---
F --- MRC
G --- BMW
H --- FRD
I --- RVR


but i want

1 2
--- ---
A jan mrc
B jim bmw
C jon frd
D jen rvr
e jun

Edited by - frtslgn on 01/07/2008 03:39:50
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/24/2007 :  07:26:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by frtslgn

how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
A jan
B jim
C jon
D jen
E jun
F MRC
G BMW
H FRD
I RVR


but i want

1 2
--- ---
A jan mrc
B jim bmw
C jon frd
D jen rvr
e jun


Are you using SQL Server 2000 or 2005?
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/24/2007 :  07:29:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by natzol

Does anyone can help? The data is arriving in the Excel file but... the numbers are being stored on the sheet as text. Unfortunately the formatting of the TEMPLATE file does not work!

Any other ideas???


Have the same problem while trying to export from SQL server to the
Excel. See simplified sample below.


DECLARE @fn varchar(500)
Select @fileFullPath = 'C:\SQL\Test.xls'


-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fileFullPath + ';HDR=YES'


-- Executing the OPENROWSET Command for copying the select contents to
Excel sheet # 2
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString
+ ''',''SELECT Name,Total FROM [Sheet1$]'') ' +
'SELECT NAME, Total from Orders'





Did you try formatting that cell to number?
How did you know that was stored in text format?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 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.14 seconds. Powered By: Snitz Forums 2000