Want to insert excel file data into a table using a SIS format problem

Question

I created the SSIS package, I took the excel source, taking the excel file, but my problem is: I need to take the B7 value from the excel file and paste it into the database table, please help, because

B7 values ​​are generated from the expression: = MAX ($ B $ 32: C284)

hi I want to accept date ie '8/26/2011' , and NAV ie '93737452.52'

and want to paste into a table, I use 'excel source ' to connect to a file other than Excel. Select 'Data access mode' in 'Excel source editor' as 'SQL command'

Now, which command do I need to write in order to get the value of 'date' and 'NAV' , since I pasted the image of the excel file, please let me know the next steps.

Answer 1

Since you only need 2 very specific cell values ​​from a spreadsheet, I would suggest using the Script Component to get values ​​only from these cells, instead of using an Excel source. Excel Source is really designed to get many rows and columns that are laid out sequentially, and not to select individual cells.

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

Then edit this script component and select the Inputs and Outputs from the leftmost column and add the 2 outputs you need and set the correct data type for them.

Then click the script button, select your language (in this case, I chose Visual Basic 2008), and then click the Edit Script button.

When the IDE appears, add a link to Microsoft.Office.Interop.Excel and add the Imports statement for this at the top of your script. Then, in the CreateNewOutputRows subroutine CreateNewOutputRows get a link to your workbook and worksheet and print the values ​​from the specific cells that you want to use using the AddRow() method. As you can see from the code below, I get the values ​​directly from cells b7 and c7 and convert them to the desired type.

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

The script will read only the 2 values ​​you want from the cells you specify and provide them with the purpose of the data stream.

Although this may look a lot more than just dropping the Excel source in the data stream, it gives you much more flexibility and control.

Answer 2

If the file you have can be changed, you can give a name to cells B7 and D7 (for example, you can choose the names "Date" and "NAV"). To do this, you need to right-click the cell and click "Range Name".

Once you do this, you can select these names ("Date" and "NAV") as if they were sheets of your excel file. Thus, you can make select * from FROM [Date$] or will be available as another sheet if you select the data access mode as "Table or View".

Answer 3

If the file format is not fixed (e.g. leading empty rows / columns), I suggest you combine the column names into one field and remove the empty elements to make it easier for SSIS.

If the file should support the existing format, change the data access mode in the original Excel object to the SQL command, and then use something similar to the following to exclude empty lines;

 SELECT F2, F3 FROM [Sheet1$] WHERE (F2 IS NOT NULL) AND (F2 <> 'USD P&L' and F2 <> 'Date') 

You will need to use a data conversion control to understand the resulting data types.

Answer 4

Could you make Excel save () and choose the .csv format?

I believe that this will only save the values.