想要使用ssis格式问题将excel文件数据插入到表中 [英] Want to insert excel file data into table using ssis - format problem

问题描述

我已经创建了一个SSIS包，我使用'excel源'采取excel文件，但我的问题是我需要从Excel文件中获取B7值，并将其插入数据库表请帮助，因为

I have created an SSIS package i took 'excel source' taking excel file but my problem is I need to take B7 value from excel file and insert it into database table please help because

B7值是从表达式生成的：= MAX（$ B $ 32：C284）

B7 values is generated from expression : =MAX($B$32:C284)

我想要获取 date ie '8/26/2011'，值 NAV ie '93737452.52'

hi I want to take value of date i.e '8/26/2011' , and value of NAV i.e '93737452.52'

并希望插入到表中，我使用'excel source '现在选择excel文件连接在'Excel源代码编辑器'中选择数据访问模式 as 'SQL command'

and want to insert into table i'm using 'excel source ' to take excel file made connection now Select 'Data access mode' in 'Excel source editor' as 'SQL command'

现在我需要写什么命令来获取'date'和'NAV'值，因为我已经粘贴了excel文件的图片，请让我知道进一步的步骤plese。

now what command I need to write to get 'date' and 'NAV'value as I have pasted picture of excel file please let me know the further steps plese .

推荐答案

由于您只需要电子表格中的2个非常特定的单元格值，建议使用脚本组件而不是使用Excel源。 Excel Source真的是为了获得一堆行&为了执行此操作，首先从数据中拖动一个脚本组件。

Since you only need 2 very specific cell values from the spreadsheet, I would suggest using a Script Component to get the values from just those cells instead of using an Excel Source. Excel Source is really made for getting a bunch of rows & columns that are laid out consistently, not for picking individual cells.

流程转换工具箱部分。当选择脚本组件类型对话框出现时，请检查第一个选项源。

To do this, first drag a Script Component from the Data Flow Transformations section of toolbox. When the select script component type dialog box appears, check the first option, "Source".

接下来，编辑此脚本组件，然后选择 Inputs&输出，并添加您需要的2个输出列，并为每个列设置正确的数据类型。

Next, edit this script component and pick Inputs & Outputs from the leftmost column and add the 2 output columns that you will need and set the correct data type for each.

然后点击脚本选项，选择你的语言Visual Basic 2008），然后单击编辑脚本按钮。

Then click the Script option, choose your language (I picked Visual Basic 2008 in this case) and then click the Edit Script button.

当IDE出现时，添加引用 Microsoft.Office.Interop.Excel 并在脚本的顶部添加一个Imports语句。然后在 CreateNewOutputRows 例程中，获取对您的工作簿和工作表，并输出要使用 AddRow（）方法的特定单元格的值。从下面的代码可以看出，我正在从单元格b7和amp; c7并将其转换为正确的类型。

When the IDE comes up, add a reference to Microsoft.Office.Interop.Excel and add an Imports statement for this at the top of your script. Then in the CreateNewOutputRows routine, get a reference to your workbook & worksheet and output the values from the specific cells that you want using the AddRow() method. As you can see from the code below, I am getting the values directly from cells b7 & c7 and converting them to the proper type.

保存脚本并退出IDE，现在可以使用此脚本组件作为数据库插入的源：

Save your script and exit the IDE and you can now use this script component as your source for your database insert:

脚本将从您指定的单元格中读取所需的2个值，并将它们提供给数据流目标。

The script will read just the 2 values you want from the cells you specify and provide them to the Data Flow Destination.

虽然这可能看起来更像是更多的工作而不仅仅是在数据流中放置一个Excel数据源，它可以让您更加灵活和控制。

While this may look like a lot more work than just dropping an Excel Source in the Data Flow, it gives you much more flexibility and control.

这篇关于想要使用ssis格式问题将excel文件数据插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！