getting data from excel using power shell

[jimelali]

Hi there;

I am very new in powershell. I have a simple project that I thought I can use power shell to program, but I see now it is not as simple as I thought.

I have an excel sheet with 6 columns and around 150 rows. I need to read the data from the first 4 columns in each row and execute a different function per row on the 5^th column and then print the content of the column 6.

Here is an example of the data:

55	77	6	8	add	comment 1
67		0	0	average	comment 2

I also would like to print the exact data after the calculation to a file using the first row as a header (btw, some cell could be empty).

I started something but then I stalled.

$Excel = new-object -comobject “excel.application”

$Excel.Visible = $True

location of eng spreadsheet

$file=‘C:\Users\test.xlsx’

Name of the tab where test parameters are defined.

$SheetName = ‘sheet1’

$WorkBook= $Excel.Workbooks.Open($file)

$WorkSheet = $Workbook.Worksheets.Item(1)

foreach ($row in $WorkSheet)

{

$col1= $WorkSheet.Columns

}

Any help or suggestions?

Thank you very much.

Jim

[bobmccoy]

This would be a LOT simpler working with a CSV file.

[jimelali]

possible, but the data is organized in excel!

[SysSquatch]

Hey Jimelali,

Your first step would be to convert the file to a CSV. You can do that by using “Save-As” within Excel. All a CSV does is convert your table into a line of text delimited by commas. (So of course make sure there are no commas in the original document.) From there you can use the Import-CSV and For-EachObject commands. For example I once setup a quick script to import a bunch of PSTs and used this script.

Import-CSV D:\Backups\BackupAtoC.csv | ForEach-Object{
New-MailboxImportRequest -Mailbox $_.Alias -FilePath $_.PSTName
}

In it “Alias” and “PSTName” were a couple of the field headers. The ForEach-Object processed each row.

If you want to use excel specifically I believ eyou need to change $Workbook.Worksheets to just $Workbook.Sheets

[epltech-jake]

I too am doing a powershell project. Import AD users from CSV + add DFS namespace & directory. I hope to gain some knowledge from this posting so I am subscribed.

[jimelali]

Lee:

Thank you for the reply and the suggestion. I will try it. I hope it will work with excel.

Jim

[artb]

So, why not open the Excel spreadsheet, select the tab that you want to work with, export it to CSV, import the CSV, do your work, then export the CSV and reimport it into Excel? This example may seem to be a lot of code, but it’s really fast.

$XLTypes = Add-Type -AssemblyName "Microsoft.Office.Interop.Excel" -PassThru
$FileFormat = $xltypes | ? {$_.name -eq "XlFileFormat"}
$XL = New-Object -comObject Excel.Application
$XLWB=$XL.Workbooks.Open("<MyFile>.xlsx")
$XL.Visible = $True
$Sheet = $XLWB.Worksheets.Item('MySheet')
$Sheet.Activate()
$Sheet.SaveAs("_MySheet.csv",$FileFormat::xlCSV)

# Import the sheet and work on it
$MySheet = Import-Csv _MySheet.csv
# Update $MySheet however you need to
$MySheet | Export-CSV _MySheet.csv -NoTypeInformation

# Now, reimport it into Excel
$XLWB2=$XL.Workbooks.Open("_MySheet.csv")
$Sh = $XLWB2.Worksheets.Item(1)
$sh.Copy($Sheet)
$XL.DisplayAlerts = $false
$Sheet.Delete()
$Sheet = $XLWB.Worksheets.Item('_MySheet')
$Sheet.Name = 'MySheet'
$XLWB2.Close()
$XLWB.Save()