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

vbx
Starting Member

USA
38 Posts

Posted - 04/14/2008 :  22:29:44  Show Profile  Visit vbx's Homepage  Reply with Quote
WOW this thread is growing faster than my children.
Last year I found the solution I needed. However, I got a new PC and lost all my information and forgot how I solved my problem.

I have read several of the same items I read back then but still not sure how to fix it.

I am using SQL Server 2005 developer software and I have 2000 rows of data in an excel sheet that I want to move over to SQL.

I have tried the OPENROW set command at the top and and getting some Ad Hoc errors. These are the same errors that i recieved last year.

If I recall correctly. This error is because the excel file is not on the server? Is this correct?

MY excel file is on my PC while the SQL is a host database.
What do you recommend that I try?

Thanks.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/15/2008 :  02:59:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by vbx

WOW this thread is growing faster than my children.
Last year I found the solution I needed. However, I got a new PC and lost all my information and forgot how I solved my problem.

I have read several of the same items I read back then but still not sure how to fix it.

I am using SQL Server 2005 developer software and I have 2000 rows of data in an excel sheet that I want to move over to SQL.

I have tried the OPENROW set command at the top and and getting some Ad Hoc errors. These are the same errors that i recieved last year.

If I recall correctly. This error is because the excel file is not on the server? Is this correct?

MY excel file is on my PC while the SQL is a host database.
What do you recommend that I try?

Thanks.


Ad hoc error?
Refer this
http://blog.sqlauthority.com/2008/01/02/sql-server-fix-error-15281-sql-server-blocked-access-to-statement-openrowsetopendatasource-of-component-ad-hoc-distributed-queries-because-this-component-is-turned-off-as-part-of-the-se/

Also

1 You need to close the EXCEL file when running the application
2 Also make sure the file is in server's directory. Otherwise use UNC like \\system_name\path_name

Madhivanan

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

vbx
Starting Member

USA
38 Posts

Posted - 04/15/2008 :  08:20:21  Show Profile  Visit vbx's Homepage  Reply with Quote
quote:
Originally posted by madhivanan

Ad hoc error?
Refer this
http://blog.sqlauthority.com/2008/01/02/sql-server-fix-error-15281-sql-server-blocked-access-to-statement-openrowsetopendatasource-of-component-ad-hoc-distributed-queries-because-this-component-is-turned-off-as-part-of-the-se/

Also

1 You need to close the EXCEL file when running the application
2 Also make sure the file is in server's directory. Otherwise use UNC like \\system_name\path_name

Madhivanan

Failing to plan is Planning to fail



madhivanan,
Thanks for the reply and

Having the excel file is not an option for me. I have a hosted db and have tried to go that route. Do you know of another method?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/15/2008 :  09:29:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is the exact error message you are getting?
Refer the link I posted in my previous reply

Madhivanan

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

amal
Starting Member

Bangladesh
1 Posts

Posted - 04/19/2008 :  05:20:30  Show Profile  Reply with Quote
I already use this code in MS SQL SERVER 2000 but its showing error.
Error is given below

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'SheetName$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

So i am not able to Export SQL Data to Excel Pls clarify wht i have to do for this error pls suggest me as soon as possible

quote:
Originally posted by madhivanan

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

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/19/2008 :  08:45:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by amal

I already use this code in MS SQL SERVER 2000 but its showing error.
Error is given below

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'SheetName$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

So i am not able to Export SQL Data to Excel Pls clarify wht i have to do for this error pls suggest me as soon as possible

quote:
Originally posted by madhivanan

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

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail




As errors says, make sure the sheetname is correct
Also your excel file should be in Server's Directory

Madhivanan

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

abs777
Starting Member

2 Posts

Posted - 04/23/2008 :  21:44:09  Show Profile  Reply with Quote
I have an excel spreadsheet with data (column C) I need to import into an existing DB table (cur_customers). I have a field in the table (cust_name) that matches the spreadsheet (column B). So I need to import (column C) into (cust_phone) where (column B) = (cust_name).

I have no idea how to this.

Any help will be greatly appreciated.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/28/2008 :  08:57:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

Like this

update T
set col=e.col
from your_table T inner join
(
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]')
) as e on t.keycol=e.keycol



Madhivanan

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

srinivas.komma@gmail.com
Starting Member

USA
1 Posts

Posted - 05/06/2008 :  15:32:44  Show Profile  Reply with Quote
Hi Deepa, We are experiencing the same problem. Did you figure out what the problem is.

Any help on this would be greatly appreciated.

Thanks
Srinivas
srinivias.komma@gmail.com


quote:
Originally posted by DeepaShinde

Hi,

We are facing issue with the excel sheet from a long time. Tried solving it but couldny solve it :(
The Aplication gives error "Make sure that the import sheet is named 'Sheet1'" (errorcode - 2147467259) while excecuting following code lines (C#.net code).
OleDbDataAdapter myData = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); //throws exception here
myData.TableMappings.Add("Table", "ExcelTest");
myData.Fill(myDataset);

Error Stack Trace is as follows:
"System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString
constr, OleDbConnection connection) at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject) at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup) at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) at
System.Data.OleDb.OleDbConnection.Open() at
System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[]
datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)"

This error is not consistent i.e. on IISRestart the error disappears and we could talk to same excel sheet, which was giving error earlier, without any changes in it. But after some period (some days), the error starts coming again :(
We have made sure all the time that the name of sheet is "Sheet1". Also, the sheet is not open while reading.

Details of our Application:
In our web application, we are importing excel sheet provided by the user on the client side. We store this excel on the server and read it from there.
The folder on the server, where the excel sheet is moved, as well as the excel sheet has the appropriate rights (full access) for the "Network Service" account, since the service account in the identity for the application pool is set to "Network Service".

Any help solving the issue would be appreciated.

Thanks,
Deepa



Srinivas

Edited by - srinivas.komma@gmail.com on 05/06/2008 15:34:47
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 05/12/2008 :  05:46:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Srinivas,

As I replied, check the sheet name

Madhivanan

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

emroc
Starting Member

2 Posts

Posted - 09/30/2008 :  12:29:39  Show Profile  Reply with Quote
Hi all,


Great info in this thread and in these forums in general, very helpful.
I have been wrestling with my export to Excel problem for a couple of weeks now, and just when I think I've found the perfect solution (sp_makewebtask), I find out it's being deprecated in future versions of SQL Server!

I am developing a SSIS job on my server that runs dynamically generated queries that it pulls from a table in my database, and stores the results of the query to a table. I am at the point now where I would like to add a step in my SSIS package to export the full table to an Excel file, but I am not sure the best way to go about this.

Obviously, any solution that requires that the file already exists (e.g. openrowset) would not be suitable for my purposes. sp_makewebtask works perfectly for me, but I require a solution that can be carried over to SQL Server 2008 when needed.

I am sure I am able to use BCP to get this done, but it would require some workaround coding to ensure that the table's column headers are output as well.

Can anyone advise the best solution for this situation?


Any advice is much appreciated.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/01/2008 :  11:18:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by emroc

Hi all,


Great info in this thread and in these forums in general, very helpful.
I have been wrestling with my export to Excel problem for a couple of weeks now, and just when I think I've found the perfect solution (sp_makewebtask), I find out it's being deprecated in future versions of SQL Server!

I am developing a SSIS job on my server that runs dynamically generated queries that it pulls from a table in my database, and stores the results of the query to a table. I am at the point now where I would like to add a step in my SSIS package to export the full table to an Excel file, but I am not sure the best way to go about this.

Obviously, any solution that requires that the file already exists (e.g. openrowset) would not be suitable for my purposes. sp_makewebtask works perfectly for me, but I require a solution that can be carried over to SQL Server 2008 when needed.

I am sure I am able to use BCP to get this done, but it would require some workaround coding to ensure that the table's column headers are output as well.

Can anyone advise the best solution for this situation?


Any advice is much appreciated.



I have modified the thread to include this. Go to the first page of the thread and read it

Madhivanan

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

larus
Starting Member

17 Posts

Posted - 10/22/2008 :  10:16:14  Show Profile  Reply with Quote
Hello,

First post to this forum.

I had problems with Madhivanan's proc_generate_excel_with_columns. I had couple of errors. First was that I had no test table so I changed this

where
table_name='test'

to this

where
table_name='@table_name'

Secondly, the procedure didn't produce column headers in a correct order (the proc below produces it right). I still have a problem with Finnish letters like ä, ö and å. Option -c in bcp clause produces ,, and '' signs. I tried to change option -c into option -w with no better results. Any ideas?

Thanks for Madhivanan for this great topic!

procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)

as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name

from
information_schema.columns
where
table_name= @table_name
order by ordinal_position

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--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)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@file_name+''''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)




Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/23/2008 :  03:17:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Hi larus

I changed the code.

What do you mean by this?
"I still have a problem with Finnish letters like ä, ö and å. Option -c in bcp clause produces ,, and '' signs."


Madhivanan

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

larus
Starting Member

17 Posts

Posted - 10/23/2008 :  03:45:08  Show Profile  Reply with Quote
Thanks for your reply!

I mean that when I run this proc for my table, I get this kind of results in Excel 2003:

Year Code Label Value
2002 10 Sis„taudit 3
2002 10 Sis„taudit 1
2002 10 Sis„taudit 2
2002 10 Sis„taudit 4
2002 10 Sis„taudit 6
2002 10 Sis„taudit 7
2002 10 Sis„taudit 8
2002 10 Sis„taudit 9
2002 10 Sis„taudit 99
2002 65 Sy”p„taudit ja s„dehoito 10
2002 65 Sy”p„taudit ja s„dehoito 1
2002 65 Sy”p„taudit ja s„dehoito 2
2002 65 Sy”p„taudit ja s„dehoito 8
2002 65 Sy”p„taudit ja s„dehoito 99

where 'Sis„taudit' should be 'Sisätaudit' and 'Sy”p„taudit ja s„dehoito' should be 'Syöpätaudit ja sädehoito'.

Edit: In the original table they are like 'Sisätaudit' and 'Syöpätaudit ja sädehoito'.

-Larus

Edited by - larus on 10/23/2008 03:55:50
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/23/2008 :  05:33:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by larus

Thanks for your reply!

I mean that when I run this proc for my table, I get this kind of results in Excel 2003:

Year Code Label Value
2002 10 Sis„taudit 3
2002 10 Sis„taudit 1
2002 10 Sis„taudit 2
2002 10 Sis„taudit 4
2002 10 Sis„taudit 6
2002 10 Sis„taudit 7
2002 10 Sis„taudit 8
2002 10 Sis„taudit 9
2002 10 Sis„taudit 99
2002 65 Sy”p„taudit ja s„dehoito 10
2002 65 Sy”p„taudit ja s„dehoito 1
2002 65 Sy”p„taudit ja s„dehoito 2
2002 65 Sy”p„taudit ja s„dehoito 8
2002 65 Sy”p„taudit ja s„dehoito 99

where 'Sis„taudit' should be 'Sisätaudit' and 'Sy”p„taudit ja s„dehoito' should be 'Syöpätaudit ja sädehoito'.

Edit: In the original table they are like 'Sisätaudit' and 'Syöpätaudit ja sädehoito'.

-Larus


Seems EXCEL is not able to handle non English texts
Post your question as a new topic so that those who know the answer may reply


Madhivanan

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

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/08/2008 :  01:02:40  Show Profile  Reply with Quote
When i run this query,
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from Contract


I am using Excel 2000. I have create a excel file in D:\testing.xls
I got this error.

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


================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/08/2008 :  06:00:08  Show Profile  Reply with Quote


insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Book1.xls;',
'SELECT * FROM [Sheet1$]') select * from FOContract

Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.


================================================

When you realize you've made a mistake, take immediate steps to correct it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/08/2008 :  06:01:36  Show Profile  Reply with Quote
does Sheet1 contain more columns than in actual table?
Go to Top of Page

karthickbabu
Posting Yak Master

India
151 Posts

Posted - 11/08/2008 :  06:35:42  Show Profile  Reply with Quote

Finally, I run without any error.

Thanks

================================================

When you realize you've made a mistake, take immediate steps to correct it.
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.15 seconds. Powered By: Snitz Forums 2000