locked
How to send Excel filename as Tablename for each time ? RRS feed

  • Question

  • Hi All,

    How to read multiple Excel files which are having different structures from  from ssis.

    each excel file  contains only one sheet. and with different columnnames.

    I have to import each excel file as separet

    table. and the table name should be same as excel filename.

    Could you please send me the examples for the same,your help will be appreciated.

    Thanks,

    satheeswar reddy.




    Thanks & Regards, Anil

    Wednesday, February 8, 2012 11:41 AM

All replies

  • Anil,

    you may decide to use OPENROWSET to grab the data from a relatively large range and dump the data right into the SQL Server, to capture the file name you may want to use a ForEach Loop container set to a file mask like *.xls and then each file will need to be captured into a variable that you can use to construct the insert SQL clause dynamically usign a SSIS expression.


    Arthur My Blog

    Tuesday, February 14, 2012 3:47 AM
  • How to read multiple Excel files which are having different structures from  from ssis.

    each excel file  contains only one sheet. and with different columnnames.

    I have to import each excel file as separet

    table. and the table name should be same as excel filename.

    Could you please send me the examples for the same,your help will be appreciated.

    so your tables are in a DB and they are not been made on the fly? is that right?

    if your answer is yes this is what youhave to do

    i.e assuming that you have 3 tables ( tbl1, tbl2 , tbl2 ) and 3 different excel (exA, exB, exC)

    what i would do is make 3 folder for each ETL and within each folder i would have a folder called ToBeProcessed and will try to get it working, here is this example, it does not care what the file name is or the excel sheet name , AS LONG AS it has the right column names in the sheet

    then i would make a 4th package, and by using a SQL table that has the COLUMN names and the ToBeProcessed FOLDER for EACH excel file i would MOVE the file to the right ToBeProcessedFolder

    i.e  U_VarExcelColumnForTbl1 = EventDate,UserID,FName,etc....

    To capture the COLUMN names in a Excel file you will need a code like ... in a SCRIPT TASK

    YOU HAVE TO MODIFY THE CODE

      strConnectionString = Dts.Variables("uVar_SourceConnectionString").Value.ToString
                    oledbExcelConnection = New OleDbConnection(strConnectionString)
                    oledbExcelConnection.Open()
                    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
                            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
                            LoopForNumberOfRealTables += 1
                            ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1)
                            ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet
                            'MsgBox(CurrentSheet & " --- " & strConnectionString)
                        End If
                    Next
                    oledbExcelConnection.Close()


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


    Wednesday, February 29, 2012 8:14 PM