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

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/11/2010 :  06:22:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Ads
Starting Member

23 Posts

Posted - 11/23/2010 :  12:48:20  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/29/2010 :  06:05:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kavithe111
Starting Member

1 Posts

Posted - 12/09/2010 :  14:57:58  Show Profile  Reply with Quote
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!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/20/2010 :  08:41:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 12/31/2010 :  07:46:25  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/04/2011 :  03:26:05  Show Profile  Reply with Quote
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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/04/2011 :  04:36:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What does this return?

print @generatingExcelFile

Madhivanan

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

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/04/2011 :  06:22:59  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/04/2011 :  08:26:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/04/2011 :  23:54:28  Show Profile  Reply with Quote
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
Go to Top of Page

ekardo
Starting Member

4 Posts

Posted - 01/09/2011 :  06:26:35  Show Profile  Reply with Quote
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$]')
)
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/10/2011 :  04:32:40  Show Profile  Reply with Quote
@@--// 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
Go to Top of Page

ekardo
Starting Member

4 Posts

Posted - 01/10/2011 :  10:19:29  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/11/2011 :  00:45:15  Show Profile  Reply with Quote
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
Go to Top of Page

ekardo
Starting Member

4 Posts

Posted - 01/11/2011 :  06:44:13  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/12/2011 :  07:07:28  Show Profile  Reply with Quote
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
Go to Top of Page

ekardo
Starting Member

4 Posts

Posted - 01/13/2011 :  09:38:56  Show Profile  Reply with Quote
Hi Mik,
Thanks a lot for your kind replies and great help! It was very useful. :)
Thanks a again!
Regards,
Kardo
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 01/13/2011 :  13:55:54  Show Profile  Reply with Quote
you are welcome !!!
Go to Top of Page

Jinasoso
Starting Member

5 Posts

Posted - 02/14/2011 :  22:37:56  Show Profile  Reply with Quote
Maybe you can try to use some free data export component to save your time and energy. I know one named Spire.DataExport. It is said that the component is useful.
You can visit
http://www.e-iceblue.com/Download/download-dataexport-for-net-now.html
to get more information.
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.19 seconds. Powered By: Snitz Forums 2000