从xls（Excel）文件创建SQL表

I'm trying to convert an Excel document into a table in SQL 2005. I found the link below and am wondering if it looks like a solution. If so, what would the @excel_full_file_name syntax be and where would the path be relative to?

我正在尝试将Excel文档转换为SQL 2005中的表。我找到了下面的链接，我想知道它是否看起来像一个解决方案。如果是这样，@ excel_full_file_name语法是什么以及路径相对于何处？

http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-convert-Excel_to_table.html

4 个解决方案

#1

You can use the BULK INSERT T-SQL command if you just want a pure sql solution. You have to save the file as csv/text first.

如果您只想要一个纯sql解决方案，可以使用BULK INSERT T-SQL命令。您必须先将文件另存为csv / text。

BULK 
INSERT YourDestinationTable
        FROM 'D:\YourFile.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

Alternatively, you can try OPENROWEST - again , a pure T-SQL solution.

或者，您可以尝试OPENROWEST - 再次，一个纯T-SQL解决方案。

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=D:\YourExcelFile.xls', 'Select * from YourExcelFile')

It really depends on how much control and flexibility you want, the SSIS route will have benefits over these methods.

这实际上取决于您想要多少控制和灵活性，SSIS路线将优于这些方法。

#2

Glancing over the code, I would expect it to be the full path name of the excel document:

浏览代码，我希望它是excel文档的完整路径名：

For example: c:\path\to\my\excel\document.xls

例如：c：\ path \ to \ my \ excel \ document.xls

I haven't installed the procedure though or run it, so I could be wrong - but that's what it appears to be at first glance.

我没有安装程序或运行它，所以我可能是错的 - 但这似乎是乍一看。

#3

I would suggest using an SSIS/DTS Package, to convert. It's much easier.

我建议使用SSIS / DTS包进行转换。这更容易。

SSIS Excel example

SSIS Excel示例

** note that this example is using the wizard. you can schedule the SSIS/DTS package as a job to run, on your SQL box.

**请注意，此示例使用向导。您可以在SQL框上安排SSIS / DTS包作为要运行的作业。

#4

This example copies data from SQL to Excel. But it is just a matter of swapping the OleDb providers to get it to work in the opposite direction.

此示例将数据从SQL复制到Excel。但这只是交换OleDb提供商以使其在相反方向上工作的问题。

#1