从xls（Excel）文件创建一个SQL表 [英] Creating a SQL table from a xls (Excel) file

问题描述

我正在尝试将Excel文档转换为SQL 2005中的表。我发现下面的链接，我想知道它是否像解决方案。如果是这样，那么@excel_full_file_name的语法和路径将相对于哪里？

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?

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

推荐答案

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

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.

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

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

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

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

这真的取决于你想要多少控制和灵活性SSIS路线将优于这些方法。

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

这篇关于从xls（Excel）文件创建一个SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！