Home > Back-end >  Powershell script Export-CSV cutoff characters in a string of a .TXT files with Comma
Powershell script Export-CSV cutoff characters in a string of a .TXT files with Comma

Time:01-12

1 .. $Count | ForEach-Object { 
    $i = $_.ToString($Length)

    $Offset = $BatchSize * ($_ - 1)
    $outputFile = $ParentDirectory   "\"   $strBaseName   "-"   $i   $strExtension
    If($_ -eq 1) {
        $objFile | Select-Object  -First $BatchSize | Export-Csv $outputFile -NoTypeInformation -Encoding UTF8  
    } Else {
       $objFile | Select-Object -First $BatchSize -Skip $Offset | Export-Csv $outputFile -NoTypeInformation -Encoding UTF8 
    }

  }    

I have a .txt with a comma on row 3 below. My code is stripping anything after the comma as seen below. how do I fix it? My file is pipe delimited.

Original file contains

|Header1|Header2|Header3|Header4|
|320|A1| |0900|
|320|A2|This, comma is needed|0900|
|320|A3| |0700|
|320|A4|f2|0900|
|320|A5| |0700|
|320|L2|c6|0900|

After splitting into 2 files -notice the missing text after "this,"

file1

|Header1|Header2|Header3|Header4|
|320|A1| |0900|
|320|A2|This, 
|320|A3| |0700|

file2

|Header1|Header2|Header3|Header4|
|320|A4|f2|0900|
|320|A5| |0700|
|320|L2|c6|0900| 

Please advise. Thanks

I tried to use delimiter and replace commands. Didn't work

CodePudding user response:

It looks like when you imported your delimited file into $objFile, you forgot to pass
-Delimiter '|' to the Import-Csv call, which would not interpret your |-separated file properly, given that Import-Csv - as well as Export-Csv - default to , as the separator.

Thus, the solution is to use -Delimiter '|' in both your Import-Csv and Export-Csv calls.


As for what you tried:

Here's a minimal example that demonstrates the problem with omitting -Delimiter '|', using the in-memory CSV processing cmdlets, ConvertFrom-Csv and ConvertTo-Csv:

@'
Field1|Field2
Value1|Value2, and more
'@ | 
  ConvertFrom-Csv | 
  ConvertTo-Csv

Output (note the missing , and more part, and how the output lines as a whole are double-quoted):

"Field1|Field2"
"Value1|Value2"
  • Since header row Field1|Field2 contains no , it became a single property in the resulting objects, literally named Field1|Field2.

  • Since the data row happened to contain , it was parsed as two fields, and since there is only one column, the extra field was simply discarded.

CodePudding user response:

Yes, It works by using -Delimiter '|' in both your Import-Csv and Export-Csv calls.

Thank you for your answer.

  • Related