CODEWITHSUNDEEP
powershellcsv
VernIsReallyTrying
VernIsReallyTrying

Reputation: 13

Powershell 5: ConvertTo-Csv a CSV with quotes in some but not all columns

I am building am updating a script which imports a large CSV file and then splits it into lots of separate CSV files based on the value in the first two columns

so POIMP_NL_20210306.csv which contains:

DOC_NUMBER|COMMENTS|ITEM|QTY|SUPPLIER 
P-100-1234|JANE|5059585896978|2|"JOES SUPPLIES" 
P-100-1234|JANE|5059585896985|2|"JOES SUPPLIES" 
P-100-6666|TED|5059585896992|1|"ACTION TOYS"

must be split into POIMP_P-100-1234_JANE.csv containing

P-100-1234|JANE|5059585896978|2|"JOES SUPPLIES"
P-100-1234|JANE|5059585896985|2|"JOES SUPPLIES"

and POIMP_P-100-6666_TED.csv

P-100-6666|TED|5059585896992|1|"ACTION TOYS"

The problem I am trying to solve is preserving the quotes in just the SUPPLIER column Since ConvertTo-Csv adds quotes to everything, I use a % { $_ -replace '"', ""} to remove these all before the out-file is created but of course it removes these from the SUPPLIER column 2

Here is my script which perfectly splits the big file into smaller files by DOC_NUMBER and COMMENTS but removes all quotes:

$basePath = "C:\"
$archivePath = "$basePath\archive\"
$todaysDate = $(get-date -Format yyyyMMdd)

$todaysFiles = @(
    (Get-ChildItem -Path $basePath | Where-Object { $_.Name -match 'POIMP_' + $todaysDate })
)

cd $basePath

foreach ($file in $todaysFiles ) {
    $fileName = $file.ToString()
    Import-Csv $fileName -delimiter "|" | Group-Object -Property "DOC_NUMBER","COMMENTS" | 
    Foreach-Object {    
            $newName = $_.Name -replace ",","_" -replace " ",""; $path=$fileName.SubString(0,8) + $newName+".csv" ; $_.group |
            ConvertTo-Csv -NoTypeInformation -delimiter "|" | % { $_ -replace '"', ""} | out-file $path -fo -en ascii 
        }
        Rename-Item $fileName -NewName ([io.path]::GetFileNameWithoutExtension("$fileName") + "_Original.csv")
        Move-Item (Get-ChildItem -Path $basePath | Where-Object { $_.Name -match '_Original' }) $archivePath -force
    }

And here is another script which I found online and amended and which successfully leaves quotes in just the SUPPLIER column by first adding double back ticks and then replacing these with quotes after all others have been removed

$ImportedCSV = Import-CSV "C:\POIMP_NL_20210306.csv" -delimiter "|"
$NewCSV = Foreach ($Entry in $ImportedCsv) {
        $Entry.SUPPLIER = '¬¬' + $Entry.SUPPLIER + '¬¬'
        $Entry
    }
$NewCSV | 
ConvertTo-Csv -NoTypeInformation -delimiter "|" | % { $_ -replace '"', ""} | % { $_ -replace '¬¬', '"'} | out-file "C:\updatedPO.csv" -fo -en ascii

I just can't merge these scripts to achieve the desired result as I can't seem to reference the correct object. I'd really appreciate your help! Thanks

Upvotes: 1

Views: 1187

Answers (2)

Theo
Theo

Reputation: 61168

Any good CSV reader should be able to handle quotes around csv fields, even when not really needed.

Having said that, It is your explicit wish to only have quotes around the field in the SUPPLIER column. (Note, in your example there is a trailing space after that column name)

In this case, I think this would help. Not only does it surround the SUPPLIER fields with quotes, but also saves the data as separate files using the values from column DOC_NUMBER and COMMENTS per group found in the csv

$path   = 'D:\Test'
$fileIn = Join-Path -Path $path -ChildPath 'POIMP_NL_20210306.csv'

# import the csv file and group first two columns
Import-Csv -Path $fileIn -Delimiter '|' | Group-Object -Property "DOC_NUMBER","COMMENTS" | ForEach-Object {
    $headerDone = $false
    $data = foreach ($item in $_.Group) {
        if (!$headerDone) {
            $item.PsObject.Properties.Name -join '|'
            $headerDone = $true
        }
        $item.SUPPLIER = '"{0}"' -f $item.SUPPLIER
        $item.PsObject.Properties.Value -join '|'
    }
    # create a new filename like 'POIMP_P-100-1234_JANE.csv'
    $fileOut = Join-Path -Path $path -ChildPath ('POIMP_{0}_{1}.csv' -f $_.Group[0].DOC_NUMBER, $_.Group[0].COMMENTS)
    # save the data not using Export-Csv because that will add quotes around everything (in PowerShell 5)
    $data | Set-Content -Path $fileOut -Force
}

Output

POIMP_P-100-1234_JANE.csv

DOC_NUMBER|COMMENTS|ITEM|QTY|SUPPLIER
P-100-1234|JANE|5059585896978|2|"JOES SUPPLIES"
P-100-1234|JANE|5059585896985|2|"JOES SUPPLIES"

POIMP_P-100-6666_TED.csv

DOC_NUMBER|COMMENTS|ITEM|QTY|SUPPLIER
P-100-6666|TED|5059585896992|1|"ACTION TOYS"

Upvotes: 0

WaitingForGuacamole
WaitingForGuacamole

Reputation: 4301

If you are Powershell 7 or later, you can use

$yourdata | ConvertTo-Csv -NoTypeInformation -QuoteFields "SUPPLIER" -Delimiter "|" | 
  Out-File ...

or you could use

$yourdata | Export-Csv -NoTypeInformation -QuoteFields "SUPPLIER" `
  -Delimiter "|" -Path <path-to-output-file>.csv

You can also use -UseQuotes AsNeeded to let the converter add quoting where it thinks it makes sense, otherwise just specify the fields you want quoted.

Upvotes: 0

Related Questions