6

How can I truncate the length of a column using CSVKit?

The definition looks like this:

  • Column 1: no length restriction
  • Column 2: <no more than 2 characters>

This should properly handle escaped (quoted) columns and new lines.

For example:

First Header,Second Header
foo,
foo,b
foo,bar
foo,"bar"
foo,"""bar"
foo,"
bar"

should become

First Header,Second Header
foo,
foo,b
foo,ba
foo,ba
foo,"""b"
foo,"
b"

3 Answers 3

4

Similar to r_31415's answer, but without having to name any other field than the field that we want to modify or remembering the order of the fields (also similar to roaima's answer in that respect):

csvsql --query 'UPDATE stdin SET "Second Header" = substr("Second Header",1,2); SELECT * FROM stdin' <file

A slightly more flexible approach which allows us to slot in any JSON-capable tool to do the needed transformations on the data:

  1. Using csvjson from csvkit to create a JSON array of the CSV data.
  2. Using jq to read the JSON data, and to truncate the Second Header values at two characters.
  3. Using in2csv from csvkit to read the JSON and to create CSV.
csvjson file | jq 'map(."Second Header" |= .[:2])' | in2csv -f json

The output of csvjson, given the CSV data in the question, is the equivalent of

[
  {
    "First Header": "foo",
    "Second Header": null
  },
  {
    "First Header": "foo",
    "Second Header": "b"
  },
  {
    "First Header": "foo",
    "Second Header": "bar"
  },
  {
    "First Header": "foo",
    "Second Header": "bar"
  },
  {
    "First Header": "foo",
    "Second Header": "\"bar"
  },
  {
    "First Header": "foo",
    "Second Header": "\nbar"
  }
]

The output of jq would then be

[
  {
    "First Header": "foo",
    "Second Header": null
  },
  {
    "First Header": "foo",
    "Second Header": "b"
  },
  {
    "First Header": "foo",
    "Second Header": "ba"
  },
  {
    "First Header": "foo",
    "Second Header": "ba"
  },
  {
    "First Header": "foo",
    "Second Header": "\"b"
  },
  {
    "First Header": "foo",
    "Second Header": "\nb"
  }
]

And the final output:

First Header,Second Header
foo,
foo,b
foo,ba
foo,ba
foo,"""b"
foo,"
b"
3

This is a solution with csvsql:

csvsql -d ',' --query 'SELECT "First Header" as "First Header",substr("Second Header",1,2) as "Second Header" FROM data' data.txt
First Header,Second Header
foo,
foo,b
foo,ba
foo,ba
foo,"""b"
foo,"
b"
3

I didn't think it was directly possible with CSVKit, until shown otherwise. However, I do know that it can be done with Miller:

mlr --csv put '${Second Header} = substr(${Second Header},0,1)' file.csv

Output

First Header,Second Header
foo,
foo,
foo,ba
foo,ba
foo,"""b"
foo,"
b"

(If you're using Miller version 6 or later there are better and preferred alternatives to substr. I can't test them here so I won't include them.)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .