adding extra column in database

[Camilo de Barros]

Hi

I have a database compiled with Notepad++. After some 10000 records (lines) I decided to add an extra one to separate city state which at the moment appear like this in ONE column “Dallas TX” into TWO columns “Dallas” “TX”.

This is a complete record as it is followed by what I need to change WITHIN Notepad++ (adding the column in the database is the easy part)
CR.jpg,Courtyard,USA,St George UT,y,n,n

CR.jpg,Courtyard,USA,St George, UT,y,n,n

Notice the commas after St George

How can I do this in batches of several hyndreds?

Thank you

[Camilo de Barros]

@Camilo-de-Barros

I just realized that what needs doing is to REPLACE the SPACE after City with a COMMA

How can that be done, please’

[Camilo de Barros]

Sorry, new to this and unable to edit,

To add to the above:

but… it has to be the end of City as many cities have a space within them, e.g. New York, or even in the example in the OP: St George

[Camilo de Barros]

Well, I got somewhere: by searching [ \t]+ and replacing with , I achieve nearly all I need

The problem, as I feared before, is that it places a comma also in between the name of a town like New York.

So, how can I restrict the code to apply the code only in a certain place , for instance, before two capital letters, as it would be the only place where two capital letters appear, e.g. TX, or NY

[guy038]

Hello, Camillo de Barros,

From your post, seemingly, your list always ends, after the State, with 3 fields, after, which each contains, in your example, the values y, n and n.

So, if my assertion is exact and, supposing that the State, in capital letters , is a ONE-word, ONLY, it’s possible, using regular expressions, to replace the space, JUST BEFORE the state with a comma !

If so, follow the few steps, below :

• Open your list, in Notepad++

• Go back to the very beginning of your file ( CTRL + Origin )

• Open the Replace dialog ( CTRL + H )

• Check the Regular exprssion search mode

Fill the Find what and Replace with zones, as below :

Find what    :      (?=[^, ]+,([^,]+,){2}[^,\r\n]+$)

Replace with :     ,

• Click on the Replace All button

Et voilà !

IMPORTANT :

• In the search regex, above, there’s a SPACE :

  • Before the FIRST OPENING ROUND bracket

  • Before the FIRST ENDING SQUARE bracket

• There’s only ONE COMMA, in the replacement part

---

Camilo, if this regex succeeds to do the job, I’ll try to explain, next time, the search regex, that, I naturally admit, was quite difficult to build ! May be, someone will find a shorter regex ?

Best Regards,

guy038

[Camilo de Barros]

Hi, many thanks

Nearly there: it does the job with the city names but throws commas in other places, like between the State

MA.jpg,Marriott,USA,Park City ,U,T,n,y,n

[Camilo de Barros]

Hi

I managed to solve the problem with this: \s([A-Z]{2},)

But now that I can get to work on the file I had to alter, I find that once downloaded, it comes with inverted commas around every field and each record within brackets…

How can I get rid of these in Notepad++, please?

[Camilo de Barros]

EDIT ABOVEbolded text

But now that I can get to work on the file I had to alter, I find that once downloaded, it comes with single quotes around every field and each record within brackets…

[guy038]

Hi, Camilo,

Seeing the example you gave, I suppose that you forgot to begin the search regex with a SPACE !?

So the right search regex should be :

" (?=[^, ]+,([^,]+,){2}[^,\r\n]+$)"        WITHOUT the DOUBLE quotes, of course !

Cheers,

guy038

P.S. :

The search regex may, also, be written :

[ ](?=[^, ]+,([^,]+,){2}[^,\r\n]+$)

or, as well :

\x20(?=[^, ]+,([^,]+,){2}[^,\r\n]+$)

[Camilo de Barros]

Hi

is this to remove the single quotes (see my EDIT above? What do I replace it with?

I tried the above with \1 for the replace but nothing happened, and even with just find it says “can’t find the text”

To recap; I need to remove single quotes throughout the file, that were added during the download from the database

[guy038]

Hi, Camilo,

is this to remove the single quotes

Of course, NOT ! I was answering to your post :

Hi, many thanks

Nearly there: it does the job with the city names but throws commas in other places, like between the State

MA.jpg,Marriott,USA,Park City ,U,T,n,y,n

I’ve had to go out, a while but I’m back, in half-an-hour and I’ll try to reply to your two last posts !

See your later

guy038

[Camilo de Barros]

Sorry, the forum does not let me Edit or post again unless I wait 10 to 20 minutes, which makes progress difficult to broadcast!

[Camilo de Barros]

I’ve been doing some manually… but I guess I made lots of mistakes. Anyway, now I know that I have to get rid of all single quotes AND all white spaces.

[guy038]

Hi, Camilo and All,

Look, guys : I found a general, but difficult, regex but you, Camilo, you just pinpoint the right regex, which is quite adapted to your list. Bravo ! Moreover, against a big file, the regex engine will execute this regex faster than mime :-) I should have thought about it, some more time !

Of course, this implies that the state is a one-word, composed of two capital letters

So, instead of my complicated regex, it can be simply achieved with :

Find what     :     (?-i)\s([A-Z]{2},)

Replace with  :     ,\1

Note : I just added the (?-i), at the beginning of the regex, in order to force the regex engine to search in a sensitive way, whatever the Match case option is set or unset

About the single quotes problem, there are, usually, 3 kinds :

• The usual single quote ', ( Apostrophe ) of Unicode code \x27

• The ‘ ( Left single quotation mark ) of Unicode code \x{2018}

• The ’ ( Right single quotation mark ) of Unicode code \x{2019}

However, I’m a bit confused, as it seems rather easy to get rid of any of these 3 characters, with the simple S/R, below :

Find what     : '|\x{2018}|\x{2019}

Replace with  :   Leave EMPTY

Best Regards,

guy038