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

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 07/06/2006 :  04:27:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

But Excel has a latency problem with this. You have to enter the cell in Excel and press enter. I think this has to do with OpenRowSet exports the formula as text.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/06/2006 04:37:42
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/07/2006 :  01:17:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Yes it seems though the cell is formated as Number the data are of varchar type until the cell is focussed and Press Enter

Madhivanan

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

PrabhaNB
Starting Member

1 Posts

Posted - 07/12/2006 :  00:53:42  Show Profile  Reply with Quote
Hi

The query to export data from sqlserver to excel results in an error.

It requires the use of a linked server for the jet provider.

Is there any other way to export data to excel .net.


Thanks

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/12/2006 :  02:53:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Read all the replies in this topic. You may have answer
Or Make use of Linked Server. Read it in sql server help file

Madhivanan

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

jronneba
Starting Member

USA
2 Posts

Posted - 07/12/2006 :  15:40:08  Show Profile  Reply with Quote
Hi Madhivanan,

I'm fairly new to SQL however I was able to get this working with the example that you provided when testing.xls exists. However in my scenario I need to create a new, dynamic (unique) filename each time the Stored Procedure is run.

I would like the filename to be: UniqueNum+"_"+EmployeeName+"_"+DateStamp+".xls"

I'll be sending the filename as a variable into the Stored Procedure. Is this even possible using the OPENROWSET? Any advise would be greatly appreciated.

Thanks,
Jeremy
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/13/2006 :  01:29:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
OpenRowset will work only if the file exists already. Otherwise you can use bcp to load data as csv file then make it as EXCEL file

Madhivanan

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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/25/2006 :  09:12:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
this is my take on it
Exports any query with column names to excel that you don't have to create first:

http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/25/2006 :  12:32:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks Mladen

Madhivanan

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

shybi
Starting Member

41 Posts

Posted - 07/26/2006 :  08:48:12  Show Profile  Reply with Quote
hi,

i want to export data from Excel to SQL Server. apart from using DTS and Export wizard, is it possible to do using query? can anyone please help me to slove this issue. thanx
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/26/2006 :  09:02:11  Show Profile  Visit spirit1's Homepage  Reply with Quote
something like:
insert into yourTable
select columns from OpenRowsset(...)



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/26/2006 :  09:28:48  Show Profile  Reply with Quote
hi madhivanan,

I badly need your help.
I want to import excel data into Sql Server.my excel file name is Employee.xls and database table is UserDetail
now, what i am doing is,
i am selecting the .xls file by uploading the file like

OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Import Excel" ;
fdlg.InitialDirectory = @"F:\DEVELOPMENT 2.0\e-Helpline\" ;
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*" ;
fdlg.FilterIndex = 2 ;
fdlg.RestoreDirectory = true ;
if(fdlg.ShowDialog() == DialogResult.OK)
{
textBox1.Text = fdlg.FileName ;
}

this is working fine. i can upload the file.

then next what i did is,i try to connect to the excelsheet like

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source='" + textBox1.Text.ToString() + "';Extended Properties=\"Excel 8.0;HDR=YES;\"";

then i try to connect to sqlserver like

string source = "user id=sa;password=sa;da" +
"ta source=\"practises\";persist security info=False;initial catalog=Northwind";

then i try to import the excel data into sql server like

try
{
//To export data from Excel to existing SQL Server table,

string sql = "Insert into UserDetail([FirstName],[LastName]) Select * FROM OPENROWSET('" + excelConnectionString + "'; SELECT [FirstName],[LastName] FROM [Employee1$])";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();

catch(Exception ex)
{
MessageBox.Show("Error:" +ex.Message);
}

after executing this it showing the following error

ex.Message = "Line2:Incorrect syntax near 'F:'.\r\nLine 2:Incorrect syntax near ')'.

this is my problem.hope to see your reply soon.thanx
shybi
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/26/2006 :  10:19:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is the result of string sql? Check if the syntax is correct

Madhivanan

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

shybi
Starting Member

41 Posts

Posted - 07/26/2006 :  10:31:05  Show Profile  Reply with Quote
following is the result of the sql:

sql = "Insert into UserDetail([FirstName],[LastName]) Select * FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0;\r\n\t\t\t\tData Source='F:\\DEVELOPMENT 2.0\\e-Helpline\\Employee.xls';Extended Properties=\"Excel 8.0;HDR=YES;\"';SELECT [FirstName],[LastName] FROM [Employee1$])"

after executing its showing syntax error like

ex.Message = "Line2:Incorrect syntax near 'F:'.\r\nLine 2:Incorrect syntax near ')'.


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/26/2006 :  11:02:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

I dont think you need \r\n\t\t\t\t
Also check if the physical path of the file is correct


Madhivanan

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

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  01:51:51  Show Profile  Reply with Quote
hi madhivanan,
good morning.

i had removed the \r\n\t\t\t\t.but again syntax error.
can you tell me the syntax for OPENROWSET

shybi
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/27/2006 :  04:24:45  Show Profile  Visit spirit1's Homepage  Reply with Quote
why don't you look into BOL = Books onlike = SQL server help?
It's explained pretty well there.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  04:36:56  Show Profile  Reply with Quote
hi spirit,

i have given the code above. can you tell me what might be teh cause of the syntax error?

shybi
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  04:40:18  Show Profile  Reply with Quote
i try to run the query in the query analyzer. its throwing the following error

INSERT INTO UserDetail
([FirstName], [LastName])
SELECT *
FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' F : \\ DEVELOPMENT 2.0 \\ e - Helpline \\ Employee.xls ';Extended Properties=\]
Excel 8.0; HDR = YES; [';
SELECT [FirstName], [LastName]
FROM [Employee1$])

the error is : Line 1: Incorrect Syntax near 'F:'.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/27/2006 :  04:58:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
you have to many single quotes = '
replace them with "




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 07/27/2006 :  06:03:59  Show Profile  Reply with Quote
hi spirit,

what i am trying to do is

I want to import excel data into Sql Server.my excel file name is Employee.xls and database table is UserDetail
now, what i am doing is,
i am selecting the .xls file by uploading the file like

OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Import Excel" ;
fdlg.InitialDirectory = @"F:\DEVELOPMENT 2.0\e-Helpline\" ;
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*" ;
fdlg.FilterIndex = 2 ;
fdlg.RestoreDirectory = true ;
if(fdlg.ShowDialog() == DialogResult.OK)
{
textBox1.Text = fdlg.FileName ;
}

this is working fine. i can upload the file.

then next what i did is,i try to connect to the excelsheet like

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source='" + textBox1.Text.ToString() + "';Extended Properties=\"Excel 8.0;HDR=YES;\"";

then i try to connect to sqlserver like

string source = "user id=sa;password=sa;da" +
"ta source=\"practises\";persist security info=False;initial catalog=Northwind";

then i try to import the excel data into sql server like

try
{
//To export data from Excel to existing SQL Server table,

string sql = "Insert into UserDetail([FirstName],[LastName]) Select * FROM OPENROWSET('" + excelConnectionString + "'; SELECT [FirstName],[LastName] FROM [Employee1$])";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();

catch(Exception ex)
{
MessageBox.Show("Error:" +ex.Message);
}

when i execute this, i am getting a syntax error:

Error:Line 1: Incorrect syntax near 'F:'.
Line 1: Incorrect synatx near ')'.

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