0

I have csv files which contain data as per below sample

MACADD||TEST|Street1|CITY||Taiwan|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE|||

I want to replace the 7th value in every csv files but problem is when I replace it the entire data gets separated by comma as the replacement value contained comma in it.

is there any way to ignore/escape commas while after the replacement has happened so that entire data appears in 1st cell of the final csv file.

$Files = Get-content -Path F:\Newfolder\*.csv

 $CountryCodeLookup = @{
    'USA'= 'United States'
    'Taiwan' = "Taiwan, Republic of China" # for this item csv file become strange
    'Delhi' = "Delhi, Capital of India, U.T" # for this item csv file become strange
    }

foreach ($File in $Files)
    {
    $DelimCount = ($File -replace '[^|]', '').Length
    $CSV_Thing = ConvertFrom-Csv -Delimiter '|' -InputObject $File -Header @(1..$DelimCount)

    If ($CountryCodeLookup.ContainsKey($CSV_Thing.7))    
    {
    $CSV_Thing.7 = $CountryCodeLookup[$CSV_Thing.7]
    }


    $OutString = (($CSV_Thing |
        ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
        Select-Object -Skip 1) -replace '"', '') + '|' | Set-Content $File.PSPath -Force

    }
Nishant
  • 7
  • 3
  • Does this answer your question? [How to remove all quotations mark in the csv file using powershell script?](https://stackoverflow.com/questions/60678901/how-to-remove-all-quotations-mark-in-the-csv-file-using-powershell-script) Never blindly remove all quote characters from a CSV file. – Theo May 27 '20 at 15:00
  • @Theo, actually I am trying to escape the comma character when the replacement has happened. if you see my code, I am trying to find string in the CSV files which are defined in the hash table if found then replacing it with the corresponding value. and save the file with the original file name. things work well when the replacement string does not have the comma in it otherwise entire data gets separated into multiple columns and this is what I am trying to avoid. – Nishant May 27 '20 at 15:08
  • 1
    The way to escape commas in CSV is by **quoting the strings that contain commas**. That is why I'm voting this a duplicate, because after the replacements have been done, you simply remove all quotes in the entire CSV with `-replace '"', ''` rendering the csv invalid. – Theo May 27 '20 at 15:10
  • @Theo, let me explain with the help of an example. before replacement : MACADD||TEST|Street1|CITY||Taiwan|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE||| After replacement: MACADD||TEST|Street1|CITY||Taiwan, Republic of China|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE||| – Nishant May 27 '20 at 15:14
  • I do not want to replace commas, I want to keep it and also doesn't want this comma to affect the final output – Nishant May 27 '20 at 15:16
  • Why don't you try using `.substring()`? – Nico Nekoru May 27 '20 at 15:47
  • Can you please help me with this code, I am new to this scripting. – Nishant May 27 '20 at 15:51
  • when i run the code on the data you show, i get `MACADD||TEST|Street1|CITY||Taiwan, Republic of China|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE|||` at that point, the data seems to be correct. when do you see "otherwise entire data gets separated into multiple columns"? – Lee_Dailey May 27 '20 at 20:11
  • @Lee_Dailey, Please try to replace the content in .csv file (for .txt files the code is working). When I try the code for .csv file I got- MACADD||TEST|Street1|CITY||Taiwan (in 1st column) Republic of China|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE||| (in 2nd column) because 7th position "Taiwan" was replaced by "Taiwan, Republic of China" and entire data got separated into 2 columns – Nishant May 28 '20 at 05:56
  • @Nishant - that is caused by importing it _with the wrong delimiter_. if you import with ONLY the `|` as your delimiter, any column with a comma is treated as a single column. if your application WILL NOT allow that, then you need to either fix it OR allow quotes around columns. **_there is a reason for having quotes around columns ... and you are seeing that reason._** – Lee_Dailey May 28 '20 at 12:04
  • @Lee_Dailey, I got the help. instead of replacing the string in .csv file, I am first changing it to .txt file - your code also works for .txt file's content replacement. After the replacement I am doing get-content and Out-file to .csv files in this way comma within the data string does not split the entire data string into multiple columns. I have added my final code below, this works now. Thanks – Nishant May 28 '20 at 13:05
  • @Nishant - i'm glad to know that you got it working as needed. you WILL have nasty problems with the data if you ever try to use it as a standard CSV file - unless you explicitly define a delimiter that is not a comma ... but apparently that is not a problem with your current target application. [*grin*] – Lee_Dailey May 28 '20 at 13:12

1 Answers1

0
$Files = Get-content -Path F:\Newfolder\*.txt
$Path = "F:\Newfolder"

 $CountryCodeLookup = @{
    'USA'= 'United States'
    'Taiwan' = "Taiwan, Republic of China" # for this item csv file become strange
    'Delhi' = "Delhi, Capital of India, U.T" # for this item csv file become strange
    }

foreach ($File in $Files)
    {
    $DelimCount = ($File -replace '[^|]', '').Length
    $CSV_Thing = ConvertFrom-Csv -Delimiter '|' -InputObject $File -Header @(1..$DelimCount)

    If ($CountryCodeLookup.ContainsKey($CSV_Thing.7))    
    {
    $CSV_Thing.7 = $CountryCodeLookup[$CSV_Thing.7]
    }


    $OutString = (($CSV_Thing |
        ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
        Select-Object -Skip 1) -replace '"', '') + '|' | Set-Content $File.PSPath -Force

    }


    $files = Get-ChildItem $Path | where { ! $_.PSIsContainer }
foreach ($file in $files){
    $newFileName = ($file.Fullname) -Replace ".txt",".csv"
    Get-Content $file.FullName | Out-File $newFileName
}
Nishant
  • 7
  • 3