import csv, zeropad a column, then export csv in powershell

Question

I've struggled with this all day... I'm trying to import a csv, filter out certain stores, zero pad all store ids to 5 digits, then re-export the csv with a different delimiter and remove quotes. I've got it all working except zero padding (input3 isn't working).

#Sleep -seconds 20  #wait for RW report generation
$input = Import-Csv E:\RWS\SysFiles\reports\CAST\ClientExport.csv -Header 'store','desc','status','ip','tcpip','timezone','drive','path','col9','col10'
$input2 = $input | where-object { $_.store -match "[0-9]" -and -not $_.store.StartsWith("99") }
#$input3 = $input2 | ForEach-Object { $_.store = $_.store.PadLeft(5,'0') }
$input3 | ConvertTo-Csv -NoTypeInformation -Delimiter ';' | % {$_ -replace '"', ""} | out-file E:\RWS\SysFiles\reports\CAST\CleanClientExport.csv -force

example input:

32013,"SHREVEPORT, LA",ENABLED,10.4.43.11,(TCP/IP),-6,C:,\Program Files\Remote\,, 7045,ELIZABETHTOWN-KY,ENABLED,10.82.240.11,(TCP/IP),-5,C:,\Program Files\Remote\,,

example out:

32013;SHREVEPORT, LA;ENABLED;10.4.43.11;(TCP/IP);-6;C:;\Program Files\Remote\;; 07045;ELIZABETHTOWN-KY;ENABLED;10.82.240.11;(TCP/IP);-5;C:;\Program Files\Remote\;;

I'm just getting a blank csv...and I'm struggling with viewing $input in a PS console, can't figure out how to display what i've imported on the host screen!

Is the "example out" how you want it to look? Or what it does look like? — Klas Mellbourn, Jun 03 '13 at 21:02

score 1 · Answer 1 · answered Jun 04 '13 at 08:55

1

Import-Csv E:\RWS\SysFiles\reports\CAST\ClientExport.csv -Header ... |
Where-Object { $_.store -match "[0-9]" -and -not $_.store.StartsWith("99") } | 
Foreach-Object {$_.store = $_.store.PadLeft(5,'0'); $_} | 
ConvertTo-Csv -NoTypeInformation -Delimiter ';' | 
Foreach-Object {$_ -replace '"'} | 
Out-File E:\RWS\SysFiles\reports\CAST\CleanClientExport.csv -Force

answered Jun 04 '13 at 08:55

Shay Levy

121,444
32
184
206

this worked, thx. I was missing the extra $_ in the 1st foreach. – Schrodo_Baggins Jun 04 '13 at 13:22

Ansgar Wiechers · Accepted Answer · 2013-06-04T11:54:29.130

Something like this should work:

$infile  = "E:\RWS\SysFiles\reports\CAST\ClientExport.csv"
$outfile = "E:\RWS\SysFiles\reports\CAST\CleanClientExport.csv"

Import-Csv $infile -Header 'store','desc','status','ip','tcpip','timezone','drive','path','col9','col10' `
  | ? { $_.store -notmatch '^99' } `
  | % { $_.store = "{0:D5}" -f [int]$_.store; $_ } `
  | ConvertTo-Csv -NoTypeInformation -Delimiter ';' `
  | % { $_ -replace '"', '' } `
  | Out-File $outfile -Force

If you import the CSV into a variable, you can display the imported data by entering a line cotaining just the variable:

PS C:\> $csv = Import-Csv $infile -Header 'store','desc','status','ip',...
PS C:\> $csv


store    : 32013
desc     : SHREVEPORT, LA
status   : ENABLED
ip       : 10.4.43.11
tcpip    : (TCP/IP)
timezone : -6
drive    : C:
path     : \Program Files\Remote\
col9     :
col10    :

store    : 07045
desc     : ELIZABETHTOWN-KY
status   : ENABLED
ip       : 10.82.240.11
tcpip    : (TCP/IP)
timezone : -5
drive    : C:
path     : \Program Files\Remote\
col9     :
col10    :

in regards to the second problem i was having...I know that I should see this console output as you display above, but I get nothing. Wish I could attach a screenshot...I type in the same line $csv = Import-Csv filepath -header etc... and then $csv and get nothing, just a new line on the console. I don't get it. When I type $csv.gettype() it returns arraylistemuneratorsimple, so something is there....I've tried all variations I can think of to display it, Write-Host "$csv", etc...nothing. All I can do is export it again as csv to view it in a file... — Schrodo_Baggins, Jun 04 '13 at 13:16
this worked, btw, thx. I was missing the extra $_ in the 1st foreach. — Schrodo_Baggins, Jun 04 '13 at 13:22

import csv, zeropad a column, then export csv in powershell

2 Answers2