none
Looping through all the sheets of the excel file where the sheet name changes and sheet order changes

    Question

  • HI All,

    I have excel files which have more work sheets. Point 1 : The excel sheet names will changes daily

    2 : The excel sheet order will vary 

    3 : We have to load the particular name worksheet in particular table only. Need help on this. two days time given. Can anyone help with the concepts and script task code if any. I don't know to code script task only SSIS i known.

    Friday, May 11, 2012 12:02 PM

Answers

  • this link will help you in getting the excel files what ever the names and excel sheet names what ever the sheet name.

    But you mentioned ......"We have to load the particular name worksheet in particular table only" ......

    your answer is

    1- if its a fix name and wont change , use the link and set the SHEET TAB variable to the sheet name and set the MUTLI SHEET = FALSE

    2- if the sheet names changes but the COLUMN names doesn't (the column names can move arround, but not change)

    you will have to add more to the link to capture the column and check if the exist if not go to next sheet

    something like

      If Dts.Variables("uVar_ColumnFieldSourceFlagCheckColumn").Value = True Then
    
                                ''-------------------------------------------------------------------------------------
                                Dim columnsInTable As DataTable
                                Dim columnInTable As DataRow
                                Dim columnRestrictions(3) As String '= (Nullable, Nullable, CurrentSheet , nullable )
                                columnRestrictions(2) = CurrentSheet ' this will restrict the columns list to the current sheet only
    
                                Dim strCurrentColumnFieldSourceFieldNames As String = ""
                                columnsInTable = oledbExcelConnection.GetSchema("COLUMNS", columnRestrictions)
    
                                Dim i As Integer = columnsInTable.Rows.Count
                                Dim strTemp As String = ""
    
                                For Each columnInTable In columnsInTable.Rows
                                    'If columnInTable.Item("TABLE_NAME").ToString = CurrentSheet Then
                                    strTemp = columnInTable.Item("COLUMN_NAME").ToString & ","
                                    If InStr(strColumnFieldSourceFieldNames, strTemp) Then
                                        'strCurrentColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames & strTemp
                                        strCurrentColumnFieldSourceFieldNames &= strTemp
                                        'MsgBox(currentColumn)
                                    End If
                                Next
    
                                strColumnFieldSourceFieldNames = SortColumnFieldsInAString(strColumnFieldSourceFieldNames)
                                strCurrentColumnFieldSourceFieldNames = SortColumnFieldsInAString(strCurrentColumnFieldSourceFieldNames)
    
                                Dim j As Integer = 0
                                If strColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames Then
                                    MsgBox("yes")
                                    'j = 1
                                Else
                                    MsgBox("No")
                                    'j = 0
                                End If
                                ''-------------------------------------------------------------------------------------
                            End If

    and

        LoopForNumberOfRealTables += 1
                            ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1)
                            ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet
                            'MsgBox(CurrentSheet & " --- " & strConnectionString)
                        End If
                    Next
                    oledbExcelConnection.Close()
                Else
                    ReDim Preserve ListOfExcelSheets(0)
                    ListOfExcelSheets(0) = Dts.Variables("uVar_SourceActiveSheetName").Value.ToString
                End If
    
                Dts.Variables("uVar_ArrayOfExcelSheetNames").Value = ListOfExcelSheets
                Dts.TaskResult = ScriptResults.Success
    

    and

     Public Function SortColumnFieldsInAString(ByVal strColumnFieldNames As String) As String
            'Dim strColumnFieldList As String()
            Dim strColumnFieldList As New List(Of String)
            Dim strCurColumnField As String
            Dim FirstPointer As Integer = 0
            Dim NextCommaPosition As Integer = 0
            Dim strLength As Integer = 0
    
            Dim iNumberOfLoops As Integer = CountCharacter(strColumnFieldNames, ",")
    
            For iLoop = 1 To iNumberOfLoops
                NextCommaPosition = strColumnFieldNames.IndexOf(",", FirstPointer)
                strLength = NextCommaPosition - FirstPointer
    
                strCurColumnField = strColumnFieldNames.Substring(FirstPointer, strLength)
                strColumnFieldList.Add(strCurColumnField)
                FirstPointer = FirstPointer + strLength + 1  ' Move pointer forward
            Next
    
            strColumnFieldList.Sort()
    
            strColumnFieldNames = ""
            For iLoop = 1 To iNumberOfLoops
                strColumnFieldNames = strColumnFieldNames & strColumnFieldList(iLoop - 1)
                strColumnFieldNames = strColumnFieldNames & ","
            Next
    
            Return strColumnFieldNames
        End Function
    
        Public Function CountCharacter(ByVal value As String, ByVal ch As Char) As Integer
            Dim cnt As Integer = 0
            For Each c As Char In value
                If c = ch Then cnt += 1
            Next
            Return cnt
        End Function
    End Class


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Ashokaveer Monday, May 14, 2012 6:14 AM
    Friday, May 11, 2012 8:31 PM
  • Hi Koen,

    I did the looping of excel files and to read from all sheets of excel, i want to know how to restrict the table load with the work sheet names that are changing. suppose if i have work sheet name as sheet55$, the sheet can be any position in a excel file, we need to get exact sheet and load in particular table only. similarly we have to do for many sheets to load many tables.

    I dont think you will be able to find the sheet location because the list is provided in a sort order, and we cant control that

    ExcelDataTable = oledbExcelConnection.GetSchema("Tables")
                    SheetCount = ExcelDataTable.Rows.Count
    
                    For Each ExcelSheet In ExcelDataTable.Rows   ' Maybe we can use --->>>>> ????? ExcelDataTable.Rows(0).Item (2)
                        CurrentSheet = ExcelSheet.Item("TABLE_NAME").ToString
                        CurrentSheet = CurrentSheet.Replace("'", "")
    
                        If Right(CurrentSheet, 1) = "$" Then

    I am assuming that you want to load each sheet  to a specific table, if so

    you will need a SCRIPT TASK and SQL table that .

    1- SQL table has the columns of the destination table that can be maped to the excel column

    2- Select all excel column for each sheet one by one and see if ALL the column names exist in which record of the sql table to be able to find the right destination table

    3- then you have to ????? redirect(maybe copy) the file to another folder with the right sheet name (must be passed as a parameter. what ever you have to set/map the right sheet to the right destination table.


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Ashokaveer Monday, May 14, 2012 6:16 AM
    Friday, May 11, 2012 8:38 PM

All replies

  • Loop over Excel tables:

    http://msdn.microsoft.com/en-us/library/ms345182(v=sql.90).aspx

    In the For Each Loop, you create a dataflow for every possible sheet.
    Before you run one of those dataflows, you need to decide which dataflow to run. I assume you can deduce this from the sheetname?


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Friday, May 11, 2012 12:10 PM
  • Hi Koen,

    I did the looping of excel files and to read from all sheets of excel, i want to know how to restrict the table load with the work sheet names that are changing. suppose if i have work sheet name as sheet55$, the sheet can be any position in a excel file, we need to get exact sheet and load in particular table only. similarly we have to do for many sheets to load many tables.

    Friday, May 11, 2012 12:15 PM
  • Not sure what you're getting at.
    As I said, inside the ForEach Loop you need to check which sheet is the current one and which corresponding dataflow you need to run.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Friday, May 11, 2012 12:17 PM
  • this link will help you in getting the excel files what ever the names and excel sheet names what ever the sheet name.

    But you mentioned ......"We have to load the particular name worksheet in particular table only" ......

    your answer is

    1- if its a fix name and wont change , use the link and set the SHEET TAB variable to the sheet name and set the MUTLI SHEET = FALSE

    2- if the sheet names changes but the COLUMN names doesn't (the column names can move arround, but not change)

    you will have to add more to the link to capture the column and check if the exist if not go to next sheet

    something like

      If Dts.Variables("uVar_ColumnFieldSourceFlagCheckColumn").Value = True Then
    
                                ''-------------------------------------------------------------------------------------
                                Dim columnsInTable As DataTable
                                Dim columnInTable As DataRow
                                Dim columnRestrictions(3) As String '= (Nullable, Nullable, CurrentSheet , nullable )
                                columnRestrictions(2) = CurrentSheet ' this will restrict the columns list to the current sheet only
    
                                Dim strCurrentColumnFieldSourceFieldNames As String = ""
                                columnsInTable = oledbExcelConnection.GetSchema("COLUMNS", columnRestrictions)
    
                                Dim i As Integer = columnsInTable.Rows.Count
                                Dim strTemp As String = ""
    
                                For Each columnInTable In columnsInTable.Rows
                                    'If columnInTable.Item("TABLE_NAME").ToString = CurrentSheet Then
                                    strTemp = columnInTable.Item("COLUMN_NAME").ToString & ","
                                    If InStr(strColumnFieldSourceFieldNames, strTemp) Then
                                        'strCurrentColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames & strTemp
                                        strCurrentColumnFieldSourceFieldNames &= strTemp
                                        'MsgBox(currentColumn)
                                    End If
                                Next
    
                                strColumnFieldSourceFieldNames = SortColumnFieldsInAString(strColumnFieldSourceFieldNames)
                                strCurrentColumnFieldSourceFieldNames = SortColumnFieldsInAString(strCurrentColumnFieldSourceFieldNames)
    
                                Dim j As Integer = 0
                                If strColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames Then
                                    MsgBox("yes")
                                    'j = 1
                                Else
                                    MsgBox("No")
                                    'j = 0
                                End If
                                ''-------------------------------------------------------------------------------------
                            End If

    and

        LoopForNumberOfRealTables += 1
                            ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1)
                            ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet
                            'MsgBox(CurrentSheet & " --- " & strConnectionString)
                        End If
                    Next
                    oledbExcelConnection.Close()
                Else
                    ReDim Preserve ListOfExcelSheets(0)
                    ListOfExcelSheets(0) = Dts.Variables("uVar_SourceActiveSheetName").Value.ToString
                End If
    
                Dts.Variables("uVar_ArrayOfExcelSheetNames").Value = ListOfExcelSheets
                Dts.TaskResult = ScriptResults.Success
    

    and

     Public Function SortColumnFieldsInAString(ByVal strColumnFieldNames As String) As String
            'Dim strColumnFieldList As String()
            Dim strColumnFieldList As New List(Of String)
            Dim strCurColumnField As String
            Dim FirstPointer As Integer = 0
            Dim NextCommaPosition As Integer = 0
            Dim strLength As Integer = 0
    
            Dim iNumberOfLoops As Integer = CountCharacter(strColumnFieldNames, ",")
    
            For iLoop = 1 To iNumberOfLoops
                NextCommaPosition = strColumnFieldNames.IndexOf(",", FirstPointer)
                strLength = NextCommaPosition - FirstPointer
    
                strCurColumnField = strColumnFieldNames.Substring(FirstPointer, strLength)
                strColumnFieldList.Add(strCurColumnField)
                FirstPointer = FirstPointer + strLength + 1  ' Move pointer forward
            Next
    
            strColumnFieldList.Sort()
    
            strColumnFieldNames = ""
            For iLoop = 1 To iNumberOfLoops
                strColumnFieldNames = strColumnFieldNames & strColumnFieldList(iLoop - 1)
                strColumnFieldNames = strColumnFieldNames & ","
            Next
    
            Return strColumnFieldNames
        End Function
    
        Public Function CountCharacter(ByVal value As String, ByVal ch As Char) As Integer
            Dim cnt As Integer = 0
            For Each c As Char In value
                If c = ch Then cnt += 1
            Next
            Return cnt
        End Function
    End Class


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Ashokaveer Monday, May 14, 2012 6:14 AM
    Friday, May 11, 2012 8:31 PM
  • Hi Koen,

    I did the looping of excel files and to read from all sheets of excel, i want to know how to restrict the table load with the work sheet names that are changing. suppose if i have work sheet name as sheet55$, the sheet can be any position in a excel file, we need to get exact sheet and load in particular table only. similarly we have to do for many sheets to load many tables.

    I dont think you will be able to find the sheet location because the list is provided in a sort order, and we cant control that

    ExcelDataTable = oledbExcelConnection.GetSchema("Tables")
                    SheetCount = ExcelDataTable.Rows.Count
    
                    For Each ExcelSheet In ExcelDataTable.Rows   ' Maybe we can use --->>>>> ????? ExcelDataTable.Rows(0).Item (2)
                        CurrentSheet = ExcelSheet.Item("TABLE_NAME").ToString
                        CurrentSheet = CurrentSheet.Replace("'", "")
    
                        If Right(CurrentSheet, 1) = "$" Then

    I am assuming that you want to load each sheet  to a specific table, if so

    you will need a SCRIPT TASK and SQL table that .

    1- SQL table has the columns of the destination table that can be maped to the excel column

    2- Select all excel column for each sheet one by one and see if ALL the column names exist in which record of the sql table to be able to find the right destination table

    3- then you have to ????? redirect(maybe copy) the file to another folder with the right sheet name (must be passed as a parameter. what ever you have to set/map the right sheet to the right destination table.


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Ashokaveer Monday, May 14, 2012 6:16 AM
    Friday, May 11, 2012 8:38 PM