Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Remove column from CSV files

    Help wanted · · · – – – · · ·
    remove column csv
    3
    7
    611
    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.
    • Jeshua Guzman
      Jeshua Guzman last edited by

      Hi,

      I want to remove a specific column from multiple csv files. There are 10 columns separated by commas, Please see example below

      09/08/2022,08:49:02,WWW1_005,604876.5,6383832.7,7.5,50939.33,0,2.2,0079-099_EEE_RR_EM2040D_TTTT_CCC_WWW1_005-0005.db
      09/08/2022,08:49:02,WWW1_005,604876.4,6383832.9,7.4,50939.33,0,2.2,0079-099_EEE_RR_EM2040D_TTTT_CCC_WWW1_005-0005.db

      I want to remove the last column containing these values 0079-099_EEE_RR_EM2040D_TTTT_CCC_WWW1_005-0005.db.

      Note: I cannot just use Alt+Shift+click as there are too many files, and I won’t be able to manually remove each independent column from 2000 csv files :)

      Any help will be appreciated,

      Thanks

      Alan Kilborn 1 Reply Last reply Reply Quote 0
      • Alan Kilborn
        Alan Kilborn @Jeshua Guzman last edited by Alan Kilborn

        @Jeshua-Guzman

        Something like this maybe:

        Find: (?-s)^((.+?,){8}.+?),.+
        Replace: ${1}
        Search mode: Regular expression

        will transform:

        09/08/2022,08:49:02,WWW1_005,604876.5,6383832.7,7.5,50939.33,0,2.2,0079-099_EEE_RR_EM2040D_TTTT_CCC_WWW1_005-0005.db
        09/08/2022,08:49:02,WWW1_005,604876.4,6383832.9,7.4,50939.33,0,2.2,0079-099_EEE_RR_EM2040D_TTTT_CCC_WWW1_005-0005.db
        

        into:

        09/08/2022,08:49:02,WWW1_005,604876.5,6383832.7,7.5,50939.33,0,2.2
        09/08/2022,08:49:02,WWW1_005,604876.4,6383832.9,7.4,50939.33,0,2.2
        
        Jeshua Guzman 1 Reply Last reply Reply Quote 2
        • Jeshua Guzman
          Jeshua Guzman @Alan Kilborn last edited by

          @Alan-Kilborn Amazing, it worked thanks! this made my day! :D

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

            Hello, @jeshua-guzman, @alan-kilborn and all,

            @jeshua-guzman said :

            I want to remove the last column…

            So , I suppose that we can use this alternative :

            • SEARCH ,[^,\r\n]+$

            • REPLACE Leave that field EMPTY


            Note :

            • However this regex S/R is less safe than the @alan-kilborn solution, as you must only run it ONCE only ! Indeed, any subsequent replacement would delete the present last column, …until all the columns, minus 1, would be consumed !

            Best Regards,

            guy038

            Jeshua Guzman 1 Reply Last reply Reply Quote 2
            • Jeshua Guzman
              Jeshua Guzman @guy038 last edited by

              @guy038 said in Remove column from CSV files:

              ,[^,\r\n]+$

              Thanks! this option also works. For future tasks, would you mind to explain how this works or how can I build it myself?

              Cheers,

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

                Hi, @jeshua-guzman and All,

                Not very difficult :

                • The $ symbol represents the zero-length string beteeen the last character of a line and its line-break chars

                • The [....] syntax represents a single character class character which matches any char within it

                • The [^....] syntax represents a single character class character which does not match any char, located inside it

                • The [^....]+ syntax represents any non-null range of characters which does not match any char, located inside it


                So, finally, the ,[^,\r\n]+$ regex means: searches for…

                • A litteral , character, followed with…

                • Any non-null range of characters ( + ) which are different ( ^ ) from, either ([....] ), the comma , and the possible line-break chars \n and \r till…

                • The very end of current line $


                USEFUL REFERENCES

                • Notepad++ Online User Manual: Searching/Regex

                • FAQ: Where to find regular expressions (regex) documentation

                • Template for Search/Replace Questions

                Best Regards,

                guy038

                Jeshua Guzman 1 Reply Last reply Reply Quote 3
                • Jeshua Guzman
                  Jeshua Guzman @guy038 last edited by

                  @guy038 Great thanks!

                  1 Reply Last reply Reply Quote 2
                  • First post
                    Last post
                  Copyright © 2014 NodeBB Forums | Contributors