Transformation between Excel and table.

Displaying records

SELECT *  FROM OpenRowset ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=f:\TestExcel.xls;HDR=NO', [Sheet1$])

Transforming to tables

 

SELECT * INTO Customers2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=f:\xltest.xls', 'SELECT * FROM [Sheet1$]')

• No need to create the table.
• The Customers2 specified is the name of the which will be created and the first column name is the name from which the column name will be created.
• If the table already exist then it will throw an exception that the table already exist.
• The above select the sheet1 which should be succeeded by $ from the xltest.xls excel file and transforms to the table customers2 which will be created dynamically.

 Transforming to tables with particular datatype

 select

cast(code as varchar(100)) as custCode,

cast(code as varchar(100)) as custName

INTO Customers2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=f:\xltest.xls', 'SELECT * FROM [Sheet1$]')

Transforming Content from ms-sql table to excel

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=f:\xltest.xls;',

'Select code,name from [Sheet1$]')

SELECT [CustCode], [CustName] FROM Customers2

Note: 

1. Here code and name should be created as the column in the sheet1 of the xltest.xls file.