| Author |
 Topic  |
|
hemhane
Starting Member
2 Posts |
 Posted - 06/07/2006 : 14:16:14
|
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!
|
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/10/2006 : 04:17:34
|
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 |
|
|
|
datagod
Starting Member
37 Posts |
Posted - 06/12/2006 : 14:52:11
|
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. |
|
|
|
hemhane
Starting Member
2 Posts |
Posted - 06/12/2006 : 15:24:31
|
Thanks,Madhivanan
Duane |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/13/2006 : 02:09:55
|
datagod, before importing data to EXCEL, format that cell as Number
Madhivanan
Failing to plan is Planning to fail |
|
|
|
datagod
Starting Member
37 Posts |
Posted - 06/13/2006 : 07:33:25
|
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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/13/2006 : 10:03:29
|
>>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 |
|
|
|
Senjaya
Starting Member
USA
2 Posts |
Posted - 06/14/2006 : 15:39:07
|
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 ? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/26/2006 : 04:19:15
|
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 |
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/26/2006 : 20:24:31
|
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$]')
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/27/2006 : 02:12:55
|
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 |
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/27/2006 : 03:02:51
|
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.
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/27/2006 : 03:17:45
|
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 |
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/27/2006 : 05:12:14
|
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' |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/27/2006 : 05:25:36
|
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 |
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/27/2006 : 05:40:22
|
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.
|
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/27/2006 : 06:09:04
|
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'
|
|
|
|
jgkiran
Starting Member
6 Posts |
Posted - 06/27/2006 : 06:15:29
|
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. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/27/2006 : 06:17:53
|
Can you post the table structure of Entries?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 06/27/2006 : 06:24:59
|
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 |
|
|
|
Topic |
|
Export to Excel
