process multiple CSV file and delete rows in a single column which has double semi colon characters using powershell

Question

consider I have a below CSV file.

input:

ID;ITEM_ID;STATUS;
001;;RELEASED;
002;36530;RELEASED;
003;86246;RELEASED;
004;;RELEASED;

I want to remove the row that has ;; (ITEM_ID) missing and save it.I tried doing it on one sample file and it worked as expected.

Import-Csv -Path ".\TestFile.CSV" | where {$_.ITEM_ID -ne ""} | Export-Csv -Path ".\TestFile-temp.CSV" -NoTypeInformation
Remove-Item -Path '.\TestDir\TestFile.csv'
Rename-Item -Path '.\TestDir\TestFile-temp.csv' -NewName 'TestFile.csv'

output:

ID;ITEM_ID;STATUS;
002;36530;RELEASED;
003;86246;RELEASED;

The challenge is, i have multiple csv files and it doesn't has value in different columns, but in single column when i opened in excel file. so it's not taking the condition < where {$_.ITEM_ID -ne ""} >. Now i have to search/parse each row of each csv file, search special character (;;) in that row and delete the line and save the file.

i am good at shell scripting but, i am very new to powershell scripting. can anybody please help me to get the logic here or use other cmdlet that can do the job?

$fileDirectory = "C:\Users\Administrator\Documents\check";
foreach($file in Get-ChildItem $fileDirectory)
{
    $csvFileToCheck = Import-Csv -Path $fileDirectory\$file
    $noDoubleSemiComma = foreach($line in $csvFileToCheck)
            {
                if(Select-String << i want the logic here>>)
                {
                $line
                }               
            }
    $noDoubleSemiComma | Export-Csv -Path $fileDirectory\tmp.csv -NoTypeInformation
    Remove-Item -Path $fileDirectory\$file
    Rename-Item -Path $fileDirectory\tmp.csv -NewName $file
}

The header in your example CSV is actually missing a column. If you simply add that, you can read the file properly and re-export it without the rows you don't want. — , Dec 04 '20 at 10:42
Excel doesn't handle commas in CSV files. It simply puts everything in one column. Either use semicolons when exporting your CSV or use the import wizard in Excel where you can tell it which separator to use. — , Dec 04 '20 at 10:58
@Swonkie yes, there was a missing header. The file which i have has lots of column. for posting this question, i removed few columns. i have updated the sample data file in above question. — srikanth jagdhane, Dec 04 '20 at 11:33
@Swonkie, yes i am looking for the same option as IFS. working on that. hope to post the answer soon. — srikanth jagdhane, Dec 04 '20 at 11:35
Use `Import-Csv -Path ".\TestFile.CSV" -Delimiter ';'` after you have fixed the headers. Without specifying the delimiter character, the cmdlet assumes the data is comma separated. — Theo, Dec 04 '20 at 13:00

score 1 · Accepted Answer · answered Dec 06 '20 at 14:21

As commented, you need to add parameter -Delimiter ';' to the cmdlet otherwise a comma is used to parse the fields in the CSV.

As I understand, you also want to remove the quotes Export-Csv outputs around all fields and headers and for PowerShell version 7 you have the option to use parameter -UseQuotes AsNeeded.

As this is not available for version 5.1, I made a function ConvertTo-CsvNoQuotes some time ago to remove the quotes in a safe way. (simply replacing them all with an empty string is dangerous, because sometimes values do need quotes)

Copy that function into your script at the top, then below that, your code could be simplified like this:

$fileDirectory = "C:\Users\Administrator\Documents\check"

Get-ChildItem -Path $fileDirectory -Filter '*.csv' -File | ForEach-Object {
    # for better readability store the full path of the file in a variable
    $filePath = $_.FullName
    (Import-Csv -Path $filePath -Delimiter ';') | ConvertTo-CsvNoQuotes -Delimiter ';' | Set-Content $filePath -Force
    Write-Host "File '$filePath' modified"
}

take a bow. it worked perfectly. Marked this answer as Accepted. — srikanth jagdhane, Dec 06 '20 at 15:18

score 0 · Answer 2 · answered Dec 06 '20 at 12:05

After all helpful suggestion, i finally nailed it down. AS my power-shell version was 5.1 , i had to use logic for trimming double quotes after export-csv. Powershell version 7 and later has -UseQuotes that could have solve that too. Hope this help others.

$fileDirectory = "C:\Users\Administrator\Documents\check";
foreach($file in Get-ChildItem $fileDirectory)
{
        Import-Csv -Path $fileDirectory\$file -Delimiter ';' | where {$_..ITEM_ID -ne ""} | Export-Csv -Path $fileDirectory\temp.csv -Delimiter ';' -NoTypeInformation
        $Test = Get-Content $fileDirectory\temp.csv
        $Test.Replace('";"',";").TrimStart('"').TrimEnd('"') | Out-File $fileDirectory\temp.csv -Force -Confirm:$false
        Remove-Item -Path $fileDirectory\$file
        Rename-Item -Path $fileDirectory\temp.csv -NewName $file
        Write-Output "$file file modified."
}

Any suggestion to trim down number of lines of code is welcomed.

process multiple CSV file and delete rows in a single column which has double semi colon characters using powershell

2 Answers2