none
Import Multiple Excel Into Sql Server RRS feed

  • Question

  • I have 4 Excel spreadsheets all with different formatting and column names that I want to use powershell to import into ONE Sql Server Table that I will want to create with powershell.  I have found several instances of importing one workbook, but I haven't found one where you can import multiple workbooks into the same table.  Can someone show sample code for a reference on how to import multiple spreadsheets into the same table with powershell please?  I found this link which looks like a GREAT starting point, but unfortunately it is only showing how to do one table, and I am WAY new to powershell so can't tweak it on my own to set it to import multiple

    http://www.sqlserver-dba.com/2013/01/sql-server-export-excel-data-to-sql-server-with-powershell.html

    Wednesday, June 4, 2014 1:17 AM

All replies

  • Hi IndigoMontoya,

    As a workaroud, how about merge mutiple excel files into one file, then import this excel file to sql table?

    To merge excel files, please refer to this script:

    $Files = 'd:\merge1.xlsx','d:\merge2.xlsx'
    #Launch Excel, and make it do as its told (supress confirmations)
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False
    
    #Open up a new workbook
    $Dest = $Excel.Workbooks.Add()
    
    #Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
    ForEach($File in $Files[0..4]){
        $Source = $Excel.Workbooks.Open($File,$true,$true)
        If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
            [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
            [void]$Dest.Activate()
            [void]$Dest.ActiveSheet.Range("A1").Select()
        }Else{ #If there is data go to the next empty row and select Column A
            [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
            [void]$Dest.Activate()
            [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
        }
        [void]$Dest.ActiveSheet.Paste()
        $Source.Close()
    }
    $Dest.SaveAs("d:\merge3.xlsx",51)
    $Dest.close()
    $Excel.Quit()

    Reference from:

    How to use powershell to copy several excel worksheets and make a new one?

    I hope this helps.

    Thursday, June 5, 2014 7:40 AM
    Moderator
  • The problem I hit is that field mappings do not mirror between work book and workbook.  So for example what might be employee name in one book is ename in another book.  Is there a way to work around this also?
    Friday, June 6, 2014 9:17 PM