-1

Attempting to parse data from a text file in a csv format and using | as the delimiter. When I try to parse the data all I get is the information with no space or breaks where the | would be. All the data is written as one long sentence.

I'm trying to use

Import-Csv C:\Users\myname\Desktop\FIX\MILLIMANFUT-CSFUT-FIX_4_4_202212052245000551.summary -Delimiter "|"

enter image description here

mklement0
  • 382,024
  • 64
  • 607
  • 775
  • 2
    If that image - and you shouldn't use images where pasting the text into a codeblock will do better - is an accurate representation of your "CSV" file, it's not a CSV file, it's a text file with the fields delimited by spaces, and no headers. – Jeff Zeitlin Jan 09 '23 at 15:07
  • 2
    What does the input text look like? – Mathias R. Jessen Jan 09 '23 at 15:07
  • 2
    Please edit your question and include a _sanitized_ sample of your input file. Please copy/paste the actual text, not an image. This makes it much easier for us to try and reproduce the problem. – zett42 Jan 09 '23 at 16:24
  • Open file with a text editor and see if the delimitator is in the file. What you posted doesn't contain the delimitator. Use Format-Table to see the columns : Import-Csv C:\Users\myname\Desktop\FIX\MILLIMANFUT-CSFUT-FIX_4_4_202212052245000551.summary -Delimiter "|" | Format-Table – jdweng Jan 09 '23 at 18:23
  • @jdweng I see this when I open as a txt file IN 20221206-18:11:03.129475700 8=FIX.4.49=5835=034=233449 and I tried that as well – Ed Vega Jan 09 '23 at 18:26
  • 3
    I just remembered the format. It is [FIX - financial interchange format](https://en.wikipedia.org/wiki/Financial_Information_eXchange). _"FIX messages are formed from several fields; each field has a tag value pairing that is separated from the next field by a delimiter SOH (0x01)."_ -- This is what you see as `□` (non-printable character). See [this answer](https://stackoverflow.com/a/72756824/7571258) for how to parse it. Note [this comment](https://stackoverflow.com/questions/72756597/parse-structured-file-fix-4-4-in-powershell/72756824#comment128513700_72756824). – zett42 Jan 09 '23 at 19:58
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jan 10 '23 at 12:58
  • 1
    Thanks, @zett42 - I've just updated my answer to the linked question and have removed the comment you link to - I previously hadn't paid attention to the specific format/protocol (FIX); good to know. – mklement0 Jan 10 '23 at 23:26
  • 2
    Does this answer your question? [Parse structured file (FIX 4.4) in powershell](https://stackoverflow.com/questions/72756597/parse-structured-file-fix-4-4-in-powershell) – zett42 Jan 10 '23 at 23:56
  • No it didn't. I did find that it has to do with the char 0x01 which is not printable. – Ed Vega Jan 12 '23 at 18:05
  • Found the answer.... $filenames = @(Get-Item "C:\Users\ed.vega\Desktop\FIX\MILLIMANFUT-CSFUT-FIX_4_4_202212052245000551.summary") $CR = "$([char]1)" foreach ($file in $filenames) {$outfile = "$file" + ".txt" Get-Content $file | Foreach-object { $_ -replace $CR,"`r`n" ` -replace [char]1,"|" ` } | Set-Content -encoding "UTF8" $outfile} – Ed Vega Jan 12 '23 at 20:29

1 Answers1

0
$filenames = @(Get-Item "C:\Users\username\Desktop\FIX\sending-target-FIX_4_4_202212052245000551.summary")

    $CR = "$([char]1)"
    foreach ($file in $filenames) {$outfile = "$file" + ".txt"
    Get-Content $file | Foreach-object {
            $_ -replace $CR,"`r`n" `
             -replace [char]1,"|" `
        } | Set-Content -encoding "UTF8" $outfile}
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574