comma delimited columns that has been done wrongly and I need to rearrange



  • Hi

    I have a long file with comma delimited columns that has been done wrongly and I need to rearrange it, preferably by getting all the columns tabulated, i.e. starting at the same distance from the left edge, so that I can then cut and paste a column from one place to another

    Here is an example:
    HI.jpg,HolidayInn,USA,IN,Portland,n,n,y
    HI.jpg,HolidayInn,USA,IN,Indianapolis,n,n,y
    HI.jpg,HolidayInn,USA,IN,Greensburg,n,n,y

    I need to eliminate the State (IN) and place it as part of the City with just a space:
    HI.jpg,HolidayInn,USA,Portland IN,n,n,y

    Ideally, can I do this to start?
    HI.jpg,HolidayInn,USA,IN,Portland ,n,n,y
    HI.jpg,HolidayInn,USA,IN,Indianapolis ,n,n,y
    HI.jpg,HolidayInn,USA,IN,Greensburg ,n,n,y

    So that I can then cut the IN column and paste it in front of the City?

    Thanks



  • Hello Camilo-de-Barros,

    with the given example a regex can do this.
    Open replace dialog, select regular expression and put the following into

    find what   : ^(.+?),(.+?),(.+?),(.+?),(.+?),(.+)$
    replace with: \1,\2,\3,\5 \4,\6
    

    What it does is:
    ^ = start of line
    (.+?), = looking for one or multiple chars followed by a comma consuming as less chars as possible
    (.+)$ = take the rest until end of line(eol)
    each \X represents its corresponding match of (.+?) or (.+)

    Cheers
    Claudia



  • Hi Claudia

    A small problem. The code below eliminates field 4 (Great!) but its contents (the 2 letter city code) must be inside field 5 (at the end with a space before it). Then the next 3 fields stay as they are: 7 fields in all.

    HI.jpg,HolidayInn,USA,Portland IN,n,n,y
    \1,\2,\3,\5 \4,\6

    Sorry…



  • Hello Camilo,

    did you try it?
    I did not eliminate match 4 I rearranged it \5 \4

    \1, = first match and comma
    \2, = second match and comma
    \3, = third match and comma
    \5 \4, fifth match and space and fourth match and comma
    \6 = rest until eol

    Cheers
    Claudia



  • Hi Claudia

    I should have tried it first. It’s working!

    One little problem though…

    Before I got your answer I downloaded XML Tools Plugin and played around with it. Now, the text has gone very small and I cannot change it in Style Configurator. I suppose I can always uninstall and reinstall Notepad++ but do you know what might be happening?

    Thanks

    Camilo



  • Hi Camilo,

    I assume you activate zooming accidentally by pressing CTRL and using the mouse wheel. Could this be?
    If so, VIEW->ZOOM->Restore default zoom might undo it.

    Cheers
    Claudia



  • Perfect!!!

    You’re an angel!

    Many thanks

    Camilo



  • Hello Camilo,

    In addition to the Claudia’s Search/Replacement, we could simplify and shorten a bit this S/R, as below :

    Find what:       ^(?:.+?,){3}\K(.+?,)(.+?),
    
    Replace with :   \2 \1
    

    Notes :

    • ^ represents the classical assertion Start of line

    • (?:.+?,){3} represents a non-capturing group, repeated three times, exactly, in order to go through the first three columns. This group is the smallest range of standard characters, with its comma delimiter

    • The \K syntax forces the regex engine to forget any previous match of the regex

    • So, the remainder, (.+?,)(.+?),, is the final match, which gets the 4th column along with the comma delimiter AND the 5th column, without its comma delimiter

    • In replacement, the syntax \2 \1 just swaps these two columns, with a space between them

    IMPORTANT :

    As the \K form is used, your MUST perform a global replacement, clicking on the Replace All button.

    The Step by Step replacement, with the Replace button, does nothing, in that specific case ( N++ bug ) !

    Best regards,

    guy038


Log in to reply