如何从Excel转换和插入到SQL Server [英] how to convert and insert from excel to sql server

问题描述

我在excel工作表中有很多记录，如何将所有这些记录插入sql表?

就像下面的编码用法一样

i am having so much of records in excel sheet, how to insert these all records into sql table?

like following coding use for it

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

//我正在使用的位置是什么------ [Microsoft.jet.OLEDB.4.0]实际上我没有使用OLEDB CONNECTION，而是使用了SQL Connection.

请检查并向我解释.

未选中的选项:将我的内容作为纯文本而不是HTML对待" [/EDIT]

//what i am using this place-----[Microsoft.jet.OLEDB.4.0] actually i am not using OLEDB CONNECTION, I used SQL Connection.

please check and explain me.

Unchecked option: "Treat my content as plain text, not as HTML"[/EDIT]

推荐答案

SELECT * INTO XLImport4 FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0'， ' Excel 8.0; Database = C:\ test \ xltest.xls'，[客户

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers

) 选择 * INTO XLImport5 FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0' ， ' Excel 8.0; Database = C:\ test \ xltest.xls'，选择*来自[Customers

) SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers

')

//我正在使用的位置是什么------ [Microsoft.jet.OLEDB.4.0]实际上我没有使用OLEDB CONNECTION，而是使用了SQL Connection.

请检查并向我解释.

未选中的选项:将我的内容作为纯文本而不是HTML对待" [/EDIT]

//what i am using this place-----[Microsoft.jet.OLEDB.4.0] actually i am not using OLEDB CONNECTION, I used SQL Connection.

please check and explain me.

Unchecked option: "Treat my content as plain text, not as HTML"[/EDIT]

这篇关于如何从Excel转换和插入到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！