Wed. Jun 8th, 2022
Function Import-Excel
    param ([string]$FilePath)
    #Create an Object Excel.Application using Com interface
    $ExcelObject = New-Object -ComObject Excel.Application
    $ExcelObject.Visible = $false
    #Open WorkBook
    $WorkBook = $ExcelObject.Workbooks.Open($FilePath)
    #Load the First work sheet - You can use Sheet name as: $workBook.Sheets.Item("Sheet Title")
    $WorkSheet = $WorkBook.Sheets.Item(1)
    #Select the range of data used
    $Columns = $WorkSheet.UsedRange.Columns.Count
    $Rows = $WorkSheet.UsedRange.Rows.Count
    #Get Column Headers from Excelsheet as Fields in SharePoint List
    $Fields = @()
    For($Column=1; $Column -le $Columns; $Column++) 
        #Get the Value from Excel Sheet
        $FieldName = $WorkSheet.Columns.Item($Column).Rows.Item(1).Text
        $Fields += $FieldName

    #Get All rows from the Excelsheet
    $DataCollection =@()
    For($Row=2; $Row -le $Rows;$Row++)
        $Data = New-Object PSObject
        #Iterate through columns of the row
        For($Column=1; $Column -le $Columns; $Column++)
            #Get the Value from Excel Sheet
            $Value = $WorkSheet.Columns.Item($Column).Rows.Item($Row).Text
            #Frame Data
        if($Fields[$Column-1] -ne "")
           $Data | Add-Member -MemberType noteproperty -Name $Fields[$Column-1] -Value $Value
            $MetaDataColName = "Metadata"+$h
            $Data | Add-Member -MemberType noteproperty -Name $MetaDataColName -Value $Value
        $DataCollection +=$Data
    #Close Objects
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) | Out-null
    Return $DataCollection

This website uses cookies. By continuing to use this site, you accept our use of cookies.