adding extra column in database



  • 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

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

    How can that be done, please’



  • 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



  • 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



  • 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



  • 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



  • 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?



  • 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…



  • 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]+$)


  • 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



  • 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



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



  • 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.



  • 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


Log in to reply