从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屋!