Import/Export 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

To export data from SQL Server table to Excel file, 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',
'Excel8.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',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Published 27 August 2007 16:23 by Madhivanan
Filed under: , , , ,

Comments

# re: Import/Export to Excel

17 January 2008 14:51 by pablo uribe

hi masri999

try this.

there is an space between Excel and 8.0.

SELECT * into table FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\file.xls; HDR=YES', 'SELECT * FROM [Sheet1$]')

# re: Import/Export to Excel

19 May 2008 03:46 by mquinn64

in using the export to Excel script I get the following message

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\testing.xls;HDR=YES',

'SELECT * FROM [Sheet1$]')

select * from cdbLink

WHERE (((cdbLink.extObjType)=11 Or (cdbLink.extObjType)=-1))

error message

Server: Msg 213, Level 16, State 5, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

# re: Import/Export to Excel

19 May 2008 08:41 by Madhivanan

mquinn64,

Make sure the number of columns in excel sheet is same as the table

# Export to EXCEL with column names

10 October 2008 16:14 by Madhivanan

In the post Import/Export to Excel , I showed how to export data to EXCEL The problem that most users

# Export to EXCEL with column names

14 October 2008 11:10 by SQL Server Transact-SQL (SSQA.net)

In the post Import/Export to Excel , I showed how to export data to EXCEL The problem that most users

# how to remove spaces while exporting a table into excel | keyongtech

22 January 2009 04:02 by how to remove spaces while exporting a table into excel | keyongtech

Pingback from  how to remove spaces while exporting a table into excel | keyongtech