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

hemhane
Starting Member

2 Posts

Posted - 06/07/2006 :  14:16:14  Show Profile  Reply with Quote
Hi Madhivanan

When exporting from SQL to Excel using your "insert into OPENROWSET" command below is there a way to get the Column headings into excel automatically? I am trying to extract multiple tables from SQL each going into it's own Sheet. If there is a Command that names the column headings I can use this commenat to get the list of Column names:

select name from dbo.syscolumns where id=object_id(N'[dbo].[SQLTABLENAME]') order by Colid

Alternativly if I could get the names to be the first row of data followed by X rows of data that would be OK too.. Then Column names could remain A,B,C,...

Thanks!

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/10/2006 :  04:17:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Senjaya,
Try to change the format of the cell before inserting records to it

hemhane.
You need to define the columns before inserting the records
Otherwise you can do something like this
http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx



Madhivanan

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

datagod
Starting Member

37 Posts

Posted - 06/12/2006 :  14:52:11  Show Profile  Reply with Quote
I am trying to examine the contents of an excel spreadsheet (using the aforementioned select * from OPENROWSET(...) that has both numeric and text columns. The weird thing is tha the text columns are displayed fine, but all the numeric columns end up as NULL.

I have to go into the spreadsheet, mark all numeric columns as TEXT, then I have to double click each cell individually. When I do this, excel warns me that "The number in this columns is defined as text or is preceeded by an apostrophe". I can then import the numeric data just fine.

Has anyone else had this problem? I am using SQL 2000 sp3, Excel 2003 sp2.

Thanks.
Go to Top of Page

hemhane
Starting Member

2 Posts

Posted - 06/12/2006 :  15:24:31  Show Profile  Reply with Quote
Thanks,Madhivanan
Duane
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/13/2006 :  02:09:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
datagod, before importing data to EXCEL, format that cell as Number

Madhivanan

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

datagod
Starting Member

37 Posts

Posted - 06/13/2006 :  07:33:25  Show Profile  Reply with Quote
Madhivanan, that did the trick.

It turns out that you have to mark the whole column as numeric, not just the cells in question.

Thanks again!

Edited by - datagod on 06/13/2006 08:31:38
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/13/2006 :  10:03:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>It turns out that you have to mark the whole column as numeric, not just the cells in question.

Yes it is

Madhivanan

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

Senjaya
Starting Member

USA
2 Posts

Posted - 06/14/2006 :  15:39:07  Show Profile  Reply with Quote
Hi Madhivanan,
In excel file - I set the colum header on the first row so the inserted record from SQL will go to 2nd row and so on.
These where the problem sit - The 1st row since it was text (even I set it as number or currency it did not help) it will treated the next row also as text. I has to set up the 2nd row as number then the record will write into 3rd row as number and it left me with 1 empty row in Row 2
Is there any way to format it properly ?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/26/2006 :  04:19:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Senjaya

Hi Madhivanan,
In excel file - I set the colum header on the first row so the inserted record from SQL will go to 2nd row and so on.
These where the problem sit - The 1st row since it was text (even I set it as number or currency it did not help) it will treated the next row also as text. I has to set up the 2nd row as number then the record will write into 3rd row as number and it left me with 1 empty row in Row 2
Is there any way to format it properly ?


I am not sure about it.
Post your question as new topic so that someone will answer it

Madhivanan

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

jgkiran
Starting Member

6 Posts

Posted - 06/26/2006 :  20:24:31  Show Profile  Reply with Quote
Please Help..Ur posts r really helpfull for me..I achieved my results by following the above listed posts,,Now i want to create a stored procedure with the following code and pass a parameter for a file name value( Actually i need to call this from .net)im facing dificulty in doing this..Any syntax help on Passing Parameters???

Thank you in Advance..
Kiran.
--Insert into ImportTemp from the excel sheet

Insert into dbo.ImportTemp([c1], [c2], [c3] , [c4])
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\example2.xls;HDR=YES',
'SELECT [DESTINATION] , [COUNTRYCODE], [AREA CODES],[PRICE]FROM [test$]')
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2006 :  02:12:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this

Create Procedure ExportToExcel(@fileName varchar(200))
as
Declare @sql varchar(1000)
Set @sql ='
Insert into dbo.ImportTemp([c1], [c2], [c3] , [c4])
Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [DESTINATION] , [COUNTRYCODE], [AREA CODES],[PRICE]FROM [test$]'')
'
Exec (@sql)


Madhivanan

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

jgkiran
Starting Member

6 Posts

Posted - 06/27/2006 :  03:02:51  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Try this

Create Procedure ExportToExcel(@fileName varchar(200))
as
Declare @sql varchar(1000)
Set @sql ='
Insert into dbo.ImportTemp([c1], [c2], [c3] , [c4])
Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [DESTINATION] , [COUNTRYCODE], [AREA CODES],[PRICE]FROM [test$]'')
'
Exec (@sql)


Madhivanan

Failing to plan is Planning to fail



Excellent Madhivanan...it worked like a Jem...thanks a lot.
I appreciate u for sharing ur knowledge , this will encourage and help a lot of people like me in doing the same...Keep going...

Thank you once again...
Kiran.


















Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2006 :  03:17:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jgkiran

Excellent Madhivanan...it worked like a Jem...thanks a lot.
I appreciate u for sharing ur knowledge , this will encourage and help a lot of people like me in doing the same...Keep going...

Thank you once again...
Kiran.


Thanks for the kind words

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 06/27/2006 03:18:47
Go to Top of Page

jgkiran
Starting Member

6 Posts

Posted - 06/27/2006 :  05:12:14  Show Profile  Reply with Quote
Hi once again..can u please help me to find the syntax error in the following code.Here im exporting my results from SQL table to a Excel Sheet.When i check the syntax in SQL it does not show any error , but when i execute my Sproc i get an error saying "Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
"

I spend more than 2 hrs to catch it..but failed...
pls help me if any one can...

Thank u,
kiran.


Create Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(1000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [EntryNo],[HeaderNo],[DESTINATION],[COUNTRY],[AREA],
[COUNTRY_AREA],[PRICE] FROM ['+@sheetName+'$]'')
'
select * from Entries
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\TestResult.xls',@sheetName='test'
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2006 :  05:25:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this

Alter Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(1000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [EntryNo],[HeaderNo],[DESTINATION],[COUNTRY],[AREA],
[COUNTRY_AREA],[PRICE] FROM ['+@sheetName+'$]'')
select * from Entries'
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\TestResult.xls',@sheetName='test'

Madhivanan

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

jgkiran
Starting Member

6 Posts

Posted - 06/27/2006 :  05:40:22  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Try this

Alter Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(1000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [EntryNo],[HeaderNo],[DESTINATION],[COUNTRY],[AREA],
[COUNTRY_AREA],[PRICE] FROM ['+@sheetName+'$]'')
select * from Entries'
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\TestResult.xls',@sheetName='test'

Madhivanan

Failing to plan is Planning to fail



That worked Brilliantly...it was a silly mistake...Thanks a lot for fast reply .I was anciously waiting for ur reply as it is my Proj.Submition day..Thank u once again...
i will keep in touch with ur posts regularly..
Regards,
Kiran.


Go to Top of Page

jgkiran
Starting Member

6 Posts

Posted - 06/27/2006 :  06:09:04  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Try this

Alter Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(1000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [EntryNo],[HeaderNo],[DESTINATION],[COUNTRY],[AREA],
[COUNTRY_AREA],[PRICE] FROM ['+@sheetName+'$]'')
select * from Entries'
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\TestResult.xls',@sheetName='test'

Madhivanan

Failing to plan is Planning to fail





Im sorry to disturb u again..im having a error on the above code when i run the Sproc.it says :
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.


where should be the conversion take place? any idea..


the following is the Procedure and call..thanx in advance...

------------

USE [TestChris]
GO
/****** Object: StoredProcedure [dbo].[ExportToExcelSheet] Script Date: 06/27/2006 13:07:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[ExportToExcelSheet](@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(1000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [EntryNo],[HeaderNo],[DESTINATION],[COUNTRY],[AREA],
[COUNTRY_AREA],[PRICE] FROM ['+@sheetName+'$]'')
select * from Entries'
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\ResultTest.xls',@sheetName='test'







Go to Top of Page

jgkiran
Starting Member

6 Posts

Posted - 06/27/2006 :  06:15:29  Show Profile  Reply with Quote
Sorry,,,that was ok..actually while testing if i clear the records in Excelsheet..then i get this problem..if i delete the records i dont have this problem. the records were imported properly..

sorry to bother u.
Regards
kiran.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2006 :  06:17:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post the table structure of Entries?

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2006 :  06:24:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jgkiran

Sorry,,,that was ok..actually while testing if i clear the records in Excelsheet..then i get this problem..if i delete the records i dont have this problem. the records were imported properly..

sorry to bother u.
Regards
kiran.


Glad to know that you solved the problem

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.17 seconds. Powered By: Snitz Forums 2000