Handling Unstructured Data from Excel in SSIS

Question

I need help with Handling Unstructured Data from Excel. The merged cell data needs to be splitted and set to the respective columns in this case. I have attached the source and target. The Merged Data need to be unmerged and the data need to be populated to those unmerged columns. In SSIS when i imported this excel it unmerges and places the data in the first column then leaves the other cell as NULL.

Source: tbl_e1 - Source
Target: tbl_e2 Target
target to be loaded in SSMS.

Detailed Explanation of Requirement:

My Excel Source which has vertically merged cells on Dept1,Dept2 and horizontally merged cells on row2 and row3 on dept2 column as you can see which has the value Banking. similarly row2 and row3 on value column as you can see which has the value 20000. Note that when i imported this source in ssis I can see that this is how it arranges things,

ID	Name	Dept1	Dept2	Value
1	Harish	HR	NULL	10000
2	Kano	Finance	Banking	20000
3	Tangiro	HR	NULL	NULL

I want to populate the unmerged cells with the data which was inside those cells when it was in merged state (ie. Source).

I want the horizontally merged cells Dept1 and Dept2 of ID 1 to have HR as value in Dept1 and Dept2.

For the Vertically merged cells on Dept2 column for ID 2 and ID 3 I want them to have Banking in Dept2 for Both Id2 and Id 3. Similarly for Column Value which has Vertically Merged cells on ID 2 and ID 3 I want them to have 20000 in Value for Both Id2 and Id 3.

I thought of trying this using the window functions which is not working and other logical things with script task.

You need a completely scripted solution for this. SSIS is not the best platform for a completely scripted solution. I suggest using something standalone and debuggable like a C# console app or Powershell. — Nick.Mc, Apr 10 '23 at 12:12
I think you're kinda smoked since for cell D4, you don't even know if you should copy from cell C4 or D3. Well, maybe you know in this example, but it is always like this. Stop merging cells that are used for *input* — siggemannen, Apr 10 '23 at 15:52

Alan Schofield · Answer 1 · 2023-04-11T10:19:00.237

You need to clean the file before attempting to do anything with it in SSIS, it's much simpler to fix it first.

The following Excel VBA sub will find and replace all merged cells by unmerging them and then replacing them with the same value as the source cell.

Sub UnmergeCellsAndDuplicateValues()
    
    Dim ws As Worksheet
    Dim r As Range
    Dim RangeAddress As String
    Dim CellValue

    For Each ws In Worksheets
        ws.Activate
        For Each r In ws.UsedRange
            If r.MergeCells Then
               RangeAddress = r.MergeArea.Address
               CellValue = r
               r.MergeCells = False
               Range(RangeAddress) = CellValue
            End If
        Next
    Next ws
    
End Sub

You could probably build a small executable that takes a filename as a parameter, opens the workbook and runs this VBA over it but I've not done that as I rarely use it. However, that would mean you could use this in your SSIS workflow , I suppose it depends on how often this will need to be run and/or will you need to run batches of files etc...

Handling Unstructured Data from Excel in SSIS

1 Answers1