Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?
Thanks for your time,
James
EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!
It is unclear.
With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.
You will still have to deal with the potential lack of heterogeneity of Excel.
What if the user give you smashed potatoes?
Regards,
Philippe
|||Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.
What I'm looking to do is this:
I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?
Thanks,
James
|||It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.
You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.
The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "
May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.
I guess I prefer the last approach.
Philippe
|||James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.
