Editing CSV columns query

Welcome Forums General PowerShell Q&A Editing CSV columns query

  • This topic has 1 reply, 2 voices, and was last updated 1 month ago by
    Participant
    .
Viewing 1 reply thread
  • Author
    Posts
    • #234916
      Participant
      Topics: 1
      Replies: 0
      Points: -8
      Rank: Member

      Hi all,

      So I’ve had a a request to edit a csv file by replacing column values with a a set of unique numbers.  Below is a sample of the original input file with a a header line followed by a couple of rows.  Note that the rows have NO column headers.

      HH ### SLH304 01100001 2 20200606 20200605 011100
      DD|GZFD|IE|USD|20200605|EUR200717||
      DD|GZFD|IE|USD|20200605|EUR200717||

      What I’m looking to do is change say the values in column 3 with a unique number.

      So far I have the following …

      
      $i=0
      $txtin = Get-Content "C:\Temp\InFile.txt" | ForEach {"$($_.split('|'))"-replace $_[2],$i++} |Out-File C:\Temp\csvout.txt
      
      

      … but this isn’t working as it removes the delimiter and adds numbers in the wrong places …

      HH0###0000000SLH30400110000100000002000000202006060202006050011100
      1D1D1 1G1Z1F1D1 1I1E1 1U1S1D1 12101210101610151 1E1U1R1210101711171 1 1
      2D2D2 2G2Z2F2D2 2I2E2 2U2S2D2 22202220202620252 2E2U2R2220202721272 2 2

      Any ideas on how to resolve would be much appreciated.

      • This topic was modified 1 month ago by BobbyS70. Reason: Sorry I'm new to this so I've amended to add formatting. Also, it's a txt file not a csv file so I've corrected that
    • #234922
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

      Rob, welcome to Powershell.org. Please take a moment and read the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!.

      When you post code, error messages, sample data or console output format it as code, please.
      In the “Text” view you can use the code tags “PRE“, in the “Visual” view you can use the format template “Preformatted“. You can go back edit your post and fix the formatting – you don’t have to create a new one.
      Thanks in advance.

      If your CSV file is a valid CSV file you should treat it as such and use Import-Csv instead of Get-Content and Export-Csv instead of Out-File. Please read the help completely including the examples to learn how to use it.

Viewing 1 reply thread
  • You must be logged in to reply to this topic.