How to read cell data in excel and output to command prompt

Answer 1

I find OLEDB and the Office 2007 drivers a little easier to use. If you don't have Office 2007, ou can download the drives from MS.

$filepath = 'C:\Users\u00\documents\backupset.xlsx'
#Choose connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
#Connection String for Excel 2003:
#$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
$qry = 'select * from [sheet1$]'

    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) 
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dt = new-object System.Data.dataTable 
    [void]$da.fill($dt)
    $conn.close()
    $dt

Answer 2

Hi All,

thanks for your help...I have fixed the problem! I was using PowerShell V2 CTP2, now I have upgraded to CTP3 this has fixed the language barrier problem and I can now run scripts without worrying about the invoke function and all is fine!

Many Thanks

Max