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
22755 Posts

Posted - 08/21/2006 :  01:15:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If you use front end application, write a loop to export each table's data to seperate sheets using OpenRowset

Madhivanan

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

hellilyntax
Starting Member

Malaysia
19 Posts

Posted - 08/27/2006 :  04:52:52  Show Profile  Send hellilyntax a Yahoo! Message  Reply with Quote
hi guys,
im new in programmin and dont quite understand much what u r discussing in he. however i guess my problem is something to do with this topic.
here is my problem.
i upload few excel files to my webserver at hosting company and need to export the excel rows to sql server on their side to be displayed in my website. i cant use DTS because of some restriction from them. so i try to use TSQL which read from every row in exvel and insert into SQL Server table. i tried to execute the TSQL using asp codes from wensite but the connection always timed out because the rows is too many...up to 10000.
do you have any idea to speed up the transaction?
Go to Top of Page

hellilyntax
Starting Member

Malaysia
19 Posts

Posted - 08/28/2006 :  04:18:11  Show Profile  Send hellilyntax a Yahoo! Message  Reply with Quote
ok...i tried your method and it's worked.
now im moving to next step...strored procedure...
but i got this message...
can u rectify this?

Server: Msg 7405, Level 16, State 1, Line 2
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.


Here is my stores procedure

CREATE PROCEDURE test AS
Declare @sql as Varchar(1000)
set @sql='
insert into SH (sh_cust_no, sh_cust_name, sh_cust_country, sh_order_no, sh_line_no, sh_order_date, sh_po_no, sh_inv_no, sh_inv_date, sh_product_refno, sh_product_descr, sh_qty, sh_uom, sh_upr)
select * from OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0; Database=D:\export\SH060822.xls; hdr=no'',
''select f1, f2, f3, f4,F5 ,F6, F7, F8, f9, F10, F11, F12, F13, F14 from [SH060822$]'')
'
exec (@sql)
GO
Go to Top of Page

hellilyntax
Starting Member

Malaysia
19 Posts

Posted - 08/28/2006 :  08:04:29  Show Profile  Send hellilyntax a Yahoo! Message  Reply with Quote
ITS OK, I ALREADY SOLVED IT
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/28/2006 :  11:44:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
How did you solve it?

Madhivanan

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

Rusty0918
Starting Member

9 Posts

Posted - 08/29/2006 :  10:52:09  Show Profile  Reply with Quote
I'm new to this kind of programming, so if someone can give me some instructions on how to code what I want, that would be wonderful.
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 08/31/2006 :  16:10:47  Show Profile  Reply with Quote
quote:
Originally posted by Rusty0918

I'm new to this kind of programming, so if someone can give me some instructions on how to code what I want, that would be wonderful.



I guess in order to help you write the code, we'd have to first know what language you're using for your front-end application. Otherwise, how do we know what code to write? Do we write VB, or VB.NET, or VBA, or C++, or C#, or Delphi, or Java, or COBOL, or...

Your question is like calling your repair shop and saying "My car isn't working. How much will it cost to fix it?". I guess the mechanic can say "A million dollars." and be OK, but he'd sure be in trouble if he guessed a hundred dollars and then found out you'd run it off a thousand foot cliff, wouldn't he?

Help us out with some information so we can help you.

Ken
Go to Top of Page

Rusty0918
Starting Member

9 Posts

Posted - 09/05/2006 :  15:42:17  Show Profile  Reply with Quote
I was going to code this procedure in SQL Server Enterprise Manager, so I'd guess I'd be using VBScript (does SQL Server support that?), or Java if it can be done that way.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/06/2006 :  12:03:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Have this code in stored procedure and call that procedure from front end application

Madhivanan

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

Rusty0918
Starting Member

9 Posts

Posted - 09/08/2006 :  17:48:18  Show Profile  Reply with Quote
Look. I'm kind of frustrated, but what you're giving me is not good enough. I need knowledge on how to code this stuff and where to put it (stored procedures?). Do I use Java, vBScript, what do I use?!!!!!! I'm completely new to this and I have to have one of these, the person I work for wants it done in this fashion.

Edited by - Rusty0918 on 09/08/2006 17:57:35
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36932 Posts

Posted - 09/08/2006 :  17:55:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Rusty0918

I really need help on my issue BADLY!!!!



I suggest you start a new thread.

Tara Kizer
Go to Top of Page

CLages
Posting Yak Master

Brazil
116 Posts

Posted - 10/30/2006 :  12:17:37  Show Profile  Reply with Quote
I am Exporting my table to Excell but i have 2 problems

1 - I need to create the xxx.Xls first.
2 - But it doesn't carry my column headers to the excel file.

I would like to Create a Xls file a get a column headers too

any help will be apreciated

tks

Clages
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 10/30/2006 :  20:20:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if this helps

http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx


Madhivanan

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

sanjayanthan
Starting Member

India
5 Posts

Posted - 11/01/2006 :  10:49:09  Show Profile  Reply with Quote
Hi Madhivanan,
I saw lot of solution u have given for the previous problem.i have one problem.

I am using the below sql.It works fine. if the comments value is less than and equal to 255 char.

if the comment value exceeds 255 char it tell binary truncate error.

As per microsoft http://support.microsoft.com/kb/189897 in this artical . I have changed the registry values from 0-16 in all combination it is not working.What will be the problem?

I am using sqlserver2005 and excel 2000

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\mail_data\Book1.xls;', 'SELECT * FROM [Sheet1$]') select Id,Name,comments from test


Thanks in advance
sanjay


sanjayanthan
Go to Top of Page

sanjayanthan
Starting Member

India
5 Posts

Posted - 11/01/2006 :  10:49:33  Show Profile  Reply with Quote
Hi Madhivanan,
I saw lot of solution u have given for the previous problem.i have one problem.

I am using the below sql.It works fine. if the comments value is less than and equal to 255 char.

if the comment value exceeds 255 char it tell binary truncate error.

As per microsoft http://support.microsoft.com/kb/189897 in this artical . I have changed the registry values from 0-16 in all combination it is not working.What will be the problem?

I am using sqlserver2005 and excel 2000

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\mail_data\Book1.xls;', 'SELECT * FROM [Sheet1$]') select Id,Name,comments from test


Thanks in advance
sanjay


sanjayanthan
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/01/2006 :  20:23:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I dont know where the problem is. Post this as new topic so that other members may help you

Madhivanan

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

philkos
Starting Member

Kuwait
5 Posts

Posted - 11/02/2006 :  00:55:48  Show Profile  Reply with Quote
I having an excel file called TEST.XLS (in c:\ drive) which has 3 columns (c0,c1,c2) & 8 rows
c0 c1 c2
1 a A
2 b B
3 c C
4 d D
5 e E
6 f F
7 g G
8 h H

In sql server i have one table called TEST which has three columns
c0 char(10)
c1 char(10)
c2 char(10)


when i runnig the following query

select *
into TEST from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\TEST.xls;HDR=YES',
'select * from [Sheet1$]')



i am getting error like this

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 'Sheet1$'. 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: ].

Version : Sql server 2000 & Excel 2002

Anybody having any idea why this error occuring?
thanks in advance...
Philkos
Go to Top of Page

philkos
Starting Member

Kuwait
5 Posts

Posted - 11/02/2006 :  01:10:43  Show Profile  Reply with Quote
when i runnig the following query

select *
into TEST from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\TEST.xls;HDR=YES',
'select * from [Sheet1$]')



i am getting error like this

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 'Sheet1$'. 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: ].

Version : Sql server 2000 & Excel 2002
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 11/02/2006 :  01:37:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Convince yourself that the Excel file "test.xls" is saved in directory "c:\".
Also make sure that there is at least one sheet named "sheet1" in the Excel file.
You can also check that you have write permissions to the Excel file and that you have write permissions to the directory "c:\".


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/02/2006 01:38:54
Go to Top of Page

sanjayanthan
Starting Member

India
5 Posts

Posted - 11/02/2006 :  05:29:04  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

I dont know where the problem is. Post this as new topic so that other members may help you

Madhivanan

Failing to plan is Planning to fail




i got work around solutions refer
http://www.dnzone.com/ShowDetail.asp?NewsId=234

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