Change Value in specific column...

Mike NYC

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…

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

Coises

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

Coises

@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…).