#40 : Reading, Updating and Inserting Data into Excel with Powershell

We often find ourself into a situation when we have to create or write into Excel sheet. This tasks can be done with two ways -
1. Using Excel COM object
2. Using Jet Engine

Using COM object is fairly simple and anyone can copy and paste from a list of sources. The drawback of using Excel COM object is that you need to have Excel application installed on the machine. But this condition is not applicable to production servers where mostly Excel and other Microsoft office tools are not installed due to security threats. Basically you will find them installed on your workstations. Considering these points I was looking for a good method to connect to Excel and update some records in Excel sheet.

Without wasting your time and mine too, let's look into code which can do it for you. I know, if you are good programmer, you don't have to break your head much. If you are a beginner, this blog is not that suitable (I must say). I am busy person and I don't have this only job (Sorry).

1. Sample Code to read data from Excel sheet -

Testing Preparations:
1. Create an Excel document. (If you don't have Microsoft Office in your workstation, you may ask your IT dept. to install OpenOffice or LiberOffice).
2. On first row of sheet as Name and insert some records-
Name
Som
Manu
Ravi
Akash

3. Save and close the doc. I assume, you saved it as F:\test.xls.

Code to Read data from Excel Sheet-

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Select * from [$strSheetName]"

$strQuery = "Select * from [$strSheetName]"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$DataReader = $sqlCommand.ExecuteReader()

While($DataReader.read())
{
$ComputerName = $DataReader[0].Tostring() 
echo $ComputerName 
}  
$dataReader.close()
$objConn.close()

Code to Update data in Excel Sheet-
(Below code will update all names with Som)

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Update [$strSheetName] set Name = 'Som' "
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$sqlCommand.ExecuteNonQuery()
$objConn.Close()

Code to Insert data in Excel Sheet-
(Below code will insert one more row)

$strFileName ="F:\test.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Insert into [$strSheetName] values ('Ravi')"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn
$objConn.open()

$sqlCommand.ExecuteNonQuery()
$objConn.Close()

Hope, this will help you.

Enjoy scripting!!