Exchange columns in CSV

Exchange columns in CSV

2
NewbieNewbie
2

    May 27, 2014#1

    Hi,

    when I have a CSV, or other similar text file (separated by some character), where each separator marks two columns, is it possible to exchange one column with other?

    For example I have a file with format similar to:

    Code: Select all

    blog name:directory:installed extensions:user:password
    in this particular case separator is a colon, but it shouldn't matter. File can contain even up to 500 entries. Is it possible to change it to:

    Code: Select all

    user:password:blog name
    somehow?

    6,604548
    Grand MasterGrand Master
    6,604548

      May 27, 2014#2

      As long as there are no line breaks in double quoted field values (= number of lines is equal number of data rows) and the value delimiter also never exists within double quoted values, this is an easy task for an UltraEdit tagged regular expression Replace All or a Perl regular expression Replace All using backreferences.

      For your example with moving the data values 4 and 5 to beginning of a row and delete the data values 2 and 3, you can use

      with the UltraEdit regular expression engine:

      Find what: %^(*^):*:*:^(*^)$
      Replace with: ^2:^1

      with the Perl regular expression engine:

      Find what: ^(.*?):.*?:.*?:(.*?)$
      Replace with: \2:\1

      With the Perl regexp engine the last line of the file can be also without line terminator, with UltraEdit regexp engine the last line must have also a line terminator.

      The Perl regexp engine makes it easy to specify X values. For example it is possible to use following for the example:

      Find what: ^(.*?):(?:.*?:){2}(.*?)$
      Replace with: \2:\1

      (?:.*?:){2} matches now exactly two values in the CSV file which are not captured/marked for back-referencing.

      ?: immediately after opening parenthesis declares the group defined by an opening and a closing parenthesis as non-capturing group in a Perl regular expression.

      The Perl expression .*? means any character except the line terminators carriage return and line-feed zero or more times non greedy. Non greedy means as less characters as possible to get a positive match for the entire expression, or in other words here in this case, stop matching any character (except CRLF) on next colon and not on any later colon in the line.
      Best regards from an UC/UE/UES for Windows user from Austria

      2
      NewbieNewbie
      2

        May 28, 2014#3

        Thanks. I must admit that this piece of software wont stop amazing me.