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

samoh
Starting Member

USA
1 Posts

Posted - 09/28/2005 :  13:12:55  Show Profile  Reply with Quote
Because I don't have access to the sql server 2000 am not able to use the options below...I'm on a desktop with a .db back -up file and excel...? Any suggestions?

muchas graciosas


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

Posted - 09/29/2005 :  01:40:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You need to restore the file to SQL Server and use that query
Otherwise post your question as new topic in the Developer Forum of SQL Team Forums and you will get better reply than mine

Madhivanan

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

juvethski
Starting Member

Canada
46 Posts

Posted - 09/30/2005 :  14:02:32  Show Profile  Reply with Quote
hi,
the export to excel works fine... but it truncates the data to 255..

i hv a description field defined as text, and most of the values for this field exceeds 255. when i export it to excel thru the openset method, it truncates the values to 255 characters. is there an option in openrowset that will not truncate the data?

thanks!!
Go to Top of Page

maborcha
Starting Member

2 Posts

Posted - 10/03/2005 :  16:44:45  Show Profile  Reply with Quote
I am trying to use the following to export a database to a text file.

insert into OpenRowset('msdasql', 
  'Driver={Microsoft Text Driver (*.txt; *.csv)};
  Database=\\server\folder\Test.TXT;Server=server; UID=user;PWD=password')
  Select * FROM ClientData WHERE (Client = '???')


Or is there a better way to export to a text file. I need to be able to specify the text file name (full path) and the client to be exported.

Thanks,
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 10/04/2005 :  02:23:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by juvethski

hi,
the export to excel works fine... but it truncates the data to 255..

i hv a description field defined as text, and most of the values for this field exceeds 255. when i export it to excel thru the openset method, it truncates the values to 255 characters. is there an option in openrowset that will not truncate the data?

thanks!!


See if this helps you
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q189897

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 10/04/2005 :  02:25:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by maborcha

I am trying to use the following to export a database to a text file.

insert into OpenRowset('msdasql', 
  'Driver={Microsoft Text Driver (*.txt; *.csv)};
  Database=\\server\folder\Test.TXT;Server=server; UID=user;PWD=password')
  Select * FROM ClientData WHERE (Client = '???')


Or is there a better way to export to a text file. I need to be able to specify the text file name (full path) and the client to be exported.

Thanks,


Use bcp
Try this

Exec Master..xp_cmdShell 'bcp "Select * FROM DBName..ClientData WHERE (Client = ''???'')" queryout "\\server\folder\Test.TXT" -c'

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 10/04/2005 02:26:25
Go to Top of Page

juvethski
Starting Member

Canada
46 Posts

Posted - 10/04/2005 :  10:24:48  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by juvethski

hi,
the export to excel works fine... but it truncates the data to 255..

i hv a description field defined as text, and most of the values for this field exceeds 255. when i export it to excel thru the openset method, it truncates the values to 255 characters. is there an option in openrowset that will not truncate the data?

thanks!!


See if this helps you
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q189897

Madhivanan

Failing to plan is Planning to fail



hi,
thanks for your reply... but this solution is on importing an excel to a sql table... my problem is the other way around... exporting the table to excel...one of the columns of the table is defined as text so this creates problem. it gives an error meesage:

"Server: Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated."

thanks.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 10/04/2005 :  10:32:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>one of the columns of the table is defined as text so this creates problem

Did you try DTS method?

Madhivanan

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

juvethski
Starting Member

Canada
46 Posts

Posted - 10/04/2005 :  21:17:03  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

>>one of the columns of the table is defined as text so this creates problem

Did you try DTS method?

Madhivanan

Failing to plan is Planning to fail


yes, and the dts works fine. but dts is not the solution for my problem because i am exporting tables that are randomnly created by a script. the best way to do it is either thru bcp or thru the jet oledb. but both methods is has limitation also:
- exporting thru jet oled db --- truncation problem
- bcp - can only export to a txt format. my data contains char(10) characters so this creates a problem for txt files.

any recommendation?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 10/05/2005 :  02:17:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>- bcp - can only export to a txt format. my data contains char(10) characters so this creates a problem for txt files

Try this

Exec Master..xp_CmdShell 'bcp "Select Columns from DBName..TableName" queryout "C:\test.xls" -c'

Now all the data will be copied to test.xls regardless of column width
All you have to do is after running that bcp query, open the file and save it as Excel file

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 10/05/2005 02:18:32
Go to Top of Page

talats
Starting Member

1 Posts

Posted - 12/08/2005 :  08:38:17  Show Profile  Reply with Quote
Hi,
I want to import excel content to Sql Server.
I've a database table "tbl_StaticData" having two columns and a excel "test.xls" having five columns. Out of that five columns I'm trying to take just two columns from excel for import. I've written a query as follows


Insert into tbl_StaticData([BORROWER NAME], [LOAN_NUMBER] )
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls;HDR=YES',
'SELECT BORROWER'S NAME , LOAN NUMBER FROM [Sheet1$]')


Here I'm getting an errors

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'S'.
Server: Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark before the character string ')

Now my queries are
1. How to take columns names from Excel having some space in between
2. How to take columns names from Excel having Aposhtope's
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 12/08/2005 :  09:29:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this

Insert into tbl_StaticData([BORROWER NAME], [LOAN_NUMBER] )
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls;HDR=YES',
'SELECT [BORROWER''S NAME] , [LOAN NUMBER] FROM [Sheet1$]')

Better to avoid having spaces and instead use UnderScore(_) like LOAN_NUMBER and also avoid single quote in the column name

Madhivanan

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

twingc
Starting Member

3 Posts

Posted - 01/06/2006 :  14:49:56  Show Profile  Reply with Quote
Hi,

Thank you for the great tips from this thread. Regarding the same topic, I am trying to import data from excel to sql using the code

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

but the challenge I am having is where should the file testing.xls actually be located? Our configuration is have a vb application sitting in the client machine and the sql server is in another one. I tried to put the testing.xls in the D drive of the client machine but I keep getting error messages. So, I copied the file and put that into the sql server's D drive. Everything works but the problem is that regular users are not allow to have access to the sql server box. I also tried to change d:\testing.xls to something like \\clientmachine\d$\testing.xls but it doesn't work. Can everybody help? I would really appreciate that.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 01/09/2006 :  02:42:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>\\clientmachine\d$\testing.xls but it doesn't work.

What is the error you got?
Make sure you have permissions to access file from client machine

Madhivanan

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

twingc
Starting Member

3 Posts

Posted - 01/09/2006 :  09:24:51  Show Profile  Reply with Quote
Thank you Madhivanan for the reply.

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

Wing



quote:
Originally posted by madhivanan

>>\\clientmachine\d$\testing.xls but it doesn't work.

What is the error you got?
Make sure you have permissions to access file from client machine

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 01/09/2006 :  09:42:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if this helps
http://www.kbalertz.com/kb_818182.aspx

Madhivanan

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

twingc
Starting Member

3 Posts

Posted - 01/09/2006 :  10:31:16  Show Profile  Reply with Quote
Hi Madhivanan:

Thanks for the tip. Should I look for the msjet40.dll in the client machine or the one in the sql server? Our sql server is running on windows 2003 server sp1 and the version of the MSJET40.dll is 4.0.9025.0. I just want to make sure if I understand. Based on the article you sent me,

Msjet40.dll version Jet 4.0 release level
4.0.2927.4 Service Pack 3 (SP3)
4.0.3714.7 Service Pack 4 (SP4)
4.0.4431.1 or 4.0.4431.3 Service Pack 5 (SP5)
4.0.6218.0 Service Pack 6 (SP6)
4.0.6807.0 Service Pack 6 (SP6) shipped only with Windows Server 2003
4.0.7328.0 Service Pack 7 (SP7)
4.0.8015.0 Service Pack 8 (SP8)
4.0.8618.0 Security Bulletin MS04-014
4.0.9025.0 Update Rollup 1 for Windows 2000 SP4

The MSJET40.dll version in our sql server is wrong?

Thanks a million for your help!


quote:
Originally posted by madhivanan

See if this helps
http://www.kbalertz.com/kb_818182.aspx

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 01/10/2006 :  01:29:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You should look for that dll where you are accessing the EXCEL file

Madhivanan

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

anglianthon
Starting Member

18 Posts

Posted - 01/22/2006 :  20:13:14  Show Profile  Reply with Quote
Hi, I'm new to SQL. Is this used to transfer data from a SQL table to Excel file??

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

What if I want to transfer the result of a query (run in query analyzer) to Excel??

Thank you
Go to Top of Page

anglianthon
Starting Member

18 Posts

Posted - 01/22/2006 :  20:29:10  Show Profile  Reply with Quote
Hi, I'm new to SQL.

Is this used to export from SQL table to Excel?

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

What if I want to export result of a query (that was run in query analyzer) to Excel?

THank you
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