有没有一种方法可以从excel特定单元格收集数据以发送到SQL Server? [英] Is there a way to collect data from excel specific cells to send to SQL Server?

问题描述

我有一个模板excel表格，希望用户每天填写.一张纸告诉我哪些单元格是可读写的(意味着我想将哪些单元格发送到数据库).该形式不是管状的.有时会设置数据(A3-> A4)或(A3-> B3).

I have a template excel sheet that I want users to fill out daily. One sheet tells me what cells are read/write (meaning which ones I want to send to my data base). The form is not tubular ie. sometimes data is setup (A3 -> A4) or (A3-> B3).

我想知道是否有一个excel插件，或者我可以以某种方式读取某些单元格，然后在表单完成后将它们发送到我的数据库中.

I was wondering is there a excel add on or any way I can read certain cells then send them to my db after the form has been completed.

我研究了Excel文档.上面写着:使用以下工具之一，将数据直接从Excel直接导入到SQL中:SQL Server导入和导出向导SQL Server集成服务(SSIS)OPENROWSET功能您可以分两个步骤导入数据，方法是将数据从Excel导出为文本，然后使用以下工具之一导入文本文件:导入平面文件向导BULK INSERT语句BCP复制向导(Azure数据工厂)Azure数据工厂我也研究了VBA.

I have looked into Excel documentation. which says: import data in a single step, directly from Excel to SQL, by using one of the following tools: The SQL Server Import and Export Wizard SQL Server Integration Services (SSIS) The OPENROWSET function You can import data in two steps, by exporting your data from Excel as text, and then using one of the following tools to import the text file: The Import Flat File Wizard The BULK INSERT statement BCP The Copy Wizard (Azure Data Factory) Azure Data Factory I also have looked into VBA a bit.

其中之一会是最好的方法吗?

Would one of these be the best path?

任何建议都值得赞赏.

我尝试将SQL Server导入和导出向导与SQL Server集成服务(SSIS)但是我的模板没有遵循我无法控制的规范化表格布局.

I have tried to use The SQL Server Import and Export Wizard with SQL Server Integration Services (SSIS) But my templates dont follow a normalized table layout, which I cannot control.

我试图使用内置在JavaScript API中的Microsoft Excel，但是我找不到将服务器端语言连接到它的方法.

I have tried to use Microsoft Excels built in JavaScript API's but I couldn't find a way to connect a sever side language to it.

推荐答案

您可以通过两种方法来实现:

You can achieve that in two approaches:

在Excel Source编辑器中，将Access模式更改为SQL Command，并在工作表名称后指定范围，例如:

In the Excel Source editor, change the Access mode to SQL Command and specify the range after the sheet name, as example:

SELECT * FROM [Sheet1$A3:A4]

• 从Excel文件中忽略列导入到SQL Server时

您可以使用脚本组件作为源，并且在脚本内可以使用Interop.Excel.ddl程序集读取Excel单元格并生成所需的输出:

You can use a Script Component as Source and within the Script you can use Interop.Excel.ddl assembly to read the Excel cells and generate desired output:

有用的链接

• SSIS脚本组件作为源

这篇关于有没有一种方法可以从excel特定单元格收集数据以发送到SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！