adding extra column in database

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