Quelltext der Seite Powershell OLE DB

Mit Powershell kann man Office Dateien bearbeiten. Das geht mit Access und Excel.<br>
Hier im Beispiel ist es Excel 2003<br>
Es geht eigentlich eher um die Verwendung von MS Jet 4.0 Provider bzw. Microsoft Office 12.0 Access Database Engine OLE DB Provider<br>

 ###### NUR 32BIT ######
 $ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\NeueExcelDatei.xls;Extended Properties='Excel 8.0;HDR=Yes';"
 $Connection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString
 $Connection.Open()
 $cmd = New-Object System.Data.OleDb.OleDBCommand
 $cmd.Connection = $Connection
 $cmd.CommandText = "CREATE TABLE Sheet1 (Sno Int, Employee_Name VARCHAR, Company VARCHAR, Date_Of_joining DATE, Stipend DECIMAL, Stocks_Held DECIMAL)"
 $cmd.ExecuteNonQuery()
 $cmd.CommandText = "INSERT INTO Sheet1 (Sno, Employee_Name, Company,Date_Of_joining,Stipend,Stocks_Held) values ('1', 'Siddharth Rout', 'Defining Horizons', '20/7/2014','2000.75','0.01')"
 $cmd.ExecuteNonQuery()
 $cmd.Connection.Close()
 $Connection.Close()


 ###### NUR 32/64BIT abhängig von Access Runtime ######"
 $ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\NeueExcelDatei64bit.xls;Extended Properties='Excel 8.0;HDR=Yes';"
 $Connection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString
 $Connection.Open()
 $cmd = New-Object System.Data.OleDb.OleDBCommand
 $cmd.Connection = $Connection
 $cmd.CommandText = "CREATE TABLE Sheet1 (Sno Int, Employee_Name VARCHAR, Company VARCHAR, Date_Of_joining DATE, Stipend DECIMAL, Stocks_Held DECIMAL)"
 $cmd.ExecuteNonQuery()
 $cmd.CommandText = "INSERT INTO Sheet1 (Sno, Employee_Name, Company,Date_Of_joining,Stipend,Stocks_Held) values ('1', 'Siddharth Rout', 'Defining Horizons', '20/7/2014','2000.75','0.01')"
 $cmd.ExecuteNonQuery()
 $cmd.Connection.Close()
 $Connection.Close()