We help IT Professionals succeed at work.

Remove Columns from CSV file, save results to new file

markpalinux
markpalinux asked
on
Looking for help creating a script that takes input parameters...
I want to take a file l like file1 - give a script name, input filename, output filename , and a string of column names to keep...

script.ps1 file1.csv file2.csv name,address

File1.csv ( see below )
name,fullname,phone,address

script would remove all columns that are not in the columns input, result would show:

Output
File2.csv
name,address

I had a vbscript that would do this a while back but I lost it.  That script also created the file file in excel, which would be fine for this as well.

Thank you.
Mark

File1.csv
name,fullname,phone,address
wsmith,"William Smith",111-111-1111,111 street
bcar,"Billy Car",222-222-2222,2222 road
dward,"David Ward",333-333-3333,3333 circle


File2.csv
name,address
wsmith,111 street
bcar,2222 road
dward,3333 circle

Open in new window

Comment
Watch Question

View Solution Only

PowerShell Developer
Top Expert 2010
Commented:

Hey Mark,

In PowerShell it can be as simple as this:


$PropertiesToKeep = name,address
Import-CSV file1.csv | Select-Object $PropertiesToKeep | Export-CSV file2.csv -NoTypeInformation


HTH

Chris
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Sorry, should have been:

$PropertiesToKeep = "name", "address"

Otherwise it'll complain that name is not a CmdLet :)

Chris
yehudaha

Commented:
do yo want only powershell ?

if not add more zones to your question like vbscript
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Yell if you do want more, I'll add it and send out an alert :)

Chris
markpalinux

Author

Commented:
Chris - thank you.

Would you be able to tell me how I can get the result to Excel rather then a CSV file?

Mark
Chris DentPowerShell Developer
Top Expert 2010

Commented:

It's messy, you have to use the Excel.Application COM object, no different from VbScript in that respect.

It should go something like this, but I don't have Excel installed here so I haven't tested it.

Chris
$PropertiesToKeep = "name", "address"
$Data = Import-CSV file1.csv | Select-Object $PropertiesToKeep

$Excel = New-Object -comobject Excel.Application

$Workbook = $Excel.Workbooks.Add()
$WorkSheet = $Workbook.Worksheets.Item(1)

$Row = 1
$Data | ForEach-Object {
  $Column = 1

  $Data.PsObject.Properties | ForEach-Object {
    $WorkSheet.Cells($Row, $Column) = $_.Value
    $Column++
  }
  $Row++
}

$Workbook.SaveAs("C:\Temp\Workbook.xls")
$Excel.Quit()

# To get rid of the Excel executable
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

Open in new window

markpalinux

Author

Commented:

Thanks