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