| Author |
 Topic  |
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
 Posted - 11/11/2010 : 06:22:42
|
quote:
Originally posted by prateekfgiet
according to this query
EXEC sp_makewebtask
@outputfile = 'd:\vishal.xls',
@query = 'Select fullname,code from owner.empmaster where empid in (1,2,3)',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='employee fullname'
i am able to create excel file with multiple rows in single sheet,while i want to create multiple sheet and each sheet contain one row result from above query in single excel file.
thanks in advancered3
For that you need to use other methods specified
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
Ads
Starting Member
23 Posts |
Posted - 11/23/2010 : 12:48:20
|
Hi,
When extracting from SQL to excel. Is it possible to run an sql statement, so that it just grabs the line the was created in the SQL database that day or the last incemented number?
Many thanks |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/29/2010 : 06:05:47
|
quote:
Originally posted by Ads
Hi,
When extracting from SQL to excel. Is it possible to run an sql statement, so that it just grabs the line the was created in the SQL database that day or the last incemented number?
Many thanks
Can you be more specific on what you want to do?
If you regularly import data to EXCEL and want to insert only newly added rows from the table, join the table with EXCEL and get only the new rows based on the id (may be an identity column)
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kavithe111
Starting Member
1 Posts |
Posted - 12/09/2010 : 14:57:58
|
hi, i am using the code that madhivanan has posted in his blog
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/10/10/export-to-excel-with-column-names.aspx
Issue- file gets created with data but there are no column names in the file.
Looks like this part is not working for me
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)
I am using sql 2000 and have office 2003 on the server that i am testing.
Any help would be greatly appreciated!! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 12/20/2010 : 08:41:20
|
quote:
Originally posted by Kavithe111
hi, i am using the code that madhivanan has posted in his blog
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/10/10/export-to-excel-with-column-names.aspx
Issue- file gets created with data but there are no column names in the file.
Looks like this part is not working for me
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)
I am using sql 2000 and have office 2003 on the server that i am testing.
Any help would be greatly appreciated!!
Can you add print @sql before exec(@sql) and see what it returns?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 12/31/2010 : 07:46:25
|
Hi Madhivanan,
Hope you'll be having a good time and happy New year
I am facing an issue related to the OpenRowSet function, with following code
set @db_file='Excel 8.0;Database='+ltrim(rtrim(@ReportFilePath))+';'
go
Insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''' + @db_file + ''',
''SElect * FROM [ITSMetaInfo$]''
)
SELECT * FROM TestDB.dbo.MasterSheetMeta
This throws the following error message:
Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
Which seems that the columns width in excel page is some how smaller to the data length in the Table's column. In order to check the Length of the columns created by the OpenRowSet i have run the following code
Select * into testdb.dbo.temptab from openrowSet .....
And examined the columns in the TempTab, where all columns were of the following datatype and length: nvarchar(510)
Also, i have tried the BCP utility option for copying data into excel file using following Code:
DEclare @ReportFilePath varchar(500),@query varchar(1000),@generatingExcelFile varchar(1000)
set @ReportFilePath='E:\New Folder\MetaInfoTemplate.xls'--+ltrim(rtrim(convert(varchar(10),GETDATE(),112)))+'.xls'
SET @query = 'SELECT * FROM TestDB.dbo.MasterSheetMeta'
SET @generatingExcelFile='bcp "'+@query+'" queryout "' +@ReportFilePath+'" -T -c'
--print @generatingExcelFile
exec master..xp_cmdshell @generatingExcelFile
However this returns the following error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
The file and Instance is on the same Machine, and i am connected via Windows Authentication. Furthermore the database compatibility version is 80 which is restored on the SQL server 2008 (named instance). Also I have excel file as 97-2003 workbook.!!
Please advise how to overcome this issue anyway? Also, it would be great if you could point out the problem in my above codes!
Many Thanks!
MIK |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/04/2011 : 03:26:05
|
Hi, I have been able to fix the issue for BCP code .. however it is still there in the openRowSet .... Kindly help me out on that one!!!
Many Thanks! |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 01/04/2011 : 04:36:29
|
What does this return?
print @generatingExcelFile
Madhivanan
Failing to plan is Planning to fail |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/04/2011 : 06:22:59
|
Hi Madhivanan, I have fixed the issue with BCP and been able to export the data into Excel Successfully (You Can ignore this one).... ---> However the issue is still there in the script of OpenRowSet;
set @db_file='Excel 8.0;Database='+ltrim(rtrim(@ReportFilePath))+';'
go
Insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''' + @db_file + ''',
''SElect * FROM [ITSMetaInfo$]''
)
SELECT * FROM TestDB.dbo.MasterSheetMeta
This throws the following error message:
Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
Which seems that the columns width in excel page is some how smaller to the data length in the Table's column. In order to check the Length of the columns created by the OpenRowSet i have run the following code
Select * into testdb.dbo.temptab from openrowSet .....
And examined the columns in the TempTab, where all columns were of the following datatype and length: nvarchar(510)
Cheers!
MIK |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 01/04/2011 : 08:26:26
|
Which version of EXCEL are you using? I think in versions prior to 2007, the column limit is 256
Madhivanan
Failing to plan is Planning to fail |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/04/2011 : 23:54:28
|
Well i have installed Office 2007, however i am saving the file in Excel 97-2003 format....
Furthermore, if i copy the same data manually from SQL server to excel from Copy-Paste (via mouse) it stores all data correctly in the same format..
thanks!
MIK |
Edited by - MIK_2008 on 01/05/2011 00:08:19 |
|
|
|
ekardo
Starting Member
4 Posts |
Posted - 01/09/2011 : 06:26:35
|
Hi!
I want to use stored procedure to do the insertion but it returns error.
I'll be thankful if someone help me. Tnx in advance!
--insert INTO Product select * --// Error: Column name or number of supplied values does not match table definition. //--
exec spProductInsert_min --// Error: Procedure or function 'spProductInsert_min' expects parameter '@ProductName', which was not supplied. //--
(select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\db.xls;HDR=YES',
'select * from [1$]')
)
|
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/10/2011 : 04:32:40
|
@@--// Error: Column name or number of supplied values does not match table definition. //--
This means that the number of columns in the given table (insert into TableName) are not equal to the number of Columns in the Select Statement. Hence you are getting this error. Make sure that the number of columns are same to the ones in the Select Statement and are in same order.
@@--// Error: Procedure or function 'spProductInsert_min' expects parameter '@ProductName', which was not supplied. //--
this means that you have created a stored procedure with a Parameter "@ProductName", however when you try to call this SP you are not providing the variable e.g.
Execute spProductInsert_min --This will return the error as mentioned
Execute spProductInsert_min 'ProductName' -- This will work fine if the datatype of the parameter is other than numeric values e.g. varchar.
|
Edited by - MIK_2008 on 01/10/2011 04:33:30 |
|
|
|
ekardo
Starting Member
4 Posts |
Posted - 01/10/2011 : 10:19:29
|
Hello Mik,
Thanks for your kind help!
When I use proc with sample data ,it works, but I want to import data from an excel file into sql server table via mentioned proc, in this case it returns error.
-- [Working] Executing proc with sample data
exec spProductInsert_min
@OriginalName = 'sample product'
, @AliasName = ''
, @LocalName = ''
, @Keywords = ''
, @ListingDesc = ''
, @DetailedDesc = ''
, @Make = ''
, @Model = ''
, @ProductNumber = ''
, @PlaceOfOrigin = ''
, @Price = '0'
-- [Not Working] executing proc with excel file
exec spProductInsert_min
(select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\db.xls;HDR=YES',
'select * from [1$]')
)
--// Msg 201, Level 16, State 4, Procedure spProductInsert_min, Line 0
Procedure or function 'spProductInsert_min' expects parameter '@OriginalName', which was not supplied.
(15 row(s) affected). //--
-- The proc I've used:
CREATE PROCEDURE dbo.spProductInsert_min
(
@OriginalName nvarchar(100)
, @AliasName nvarchar(100) = ''
, @LocalName nvarchar(100) = ''
, @Keywords nvarchar(255) = ''
, @ListingDesc nvarchar(128) = ''
, @DetailedDesc nvarchar(MAX) = ''
, @PlaceOfOrigin nvarchar(50) = ''
, @Make nvarchar(100) = ''
, @Model nvarchar(100) = ''
, @ProductNumber nvarchar(30) = ''
, @Price money = '0'
)
AS
DECLARE @ProductID UNIQUEIDENTIFIER
SET @ProductID = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER);
INSERT INTO [bapook].[dbo].[Product]
(
[ProductID]
,[OriginalName]
,[AliasName]
,[LocalName]
,[Keywords]
,[ListingDesc]
,[DetailedDesc]
,[Make]
,[Model]
,[ProductNumber]
,[PlaceOfOrigin]
,[DatePosted]
,[LastModified]
,[Price]
)
VALUES
(
@ProductID
,@OriginalName
,@AliasName
,@LocalName
,@Keywords
,@ListingDesc
,@DetailedDesc
,@Make
,@Model
,@ProductNumber
,@PlaceOfOrigin
,GetDate()
,GetDate()
,@Price
)
GO
I'd highly appreciate if you give me any kind of comment on other mistakes I've done.
Thanks in advance! |
Edited by - ekardo on 01/10/2011 10:31:19 |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/11/2011 : 00:45:15
|
Well Ekardo, i dont know why are you using so many parameters in your stored procedure and what are their purpose .. As you requirements are only to read and insert the data from excel sheet, which can be done via following ways:
1) if the table is created and only needed to insert the data from excel file
Insert into databasename.dbo.TableName
select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\db.xls;HDR=YES',
'select * from [1$]')
2) If the table is not created
select *
into databasename.dbo.TableName
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\db.xls;HDR=YES',
'select * from [1$]')
|
Edited by - MIK_2008 on 01/11/2011 00:46:05 |
|
|
|
ekardo
Starting Member
4 Posts |
Posted - 01/11/2011 : 06:44:13
|
Hi Mik,
Well I've used both ways; the 2nd one (not existing table) works fine which does not fit with my needs, but the first one doesn't for some reasons:
1. The number of columns are not equal (Error: Column name or number of supplied values does not match table definition.)
2. I want to create the GUID using the following method (SET @ProductID = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER);)
This is why I decided to use a proc. If it work fine, then the user will be able to enter products in batch (a great bonus).
Tnx, Kardo |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/12/2011 : 07:07:28
|
Ekardo, the first one is not working fine since the number of columns in your table is more or Less than the columns in the sheet... As i Said in my last response....
Any how if you prefer to use the first method you can do it as both are doing same thing .. However you will need to identify the mis-match between the number of columns between both sources.
If you want me to find this Missing Column for you then send me all required info! ;)
Cheers!
MIK
P.S A hint to Debug the issue... replace the * used in the datasheet query with the exact headings of the columns as in the Sheet... This way you will be able to map each and every column exactly as required.
You can copy all the columns from Sheet and paste it into a Column in a second sheet. Copy the columns of the table and paste them in the next column of the Second Sheet. Keep same names for the columns of both sources and identify the missing one :-D |
Edited by - MIK_2008 on 01/12/2011 07:12:59 |
|
|
|
ekardo
Starting Member
4 Posts |
Posted - 01/13/2011 : 09:38:56
|
Hi Mik,
Thanks a lot for your kind replies and great help! It was very useful. :)
Thanks a again!
Regards,
Kardo |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 01/13/2011 : 13:55:54
|
| you are welcome !!! |
|
|
|
Jinasoso
Starting Member
5 Posts |
|
|
Topic |
|
Export to Excel
