Welcome › Forums › General PowerShell Q&A › Code Efficiency – CSV Trim and Replace Operations
- This topic has 2 replies, 3 voices, and was last updated 10 months, 2 weeks ago by
Participant.
-
AuthorPosts
-
-
March 12, 2020 at 5:38 am #209799
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)
PowerShell123456789101112131415$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" ]"-
This topic was modified 10 months, 2 weeks ago by
vertigo88858.
-
This topic was modified 10 months, 2 weeks ago by
vertigo88858.
-
This topic was modified 10 months, 2 weeks ago by
-
March 12, 2020 at 1:04 pm #209871
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.
PowerShell12345678$content = ".\documents"$files = Get-ChildItem -Path $content -Recurse -Filter *.csvforeach ($item in $files){(Get-Content $item.fullname -Raw) -replace ',',"`t" -replace '"','' |Set-Content -Path "$($item.fullname).tsv"} -
March 12, 2020 at 2:00 pm #209895
Loops are traditionally avoided if possible, but here are a couple of options. This is the CSV to mess with:
PowerShell12345678$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:
PowerShell1234567$csv = Import-Csv -Path 'C:\Scripts\temp.csv'$myNewCSV = [System.Text.StringBuilder]::new()#Header Rowforeach ($row in $csv) {[void]$myNewCSV.Append("{0}[crayon-600ec840e9587421994106 inline="true" ]rOutput:
PowerShell12345678910111213141516171819EmployeeId;FirstName;LastName;Manager;Password;Usernametst00001;Angela;Tester;rsimmers;<RANDOM>;atestertst00002;Angelo;Tester;rsimmers;<RANDOM>;atestertst00003;Bob;Tester;rsimmers;<RANDOM>;btestertst00004;Chris;Tester;rsimmers;<RANDOM>;ctestertst00005;Derek;Tester;rsimmers;<RANDOM>;dtester#Measure CommandDays : 0Hours : 0Minutes : 0Seconds : 0Milliseconds : 15Ticks : 152401TotalDays : 1.76390046296296E-07TotalHours : 4.23336111111111E-06TotalMinutes : 0.000254001666666667TotalSeconds : 0.0152401TotalMilliseconds : 15.2401Another approach is using Get-Content -Raw, which should bring the content in as raw content and not parse lines. This is available Powershell 4+.
PowerShell12$content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw$content.Replace('"','')Output:
PowerShell123456789101112131415161718192021222324252627282930PS C:\Users\rasim> $content.Count1PS C:\Users\rasim> $content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw$content.Replace('"','')EmployeeId,FirstName,LastName,Manager,Password,Usernametst00001,Angela,Tester,rsimmers,<RANDOM>,atestertst00002,Angelo,Tester,rsimmers,<RANDOM>,atestertst00003,Bob,Tester,rsimmers,<RANDOM>,btestertst00004,Chris,Tester,rsimmers,<RANDOM>,ctestertst00005,Derek,Tester,rsimmers,<RANDOM>,dtesterPS C:\Users\rasim> Measure-Command {$content = Get-Content -Path 'C:\Scripts\temp.csv' -Raw$content.Replace('"','')}Days : 0Hours : 0Minutes : 0Seconds : 0Milliseconds : 12Ticks : 127013TotalDays : 1.47005787037037E-07TotalHours : 3.52813888888889E-06TotalMinutes : 0.000211688333333333TotalSeconds : 0.0127013TotalMilliseconds : 12.7013
-
-
AuthorPosts
- The topic ‘Code Efficiency – CSV Trim and Replace Operations’ is closed to new replies.