Community
    • Login

    Change Value in specific column...

    Scheduled Pinned Locked Moved General Discussion
    4 Posts 3 Posters 3.4k 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.
    • Mike NYCM
      Mike NYC
      last edited by

      Hi All…

      I have a lot of csv files that I need to change a specific column from a blank to a “T”

      Column G

      Then resave it…

      Can it be done

      Thx…Capture.JPG

      PeterJonesP CoisesC 3 Replies Last reply Reply Quote 0
      • PeterJonesP
        PeterJones @Mike NYC
        last edited by PeterJones

        @Mike-NYC said in Change Value in specific column...:

        I have a lot of csv files that I need to change a specific column from a blank to a “T”

        Notepad++ doesn’t natively “understand” CSV in terms of tabular data: it just thinks of CSV as yet-another-text-file. So while it’s possible to craft a regular expression to search-and-replace in just a specific column (by requiring a certain number of commas before the match and replacement), it’s not an easy thing to do.

        I don’t remember whether the CSVLint plugin (available through Plugins > Plugins Admin) or the Columns++ plugin (not yet available in Plugins Admin, but this post links to the most recent alpha development version that can be downloaded and manually installed) are able to do column-aware replacements (but I’m sure @Coises, the author of Columns++, will chime in when he has read this).

        ----
        Update: Assuming the default comma for CSV (character-separated-values file), and assuming no commas in the values for any of the columns from A through F, and by “blank” you mean “cell contains nothing, not even spaces, so the CSV will be ,, for that cell” :

        FIND = ^([^,\r\n]*,){6}\K(?=,)
        REPLACE = T
        SEARCH MODE = Regular Expression
        REPLACE ALL

        That should look for an empty column G (which is the 7th column): it looks for the start-of-line followed by 6 pairs of “zero-or-more-non-comma/non-newline followed by comma” (ie, columns A-F and their separators), then resets the match (so the replacement won’t affect columns A-F), then if it immediately finds a comma next, it will replace the cell with T

        But if you try to adapt this to other data or other columns or other rules, or if your data doesn’t exactly match my assumptions, this isn’t likely to work (which is why I phrased it as “not an easy thing to do” – any expression you come up with will be highly constrained to the exact circumstances, rather than giving you a generic “rule” to follow for all similar replacements). Good luck.

        Useful References

        • Please Read Before Posting
        • Template for Search/Replace Questions
        • Formatting Forum Posts
        • Notepad++ Online User Manual: Searching/Regex
        • FAQ: Where to find other regular expressions (regex) documentation
        1 Reply Last reply Reply Quote 5
        • CoisesC
          Coises @Mike NYC
          last edited by

          @Mike-NYC It looks like you have a screenshot of the file open in a spreadsheet program. If you are already reasonably fluent with a program like Excel or one of its open-source clones, it would almost certainly be less error-prone to use the Find/Replace functions in the spreadsheet program than to try to get it right in Notepad++.

          If you still choose to work in Notepad++:

          @PeterJones mentioned my Columns++ plugin. It can do column-restricted replacement, but it does not work directly with CSV files; you would have to convert the file to tab-delimited, enable Elastic tabstops, and make a rectangular selection enclosing the column. The Search… function in Columns++ would then be able to do the desired replacement. Columns++ can convert from CSV and back again, though there are some details that require care to be sure you can convert back without unexpected changes.

          When making rectangular selections in tab-delimited files for Columns++, it’s generally easiest and best to include the trailing tab. So, when you go to do the find and replace, you’ll want to enable regular expressions and set Find what: ^ *\t$ and Replace with: T\t so that you match only cells containing blanks or nothing at all. When searching rectangular selections in Columns++, ^ and $ match the beginning and end of the selection in each row.

          Honestly, if you only have to do this once, the learning curve for Columns++ is probably not worth it. If you will often have to manipulate column-oriented data and prefer to avoid spreadsheet programs… that is the target use case for Columns++.

          1 Reply Last reply Reply Quote 4
          • CoisesC
            Coises @Mike NYC
            last edited by

            @Mike-NYC said in Change Value in specific column...:

            I have a lot of csv files

            I missed that part.

            At present, Columns++ only works on one open file at a time. So it probably won’t help you.

            The method @PeterJones described in the Update section of his post is, at the moment, the only method of which I’m aware that could be applied to many files at once (using Search | Find in Files…).

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