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
|show 3 more comments|
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.
To remove a column, not print it:
To change the order:
Re-direct to an output file.
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
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