Mon. Jan 3rd, 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++)
    {
        $h=0
        $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
        }else
        {
            $h++
            $MetaDataColName = "Metadata"+$h
            $Data | Add-Member -MemberType noteproperty -Name $MetaDataColName -Value $Value
        }
        }
        $DataCollection +=$Data
    }
 
    #Close Objects
    $WorkBook.Close()
    $ExcelObject.Quit()
    [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.