Wednesday, July 27, 2005

RE: Import/Export to Excel

Every now and then, I need to send stuff from SQL to Excel....

Apart from using DTS and Import/Export wizard, we can also use this query to export data from SQL Server2000 to Excel and vice versa

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$]')


[Via SQLJunkies Blogs]

2 comments:

  1. Chris Szlooka1/27/2006 3:49 PM

    Not sure how to get a hold of you outside this comment mechanism. Is there a way to contact you to talk about this? I can be reached at chrissz__AT__bigfoot__DOT__com.

    ReplyDelete
  2. Hi Chris,

    I sent you an email to the address that you had listed.

    ReplyDelete

Note: Only a member of this blog may post a comment.