将 Excel 导入 SQL Server 2008 [英] Import Excel to SQL Server 2008
问题描述
我需要创建一个流程来将多选项卡的 Excel 电子表格导入 SQL Server 2008R2.每个选项卡将是数据库中的不同表.这需要每周完成一次，并且导入应该是自动化的.理想情况下，我想将电子表格弹出到一个文件夹中 [或让一些实习生来做] 并让 sql 运行一个在此文件夹中查找的过程，并将数据添加到此数据库中的表中.我还想有另一个表来跟踪进口和日期戳他们.我真的不知道从哪里开始，因为在 tsql 方面我是一个非常大的菜鸟.
I need to create a process to import a multi tabbed excel spreadsheet into SQL Server 2008R2. Each tab will be a different table in the database. This will need to be done weekly and imports should be automated. Ideally I want to pop the spreadsheet into a folder [or have some intern do it] and have sql run a procedure that looks in this folder, and adds the data to the tables in this db. I would also like to have another table that tracks the imports and date stamps them. I really have no idea where to even start here as I'm a pretty huge noob when it comes to tsql.
推荐答案
如果您仅限于 TSQL，以上两个答案将向您展示一些想法.如果您可以使用 SSIS 访问 Data Tools 或 Business Intelligence，则可以在假设 Excel 工作簿中的每个工作表每次都匹配.使用 SSIS，您将使用数据流任务，并且每个工作表都将被导入到您想要的表格中.下周准备好该文件时，将其放入文件夹并运行 SSIS 包.
If you're limited solely to TSQL, the above two answers will show you some ideas. If you have access to either Data Tools or Business Intelligence, with SSIS, you can automate it with the assumption that each sheet in the Excel workbook matches each time. With SSIS, you'll use a Data Flow task and each sheet will be imported into the table that you want. When you're ready for the file the next week, you'll drop it into the folder and run the SSIS package.
但是，如果工作表名称发生变化(例如，一周工作表被称为 Cats、Dogs、Rain，而下周它是 Sulfur、Fire、Hell)，那么这将导致包装破裂.否则，如果只有工作表中的数据发生变化，那么这可以通过 SSIS 完全自动化.
However, if the sheet names change, (for instance, one week sheets are called Cats, Dogs, Rain and the next week it's Sulfur, Fire, Hell) then this would cause the package to break. Otherwise, if only the data within the worksheet change, then this can be completely automated with SSIS.
示例文章:https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/
这篇关于将 Excel 导入 SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
