How can I do the following to a CSV file using
- Delete a column
- Duplicate a column
- Move a column
I have a big table with over 200 rows, and I'm not that familiar with
Aside from how to cut and re-arrange the fields (covered in the other answers), there is the issue of quirky CSV fields.
If your data falls into this "quirky" category, a bit of pre and post filtering can take care of it. The filters shown below require the characters
Here are the filters wrapped around a simple
Note: field-five has an invalid/incomplete "quoted field" layout, but it is benign at the end of a row (depending on the CSV parser). But, of course, it would cause problematic unexpedted results if it were to be swapped away from its current end-of-row position.
Update; user121196 has pointed out a bug when a comma precedes a trailing quote. Here is the fix.
Here is the pre filter, expanded with comments.
This depends on whether your CSV file uses commas only for delimiters, or if you have madness like:
This assumes you're using a simple CSV file:
Removing a column
You can get rid of a single column many ways; I used column 2 as an example. The easiest way is probably to use
If you actually need to use
There are a number of ways to do this in
I find it easier to output field 1 and then use
This is annoying for columns further along though
Duplicating a column
(tcdyl came up with a better method in his answer)
Moving a column
I think the
To remove a column, not print it:
To change the order:
Re-direct to an output file.
Given a space-delimited file in the following format:
You can remove field 2 with awk like so:
Replace column 2 with column n where appropriate.
To duplicate column 2,
To switch column 2 and 3,
awk is generally very good at dealing with the concept of fields. If you're dealing with a CSV, and not a space-delimited file, you can simply use
to define your field as a comma, instead of a space (which is the default). There are a number of good awk resources online, one of which I list as a source below.
Source for #3