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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to convert the resultset from a query to EXCEL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Raoulh79
Starting Member

24 Posts

Posted - 11/12/2007 :  04:37:15  Show Profile  Reply with Quote
DEAR ALL,

How can i export the result set of a query in EXCEL, so that every field goes to a sepate field in the excel sheet???


For example

select * from ITEMS
where
n0_item_code =1001

Any help will be appreciated!!!!


Thanks in advance....

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/12/2007 :  04:42:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Refer http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

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

Raoulh79
Starting Member

24 Posts

Posted - 11/12/2007 :  04:55:09  Show Profile  Reply with Quote
How can i join this 2 queries together: I mean the query from insert into bla bla bla and the query i want to run ?????


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

and

select * from ITEMS
where
n0_item_code =1001
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/12/2007 :  05:14:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]')
Select query 1
union all
Select query 2


Madhivanan

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

Raoulh79
Starting Member

24 Posts

Posted - 11/12/2007 :  16:26:30  Show Profile  Reply with Quote
Dear All i run the query like:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;',
'SELECT * FROM [sheet1$]')
select * from items

where I saved the excel file in c:\test.xls and the worksheet by default has the name sheet1, but i get the following message:

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'sz_item_ref_no'. The data type is not supported.

I didnt put data type for the columns on the excel, the data i get from the query are numbers and text i dont get why it gives me these error???


Thanks in advance!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 11/12/2007 :  17:28:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What datatype is then 'sz_item_ref_no'? Did it occur to you that you can CAST or CONVERT the data to proper datatype?
And you must also be aware that the path to the excel file is RELATIVE from SQL Server point of view, not your local workstation.
Use UNC path if path not found.



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

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/13/2007 :  02:28:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
what is the datatype of sz_item_ref_no in items table?

Madhivanan

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

Raoulh79
Starting Member

24 Posts

Posted - 11/13/2007 :  02:50:35  Show Profile  Reply with Quote
The 'sz_item_ref_no' in the Item's table has the internal code of the product, it is not sth strange as data type. As i told you the data types that i used are or varchar or text or integer. I dont understand why excel can not take this data types. The sql server is in my laptop where i have created a TEST environment and i am working on it. So the SQL Server 2000 and the Query are both run in the same environment...
Does the structure of the query is right??? I am not sure for that!!!

Thanks in advance!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 11/13/2007 :  03:26:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I am not sure EXCEL accepts TEXT datatype.
I think there is a limit for 1024 bytes in each cell.



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

Raoulh79
Starting Member

24 Posts

Posted - 11/13/2007 :  03:38:42  Show Profile  Reply with Quote
I entered in the database and the 3 fields that i want to import on the excel are the following: The message i get is from the first field sz_item_ref_no.

sz_item_ref_no char(16)
sz_long_descr varchar(60)
sz_short_descr varchar (20)

I want to underline that the fields on the database are fixed, they are related to an application and i can not change them. Do I have to change sth in the query???

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;',
'SELECT * FROM [sheet1$]')
select * from items


The [sheet1$] is right???? Do i need to put the dollar sign anyway???


Any suggestion to over ride the error message...



Thanks in advance!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 11/13/2007 :  04:06:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try to use MSDASQL provider temporarily to see if you get more precise error message
http://weblogs.sqlteam.com/peterl/archive/2007/10/24/Getting-errors-when-working-with-Excel-and-SQL-Server.aspx



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

Raoulh79
Starting Member

24 Posts

Posted - 11/13/2007 :  07:58:54  Show Profile  Reply with Quote
Peso i tried what you said, and to say the truth i get the same message:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied invalid metadata for column 'sz_item_ref_no'. The data type is not supported.

The query was like this:

select * from OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\test.xls;','SELECT t1.sz_item_ref_no, DESCRIPTIONS.SZ_SHORT_DESCR, DESCRIPTIONS.SZ_long_DESCR FROM [sheet1]')
select t1.sz_item_ref_no, DESCRIPTIONS.SZ_SHORT_DESCR, DESCRIPTIONS.SZ_long_DESCR from ITEMS
where
n0_item_code =1001

In addition i have to i tried to change [sheet1$] with [sheet1] and the result i get was the following:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]


The syntax of the query is correct???? Any idea what is going on!!!


Thanks in advance
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