Code Efficiency – CSV Trim and Replace Operations

[vertigo88858]

Hi

My code below finds all CSVs recursively in a directory, loads located CSVs, converts the CSVs into TSVs, trims first and end double quotes and then removes all double quotes between a tab space.

My Question:

Is there a way to make the code more efficient? I can’t seem to find a way to remove the ForEach-Object loop. Please help. Thanks. (For some reason the accent symbol does not work to display the code below)

PowerShell

$content = "C:\somedir" $files = Get-ChildItem -Path $content -Recurse -Filter *.csv | Select-Object -Property *, @{ expression ={ Write-Host $_.FullName $source = $_.FullName $dest = $_.DirectoryName + "\" + $_.BaseName + ".tsv" Import-Csv -Path $source | convertto-csv -NoTypeInformation -Delimiter "`t" | % { ForEach-Object { $_.Trim('"') | % { $_ -replace ("[crayon-600ec840d7866272557664 inline="true" ]"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$content = "C:\somedir"   $files = Get-ChildItem -Path $content -Recurse -Filter *.csv | Select-Object -Property *, @{ expression ={ Write-Host $_.FullName $source = $_.FullName $dest = $_.DirectoryName + "\" + $_.BaseName + ".tsv" Import-Csv -Path $source | convertto-csv -NoTypeInformation -Delimiter "`t" | % { ForEach-Object { $_.Trim('"') | % { $_ -replace ("[crayon-600ec840d7866272557664 inline="true" ]"

t "" ,"t") } } } | Set-Content "$dest" } } [/crayon]

• This topic was modified 10 months, 2 weeks ago by vertigo88858.
• This topic was modified 10 months, 2 weeks ago by vertigo88858.

[Mike R. Participant Topics: 5 Replies: 260 Points: 1,039Rank: Community Hero]

Which ForEach-Object loop are you trying to eliminate?  I count 3 of them in your code (line 12, 13, 15).  % is an alias for foreach-object.  Without samples of your csv, I think you might be better working with the raw data that multiple conversions (import-csv then  convertto-csv).  I’m not sure if my example below is more efficient, but you could use measure-command to see for sure.  Either way I find yours a little hard to read with nested loops, but that is just a preference.

PowerShell

$content = ".\documents" $files = Get-ChildItem -Path $content -Recurse -Filter *.csv foreach ($item in $files) { (Get-Content $item.fullname -Raw) -replace ',',"`t" -replace '"','' | Set-Content -Path "$($item.fullname).tsv" }

1 2 3 4 5 6 7 8

$content = ".\documents" $files = Get-ChildItem -Path $content -Recurse -Filter *.csv   foreach ($item in $files) {    (Get-Content $item.fullname -Raw) -replace ',',"`t" -replace '"','' |        Set-Content -Path "$($item.fullname).tsv" }

[Rob Simmers Participant Topics: 17 Replies: 1956 Points: 4,010Rank: Community Hero]

Loops are traditionally avoided if possible, but here are a couple of options. This is the CSV to mess with:

PowerShell

$csv = @" "EmployeeId","FirstName","LastName","Manager","Password","Username" "tst00001","Angela","Tester","rsimmers","<RANDOM>","atester" "tst00002","Angelo","Tester","rsimmers","<RANDOM>","atester" "tst00003","Bob","Tester","rsimmers","<RANDOM>","btester" "tst00004","Chris","Tester","rsimmers","<RANDOM>","ctester" "tst00005","Derek","Tester","rsimmers","<RANDOM>","dtester" "@

1 2 3 4 5 6 7 8

$csv = @" "EmployeeId","FirstName","LastName","Manager","Password","Username" "tst00001","Angela","Tester","rsimmers","<RANDOM>","atester" "tst00002","Angelo","Tester","rsimmers","<RANDOM>","atester" "tst00003","Bob","Tester","rsimmers","<RANDOM>","btester" "tst00004","Chris","Tester","rsimmers","<RANDOM>","ctester" "tst00005","Derek","Tester","rsimmers","<RANDOM>","dtester" "@

This would parse using Import-CSV, so if there are “rogue” double qoutes in the content, this is parsing it as a CSV and then gluing it together with string builder. While this is using for loops, there is no Replace operations, it’s just parse and glue it together:

PowerShell

$csv = Import-Csv -Path 'C:\Scripts\temp.csv' $myNewCSV = [System.Text.StringBuilder]::new() #Header Row foreach ($row in $csv) { [void]$myNewCSV.Append("{0}[crayon-600ec840e9587421994106 inline="true" ]r

1 2 3 4 5 6 7

$csv = Import-Csv -Path 'C:\Scripts\temp.csv'   $myNewCSV = [System.Text.StringBuilder]::new()   #Header Row foreach ($row in $csv) {     [void]$myNewCSV.Append("{0}[crayon-600ec840e9587421994106 inline="true" ]r

n" -f ($row.PSObject.Properties.Name -join ';')) break } #Data Row foreach ($row in $csv) { [void]$myNewCSV.Append("{0} rn" -f ($row.PSObject.Properties.Value -join ';')) } $myNewCSV.ToString() #Set Content [/crayon]

Output:

PowerShell

EmployeeId;FirstName;LastName;Manager;Password;Username tst00001;Angela;Tester;rsimmers;<RANDOM>;atester tst00002;Angelo;Tester;rsimmers;<RANDOM>;atester tst00003;Bob;Tester;rsimmers;<RANDOM>;btester tst00004;Chris;Tester;rsimmers;<RANDOM>;ctester tst00005;Derek;Tester;rsimmers;<RANDOM>;dtester #Measure Command Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 15 Ticks : 152401 TotalDays : 1.76390046296296E-07 TotalHours : 4.23336111111111E-06 TotalMinutes : 0.000254001666666667 TotalSeconds : 0.0152401 TotalMilliseconds : 15.2401

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

EmployeeId;FirstName;LastName;Manager;Password;Username tst00001;Angela;Tester;rsimmers;<RANDOM>;atester tst00002;Angelo;Tester;rsimmers;<RANDOM>;atester tst00003;Bob;Tester;rsimmers;<RANDOM>;btester tst00004;Chris;Tester;rsimmers;<RANDOM>;ctester tst00005;Derek;Tester;rsimmers;<RANDOM>;dtester   #Measure Command Days              : 0 Hours             : 0 Minutes           : 0 Seconds           : 0 Milliseconds      : 15 Ticks             : 152401 TotalDays         : 1.76390046296296E-07 TotalHours        : 4.23336111111111E-06 TotalMinutes      : 0.000254001666666667 TotalSeconds      : 0.0152401 TotalMilliseconds : 15.2401

Another approach is using Get-Content -Raw, which should bring the content in as raw content and not parse lines. This is available Powershell 4+.

PowerShell

$content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','')

1 2	$content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','')

Output:

PowerShell

PS C:\Users\rasim> $content.Count 1 PS C:\Users\rasim> $content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','') EmployeeId,FirstName,LastName,Manager,Password,Username tst00001,Angela,Tester,rsimmers,<RANDOM>,atester tst00002,Angelo,Tester,rsimmers,<RANDOM>,atester tst00003,Bob,Tester,rsimmers,<RANDOM>,btester tst00004,Chris,Tester,rsimmers,<RANDOM>,ctester tst00005,Derek,Tester,rsimmers,<RANDOM>,dtester PS C:\Users\rasim> Measure-Command { $content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','') } Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 12 Ticks : 127013 TotalDays : 1.47005787037037E-07 TotalHours : 3.52813888888889E-06 TotalMinutes : 0.000211688333333333 TotalSeconds : 0.0127013 TotalMilliseconds : 12.7013

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

PS C:\Users\rasim> $content.Count 1   PS C:\Users\rasim> $content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','') EmployeeId,FirstName,LastName,Manager,Password,Username tst00001,Angela,Tester,rsimmers,<RANDOM>,atester tst00002,Angelo,Tester,rsimmers,<RANDOM>,atester tst00003,Bob,Tester,rsimmers,<RANDOM>,btester tst00004,Chris,Tester,rsimmers,<RANDOM>,ctester tst00005,Derek,Tester,rsimmers,<RANDOM>,dtester     PS C:\Users\rasim> Measure-Command { $content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw $content.Replace('"','') }     Days              : 0 Hours             : 0 Minutes           : 0 Seconds           : 0 Milliseconds      : 12 Ticks             : 127013 TotalDays         : 1.47005787037037E-07 TotalHours        : 3.52813888888889E-06 TotalMinutes      : 0.000211688333333333 TotalSeconds      : 0.0127013 TotalMilliseconds : 12.7013

[Author]

Posts