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

[Camilo de Barros]

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

[Claudia Frank]

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

[Camilo de Barros]

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…

[Claudia Frank]

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

[Camilo de Barros]

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

[Claudia Frank]

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

[Camilo de Barros]

Perfect!!!

You’re an angel!

Many thanks

Camilo

[guy038]

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