SQL Sever read Excel Summary "Go"

The main use of the Openrowset,opendatasource system functions, either of these two functions can complete the task

This approach enables you to import and export between Excel and SQL Server tables.

The writing of OPENROWSET

--3, link Excel Microsoft Jet 4.0 OLE DB Provider--Read Excel data, note that Excel must be closed in advanceSelect*FromOpenRowset‘Microsoft.Jet.OLEDB.4.0‘,‘Excel 8.0; Hdr=yes;database=e:/temp/myexcel20131212.xls‘, sheet1$)-- write data to Excel2003 file --< Span style= "color: #008080;" > > Note: First, you need to set some values that are equal to the retrieved columns for the first line of Excel's Sheet1, which can be any value, --insert into openrowset (  Microsoft.jet.oledb.4.0 ",   ' select * from Dbo. Dquestiondata               

The writing of OpenDataSource

Fromopendatasource ('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;database=e:/temp/myexcel20131212.xls') ... sheet1$      

The above code is a link Excel2003 , and for the Excel2007 link, the link will be different because OLE DB provider is different.

The following is a different way of linking, also found on the Internet.

--> Jet engine access to Excel 97-2003Select*FromOpenRowSet (‘Microsoft.Jet.OLEDB.4.0‘,‘Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘,‘SELECT * FROM [sheet1$]‘)Select*FromOpenRowSet (‘Microsoft.Jet.OLEDB.4.0‘,‘Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘,[sheet1$])Select*FromOpenDataSource (‘Microsoft.Jet.OLEDB.4.0‘,‘Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘)...[sheet1$]Select*FromOpenDataSource (‘Microsoft.Jet.OLEDB.4.0‘,‘Data Source=d:/97-2003.xls; Extended properties= "Excel 8.0; Hdr=yes;imex=1 "‘)...[sheet1$]--> ACE engine access to Excel 97-2003Select*FromOpenRowSet (‘microsoft.ace.oledb.12.0‘,‘Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘,‘SELECT * FROM [sheet1$]‘)Select*FromOpenRowSet (‘microsoft.ace.oledb.12.0‘,‘Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘,[sheet1$])Select*FromOpenDataSource (‘microsoft.ace.oledb.12.0‘,‘Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls‘)...[sheet1$]Select*FromOpenDataSource (‘microsoft.ace.oledb.12.0‘,‘Data Source=d:/97-2003.xls; Extended properties= "Excel 12.0; Hdr=yes;imex=1 "‘)...[sheet1$]--> ACE engine access to Excel 2007Select*FromOpenRowSet (‘microsoft.ace.oledb.12.0‘,‘Excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx‘,‘SELECT * FROM [sheet1$]‘)Select*FromOpenRowSet (‘microsoft.ace.oledb.12.0‘,‘Excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx‘,[sheet1$])Select*FromOpenDataSource (‘microsoft.ace.oledb.12.0 ",  excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx ") ... [sheet1$ select * from  ' microsoft.ace.oledb.12.0 ',  ' data source=d:/2007.xlsx; Extended properties= "Excel 12.0; Hdr=yes;imex=1 "") ... [sheet1$   

SQL Sever read Excel Summary "Go"