PS
Powershell Tutorial Online

Powershell-With-Excel

I worked on csv and xls files extensively, as I worked on middleware technologies like SSIS, Biztalk etc..
There is lots of support cmdlets provided by microsoft for csv in powershell like 'Import-Csv', But I don't find anything for excel, so thought of writing custom function for it.

    
        $FileName = "c:\test.xls"
        $WorksheetName = "Sheet1"
        $strSheetName = $WorksheetName + '$'
        $query = 'select * from ['+$strSheetName+']';
        $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$FileName`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
        $conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
        $conn.open()
        $cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn) 
        $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) 
        $dataTbl = New-Object System.Data.DataTable 
        $dataAdapter.fill($dataTbl)
        $conn.close()
        $myDataRow ="";
        $columnArray =@();
        foreach($col in $dataTbl.Columns)
        {
        $columnArray += $col.toString();
        }
        $returnObject = @();
        foreach($rows in $dataTbl.Rows)
        {
        $i=0;
        $rowObject = @{};
        foreach($columns in $rows.ItemArray){
            $rowObject += !@{$columnArray[$i]=$columns.toString()};

            Write-Host $rowObject
            $i++
        } 
        
        }