Export specific columns from Excel to .csv Powershell

Question

I have such a code for exporting Excel file with two worksheets into two csv files.The problem is that I am currently exporting whole worksheets and I want to export only these 3 columns from my loop.How can I save them? They must be in order because I want to import it later to AD.

Function ExportWSToCSV ($excelFileName , $csvLoc){

#Sample use in a console:  ExportWSToCSV -excelFileName "Test_Peoplesoft.xls" -csvLoc "y:\Application Data\CSVFiles\"

$CultureOld = [System.Threading.Thread]::CurrentThread.CurrentCulture
#Original culture info 
$CultureUS = [System.Globalization.CultureInfo]'en-US'                            
#US culture info
$excelFile = "y:\Application Data\Test_Peoplesoft.xls"                            
#Loc of Excel file .xls    , #csvLov - Loc of output files in format .csv
[System.Threading.Thread]::CurrentThread.CurrentCulture = $CultureUS
$E = New-Object -ComObject Excel.Application
$E.Visible = $false                                                               
$E.DisplayAlerts = $false                                                   
$wb = $E.Workbooks.Open($excelFile)
$intRow = 2  
$intRowMax =($ws.UsedRange.Rows).count 
$elements = $email -or $costcode -or $leader 



Do{
foreach($ws in $wb.sheets.item("Inactive")){
if($elements -ne $null ){
$email = $ws.Cells.Item($intRow, 4).Value()
$costcode = $ws.Cells.Item($intRow, 15).Value()
$leader = $ws.Cells.Item($intRow, 20).Value()
}else{Write-Host "Null Value in one of the attributes"}

}
<#
 foreach($ws in $wb.sheets.item("Inactive")){
$email = $ws.Cells.Item($intRow, 4).Value()
$costcode = $ws.Cells.Item($intRow, 15).Value()
$leader = $ws.Cells.Item($intRow, 20).Value()
}
#>
$user = $email + "_" + $costcode + "_" + $leader

write-host $intRow " " $user 

$intRow++    

}While ($ws.Cells.Item($intRow,1).Value() -ne $null)


foreach ($ws in $wb.Worksheets)
{
    Write-Host "Processing Worksheet: " $ws.Name
    $n = $csvLoc + $excelFileName + "_" + $ws.Name                               
    #Name variable - Output file loc + excel file name + worksheet name             
    $ws.SaveAs($n + ".csv", 6)                                                   
    #Saving file to .csv       
}



$E.Quit()
[System.Threading.Thread]::CurrentThread.CurrentCulture = $CultureOld

}

Roderick Bant · Accepted Answer · 2014-08-17 13:57:06Z

1

Something like this should work:

First, setup an array for containing our list of exported users like so:

$exportList = @()

(place before you start looping the rows)

Then loop through the rows on your worksheet with your do while loop and add a user object to add the your export list like so

# Create userObj with the required properties
$userObj = New-Object System.Object
$userObj | Add-Member -Type NoteProperty -Name Email -Value $email
$userObj | Add-Member -Type NoteProperty -Name CostCode -Value $costcode
$userObj | Add-Member -Type NoteProperty -Name Leader -Value $leader

# Add $userObj to our list to be exported
$exportList += $userObj

And ofcourse export it to .csv

$exportList | Export-Csv $pathToCsvFile

answered Aug 17, 2014 at 13:57

Roderick Bant

1,7991 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Export specific columns from Excel to .csv Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related