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

frtslgn
Starting Member

3 Posts

Posted - 12/24/2007 :  07:39:46  Show Profile  Reply with Quote
i am using SQL Server 2005
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 12/24/2007 :  07:43:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by frtslgn

i am using SQL Server 2005



Then use this function in your code
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/07/2008 :  05:17:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by frtslgn

how can i write two different query for same page in the excel.
i wrote two query and system displayed the results like this

1 2
--- ---
A jan ---
B jim ---
C jon ---
D jen ---
E jun ---
F --- MRC
G --- BMW
H --- FRD
I --- RVR


but i want

1 2
--- ---
A jan mrc
B jim bmw
C jon frd
D jen rvr
e jun


Can you post your question as a new topic?


Madhivanan

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

natzol
Starting Member

USA
2 Posts

Posted - 01/07/2008 :  10:01:51  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan
Did you try formatting that cell to number?
How did you know that was stored in text format?
Madhivanan



Yes I did try to format columns in the template to number. But it doesn't work as I said - data still arrives as a text.

You can not perform any function (like auto-sum in my case) on this "text data"!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/10/2008 :  09:37:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by natzol

quote:
Originally posted by madhivanan
Did you try formatting that cell to number?
How did you know that was stored in text format?
Madhivanan



Yes I did try to format columns in the template to number. But it doesn't work as I said - data still arrives as a text.

You can not perform any function (like auto-sum in my case) on this "text data"!



So, if you have number like 10000045, how is stored in a EXCEL cell?

Madhivanan

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

pantena1
Starting Member

1 Posts

Posted - 02/14/2008 :  01:12:29  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by natzol

quote:
Originally posted by madhivanan
Did you try formatting that cell to number?
How did you know that was stored in text format?
Madhivanan



Yes I did try to format columns in the template to number. But it doesn't work as I said - data still arrives as a text.

You can not perform any function (like auto-sum in my case) on this "text data"!



So, if you have number like 10000045, how is stored in a EXCEL cell?

Madhivanan

Failing to plan is Planning to fail



Hello.

I've the same problem.
number 1000045 is stored in excel cell '1000045 and we see 100045

Have you any idea?


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 02/15/2008 :  04:13:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
pantena1, did you format the cell to Number before exporting data to EXCEL?

Madhivanan

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

pankaj
Starting Member

1 Posts

Posted - 03/17/2008 :  11:42:59  Show Profile  Reply with Quote
Can i upload more than 1 excel worksheets at one go.
I mean in the same insert statement can i upload more than 1 excel sheets.if yes, how? please reply at the earliest as it is kind of urgent.

quote:
Originally posted by madhivanan

Is rept_name a table name?

Try

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


Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 03/18/2008 :  06:58:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by pankaj

Can i upload more than 1 excel worksheets at one go.
I mean in the same insert statement can i upload more than 1 excel sheets.if yes, how? please reply at the earliest as it is kind of urgent.

quote:
Originally posted by madhivanan

Is rept_name a table name?

Try

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


Madhivanan

Failing to plan is Planning to fail





I dont think you can update two sheets using single statement
You need to use seperate update statements

Madhivanan

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

DeepaShinde
Starting Member

1 Posts

Posted - 03/26/2008 :  03:47:52  Show Profile  Reply with Quote
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
Go to Top of Page

arieltopaz
Starting Member

Israel
2 Posts

Posted - 03/26/2008 :  04:25:37  Show Profile  Reply with Quote
hi
i have a problem
and i hope u can help me

i want to write sql query into vba
how i do that

(i need to take the data from one cell and put it
in my sql query )

thank u all

Go to Top of Page

eltricks
Starting Member

1 Posts

Posted - 03/26/2008 :  11:13:54  Show Profile  Reply with Quote
I just got the same error: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I tried a lot of things, changing priviledged and permissions, at the end everything was solved restarting the services of the server.

Don´t forget to check if your file is closed and if it´s saved on a sql server.

Later.

Tricks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/02/2008 :  02:40:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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


As error says Make sure you used correct sheet name. Check if it is sheet 1 or sheet1

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/02/2008 :  02:41:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by arieltopaz

hi
i have a problem
and i hope u can help me

i want to write sql query into vba
how i do that

(i need to take the data from one cell and put it
in my sql query )

thank u all




Check some sample codes at www.vbcity.com

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/02/2008 :  02:42:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by eltricks

I just got the same error: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

I tried a lot of things, changing priviledged and permissions, at the end everything was solved restarting the services of the server.

Don´t forget to check if your file is closed and if it´s saved on a sql server.

Later.

Tricks


You need to read the entire thread again
Make sure the file is colsed at the time of running the query


Madhivanan

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

tinayw
Starting Member

2 Posts

Posted - 04/02/2008 :  11:32:45  Show Profile  Reply with Quote
I read the thread, it's helpful. But is it possible to use OpenRowSet or OpenDataSource to update a spread sheet based on a field match to a SQL table? In other word, I have to use a join between the spreadsheet and the SQL table in "Update" statement to update EXCEL spreadsheet. The spreadsheet has color coding and VBA with it. I can't use export or DTS from SQL, I can only update value on this spreadsheet. Is this fesible?

Madhivanan, you seem to be the solution here. Can you help?

Thanks,
Tina
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/03/2008 :  02:46:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by tinayw

I read the thread, it's helpful. But is it possible to use OpenRowSet or OpenDataSource to update a spread sheet based on a field match to a SQL table? In other word, I have to use a join between the spreadsheet and the SQL table in "Update" statement to update EXCEL spreadsheet. The spreadsheet has color coding and VBA with it. I can't use export or DTS from SQL, I can only update value on this spreadsheet. Is this fesible?

Madhivanan, you seem to be the solution here. Can you help?

Thanks,
Tina


It would be something like

update O set O.a=100 from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\testing.xls;hdr=no',
'SELECT * FROM [Sheet1$]') O inner join table t on O.a=t.col

Madhivanan

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

im1dermike
Posting Yak Master

222 Posts

Posted - 04/07/2008 :  15:57:49  Show Profile  Reply with Quote
Is there a way to password protect the Excel file either within the OPENROWSET function or in a command prompt I can call using xp_cmdshell?
Go to Top of Page

tinayw
Starting Member

2 Posts

Posted - 04/07/2008 :  16:45:21  Show Profile  Reply with Quote
Thanks madhivanan, but I got error message as following when I ran the update query.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'O'.

update O set O.a=100 from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\testing.xls;hdr=no',
'SELECT * FROM [Sheet1$]') O inner join table t on O.a=t.col

I don't think an alias can be used in the update clause.

Any idea?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/14/2008 :  09:46:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by im1dermike

Is there a way to password protect the Excel file either within the OPENROWSET function or in a command prompt I can call using xp_cmdshell?


I dont know the answer. Post your question as a new topic so that other members may reply or do google search

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