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