Powershell Tutorial Online


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)
        $cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn) 
        $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) 
        $dataTbl = New-Object System.Data.DataTable 
        $myDataRow ="";
        $columnArray [email protected]();
        foreach($col in $dataTbl.Columns)
        $columnArray += $col.toString();
        $returnObject = @();
        foreach($rows in $dataTbl.Rows)
        $rowObject = @{};
        foreach($columns in $rows.ItemArray){
            $rowObject += [email protected]{$columnArray[$i]=$columns.toString()};

            Write-Host $rowObject

Copy and Try it