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

      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