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

naadkhula
Starting Member

2 Posts

Posted - 07/16/2007 :  08:08:37  Show Profile  Reply with Quote
rept_name is a variable where the rept name is generated automatically.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/16/2007 :  08:34:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by naadkhula

rept_name is a variable where the rept name is generated automatically.

Then you need to use Dynamic sql
Refer third page of this thread
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=3

Madhivanan

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

Overload
Starting Member

4 Posts

Posted - 07/20/2007 :  14:24:52  Show Profile  Reply with Quote
Hi guys,
Please, I need some help.
I read through the thread from page 1-12 and couldnt find the answer to my question.
A couple of ppl have asked the same question but have been ignored.

Question is HOW CAN YOU GET THE RESULTS FROM A QUERY RAN IN SQL INTO EXCEL AND HOW CAN YOU AUTOMATE THIS PROCESS TO RUN AT A SCHEDULED TIME.

SAY I WRITE A QUERY TO RETRIEVE INFOMATION AND I WANT THE RESULTS FROM THE QUERY TO BE DUMPED INTO EXCEL AUTOMATICALLY?

THANKS FOR YOUR HELP.
Go to Top of Page

purs0052
Starting Member

1 Posts

Posted - 07/20/2007 :  16:05:29  Show Profile  Reply with Quote
hi, so I'm very new to using SQL Server and am not certain how exactly to start, so I guess I'm mostly hoping to get a bit if direction to get me started for now...

what I would like to do is write a script that is executed from a client computer, it needs to access an SQL database on a server (from this I presume I will need to supply a server name, database name, user ID and password... that sort of stuff), then exports a few specific columns from a specific table into an excel spreadsheet that is located on the clients harddrive.

I've seen the "INSERT INTO OPENROWSET(..." example used a lot, but I do not see it being used to access things remotely, and have not found anything else that looks helpful. any assistance here would be greatly appreciated. thanks in advance

Donald P.
Go to Top of Page

Rammi
Starting Member

19 Posts

Posted - 07/26/2007 :  07:51:48  Show Profile  Reply with Quote
Hi Everybody
I am trying to use OpenRowSet function for exporting data to excel file with the query:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls;','SELECT a,b FROM [Sheet1$]') select convert(varchar(10),id),name from emp

It throw the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT a,b FROM [Sheet1$]" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Please let me know to solve this.
Go to Top of Page

Overload
Starting Member

4 Posts

Posted - 07/26/2007 :  10:05:46  Show Profile  Reply with Quote
Ok ppl, I found a way around it and since nobody was able to help or come up with a solution I thot i'd share mine.

If you have the query already written, convert it to a view using

CREATE VIEW *insert name*
as
[Then paste your query here]

Note: Do not create the view using the graphical interface, i mean dont right click on view and select new view, if you do that you will run into so many errors.

Once you create your view the you can now use the OPENROWSET function like you would use it with a table, making sure all the columns in your view matches the one in the excel spreadsheet. IT MUST BE EXACTLY THE SAME THING

quote:
Originally posted by Overload

Hi guys,
Please, I need some help.
I read through the thread from page 1-12 and couldnt find the answer to my question.
A couple of ppl have asked the same question but have been ignored.

Question is HOW CAN YOU GET THE RESULTS FROM A QUERY RAN IN SQL INTO EXCEL AND HOW CAN YOU AUTOMATE THIS PROCESS TO RUN AT A SCHEDULED TIME.

SAY I WRITE A QUERY TO RETRIEVE INFOMATION AND I WANT THE RESULTS FROM THE QUERY TO BE DUMPED INTO EXCEL AUTOMATICALLY?

THANKS FOR YOUR HELP.

Go to Top of Page

Overload
Starting Member

4 Posts

Posted - 07/26/2007 :  10:20:16  Show Profile  Reply with Quote
hey
First thing you need to do is create a spreadsheet containing headers matching the ones you are trying to select.
You must make sure the column name in the table is exactly the same in the spreadsheet. For example
say you trying to select 2 colums 'Name' and 'Address' column from a table EMP, you must create an excel spreadsheet and enter into the first column 'Name' and the next column 'Address' your best bet is copy and paste , so you have exactly the same thing, am putting emphasis on this cos thats what gives you the error, even a little space will make it run into errors. So you have an empty spreadsheet with only headers. Then you can use the function like the one below...

INSERT INTO OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls;',
'SELECT
[Name],
[Address]
FROM
[Sheet1$]'
)

SELECT
[Name],
[Address]

FROM

EMP


quote:
Originally posted by Rammi

Hi Everybody
I am trying to use OpenRowSet function for exporting data to excel file with the query:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls;','SELECT a,b FROM [Sheet1$]') select convert(varchar(10),id),name from emp

It throw the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT a,b FROM [Sheet1$]" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Please let me know to solve this.



Edited by - Overload on 07/26/2007 10:24:16
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 08/03/2007 :  14:12:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SQL Server 2005 update...

If you are using SQL Server 2005, make sure that you have enabled the Ad Hoc Distributed Queries option by using SQL Server Surface Area Configuration, as in the following example:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$


Note that OPENROWSET uses an uncommon syntax for the second ("Provider String") argument:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)


The syntax that an ActiveX Data Objects (ADO) developer may expect to use for the second ("Provider String") argument with OPENROWSET:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

This syntax raises the following error from the Jet Provider:
Could not find installable ISAM.


Also see http://support.microsoft.com/kb/306397/en-us


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 08/03/2007 14:15:27
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/06/2007 :  03:22:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks for the updation Peso

Madhivanan

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

b00gieman
Starting Member

14 Posts

Posted - 08/16/2007 :  04:26:52  Show Profile  Reply with Quote
Hi!
I also tried to import data from excel into ms sql.I'm using excel 2003 and SQL Server 2000.

SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\inetpub\wwwroot\fileuploader\upload\tmb2.xls', 'SELECT * FROM [Sheet1$]')

When I'm running this command in QueryAnalyzer,I'm getting the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

What could be the cause of this problem?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/16/2007 :  05:41:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Make sure the file exists in the server's directory

Madhivanan

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

b00gieman
Starting Member

14 Posts

Posted - 08/16/2007 :  06:27:06  Show Profile  Reply with Quote
The file exists.I'm not sure if I specified the correct path.Basically,I save the excel file in a folder located on the server.
On the upload page,I have this code for finding the upload folder:

Response.Write("ServermpPath"&Server.MapPath("upload")&"")

On the web page,the upload path I'm getting:c:\inetpub\wwwroot\Test_AssetDB\fileuploader\upload\tmb2.xls

The command for importing files into ms sql is:

SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+Server.MapPath("\\fileuploader\\upload\\tmb2.xls")+"', 'SELECT * FROM [Sheet1$]')"

I wanted to ask....can somebody tell me if I'm doing the right thing(is the Server.MapPath used correctly?)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/22/2007 :  04:31:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by b00gieman

The file exists.I'm not sure if I specified the correct path.Basically,I save the excel file in a folder located on the server.
On the upload page,I have this code for finding the upload folder:

Response.Write("ServermpPath"&Server.MapPath("upload")&"")

On the web page,the upload path I'm getting:c:\inetpub\wwwroot\Test_AssetDB\fileuploader\upload\tmb2.xls

The command for importing files into ms sql is:

SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+Server.MapPath("\\fileuploader\\upload\\tmb2.xls")+"', 'SELECT * FROM [Sheet1$]')"

I wanted to ask....can somebody tell me if I'm doing the right thing(is the Server.MapPath used correctly?)


Print the value returned from Print the filename returned from Server.MapPath("\\fileuploader\\upload\\tmb2.xls")
and see if it is valid path

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 08/22/2007 04:33:46
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/22/2007 :  04:35:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I also think it should be

SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+Server.MapPath("\fileuploader\upload\tmb2.xls")+"', 'SELECT * FROM [Sheet1$]')"



Madhivanan

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

b00gieman
Starting Member

14 Posts

Posted - 08/22/2007 :  04:45:13  Show Profile  Reply with Quote
Solved that.Now,my problem is that some values from the excel file like 87987845 are stored in the sql table like 8.79878e+007.Any idea on the cause of this problem?

Thanks!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/24/2007 :  04:38:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by b00gieman

Solved that.Now,my problem is that some values from the excel file like 87987845 are stored in the sql table like 8.79878e+007.Any idea on the cause of this problem?

Thanks!


What is the datatype of the column in the table?

Madhivanan

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

b00gieman
Starting Member

14 Posts

Posted - 08/24/2007 :  05:27:41  Show Profile  Reply with Quote
I solved that too.The columns should've been fromatted to text before introducing data into the excel file
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 08/24/2007 :  05:44:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or just change the IMEX mode!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BHari
Starting Member

India
2 Posts

Posted - 08/24/2007 :  09:55:18  Show Profile  Reply with Quote
Hi guys..this hread was really helpful for me..
I am using the bcp to export data from my SQL tables to excel..however, I am not getting the column name in the sheet. if I create a xls file with the coulmn name, it gets overridden with the values..is thr a way whr I can get the column name in the output file..plz help..

B
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/28/2007 :  02:28:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by BHari

Hi guys..this hread was really helpful for me..
I am using the bcp to export data from my SQL tables to excel..however, I am not getting the column name in the sheet. if I create a xls file with the coulmn name, it gets overridden with the values..is thr a way whr I can get the column name in the output file..plz help..

B


Dont use bcp. As suggested use OPENROWSET

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