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