Community
    • Login

    adding extra column in database

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    14 Posts 2 Posters 7.6k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Camilo de BarrosC
      Camilo de Barros
      last edited by

      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 BarrosC 1 Reply Last reply Reply Quote 0
      • Camilo de BarrosC
        Camilo de Barros @Camilo de Barros
        last edited by

        @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’

        1 Reply Last reply Reply Quote 0
        • Camilo de BarrosC
          Camilo de Barros
          last edited by

          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

          1 Reply Last reply Reply Quote 0
          • Camilo de BarrosC
            Camilo de Barros
            last edited by

            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

            1 Reply Last reply Reply Quote 0
            • guy038G
              guy038
              last edited by

              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

              1 Reply Last reply Reply Quote 0
              • Camilo de BarrosC
                Camilo de Barros
                last edited by

                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

                1 Reply Last reply Reply Quote 0
                • Camilo de BarrosC
                  Camilo de Barros
                  last edited by

                  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?

                  1 Reply Last reply Reply Quote 0
                  • Camilo de BarrosC
                    Camilo de Barros
                    last edited by

                    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…

                    1 Reply Last reply Reply Quote 0
                    • guy038G
                      guy038
                      last edited by 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]+$)
                      
                      1 Reply Last reply Reply Quote 0
                      • Camilo de BarrosC
                        Camilo de Barros
                        last edited by

                        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

                        1 Reply Last reply Reply Quote 0
                        • guy038G
                          guy038
                          last edited by 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

                          1 Reply Last reply Reply Quote 0
                          • Camilo de BarrosC
                            Camilo de Barros
                            last edited by

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

                            1 Reply Last reply Reply Quote 0
                            • Camilo de BarrosC
                              Camilo de Barros
                              last edited by

                              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.

                              1 Reply Last reply Reply Quote 0
                              • guy038G
                                guy038
                                last edited by

                                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

                                1 Reply Last reply Reply Quote 0
                                • First post
                                  Last post
                                The Community of users of the Notepad++ text editor.
                                Powered by NodeBB | Contributors