2022 Developer Survey is open! Take survey.

Hot answers tagged

77 votes

Using jq to extract values and format in CSV

jq has a filter, @csv, for converting an array to a CSV string. This filter takes into account most of the complexities associated with the CSV format, beginning with commas embedded in fields. (jq ...
user avatar
  • 910
65 votes
Accepted

Join multiple sed commands in one script for processing CSV file

First of all, as Michael showed, you can just combine all of these into a single command: sed '/^FOOTER/d; s/^\"//; s/\"$//; s/\"|\"/|/g' csv > csv1 I think some sed implementations can't cope ...
user avatar
  • 215k
58 votes

Is there a robust command line tool for processing csv files?

I am probably a little bit too late, but there is another tool worth mentioning: csvkit. It has a lot of command line tools that can: reformatting CSV files, convert to and from CSV from various ...
user avatar
  • 681
54 votes
Accepted

Converting CSV to TSV

Python Add to file named csv2tab, and make it executable touch csv2tab && chmod u+x csv2tab Add to it #!/usr/bin/env python import csv, sys csv.writer(sys.stdout, dialect='excel-tab')....
user avatar
48 votes

Using jq to extract values and format in CSV

I prefer to make each record a row in my CSV. jq '.data | map([.displayName, .rank, .value] | join(", ")) | join("\n")'
user avatar
43 votes
Accepted

How to merge two text files into one file without using cat or sed

Try paste command : paste -d';' File1 File2 > File3
user avatar
  • 12.5k
42 votes

Merging contents of multiple .csv files into single .csv file

The simplest approach for achieving that would be typing the following command cat *csv > combined.csv This file would contain the contents of all your csv files just in the way you mentioned.
user avatar
  • 611
42 votes
Accepted

How do I keep the first 200 lines of all the csv files in a directory using bash?

Assuming that the current directory contains all CSV files and that they all have a .csv filename suffix: for file in ./*.csv; do head -n 200 "$file" >"$file.200" done This outputs the first ...
user avatar
  • 268k
35 votes

Using jq to extract values and format in CSV

Given just this file, you can do something like: <testfile jq -r '.data | map(.displayName), map(.value) | join(", ")' The . operator selects a field from an object/hash. Thus, we start with ....
user avatar
  • 43k
30 votes

Remove Columns from a CSV File

To remove the fourth column, $ cut -d, -f4 --complement example.csv > input.csv Adjust the -f option to match the column number. If the CSV file is more complicated, you could use some perl and ...
user avatar
  • 490
30 votes

Converting CSV to TSV

Using csvkit (Python), for example: $ csvformat -T in.csv > out.txt Does streaming, with correct CSV and TSV quoting and escaping It's in apt and other package managers
user avatar
28 votes
Accepted

Turning separate lines into a comma separated list with quoted entries

You can add quotes with sed and then merge lines with paste, like that: sed 's/^\|$/"/g'|paste -sd, - If you are running a GNU coreutils based system (i.e. Linux), you can omit the trailing '-'. If ...
user avatar
  • 3,702
27 votes

Is there a command line utility to transpose a csv-file?

From https://stackoverflow.com/a/2776078 : $ apt-get install csvtool And then convert $ csvtool transpose input.csv > ouput.csv Or in pipeline $ ... | csvtool transpose - | ...
user avatar
25 votes

How do I keep the first 200 lines of all the csv files in a directory using bash?

Previous answers copy data and overwrite files. This technique should keep the same inodes, do no copying, and run a whole lot faster. For each file : (a) Find the length of each file by reading the ...
user avatar
  • 6,607
24 votes
Accepted

Keep only the lines containing exact number of delimiters

Another POSIX one: awk -F , 'NF == 11' <file If the line has 10 commas, then there will be 11 fields in this line. So we simply make awk use , as the field delimiter. If the number of fields is ...
user avatar
  • 143k
24 votes
Accepted

Replacing underscore by comma and removing double quotes in CSV

Far simpler way is to use tr $ tr '_' ',' < input.csv | tr -d '"' 1,1,0,0,76 1,1,0,0,77 1,1,0,0,78 The way this works is that tr takes two arguments - set of characters to ...
user avatar
24 votes

Converting CSV to TSV

For fun, sed. sed -E 's/("([^"]*)")?,/\2\t/g' file If your sed doesn't support -E, try with -r. If your sed doesn't support \t for a literal tab, try putting a literal tab (in many ...
user avatar
  • 6,929
23 votes

How do I get the character count of words in a particular column?

awk -F, '{sum+=length($3)}; END {print +sum}' file
user avatar
  • 81.5k
23 votes

How do I get the character count of words in a particular column?

cut -d, -f3 | tr -d '\n' | wc -m (remember that wc -c counts bytes, not characters: $ echo a,1,españa,2 | cut -d, -f3 | tr -d '\n' | wc -c 7 $ echo a,1,españa,2 | cut -d, -f3 | tr -d '\n' | wc -m 6 ...
user avatar
22 votes

Replace the comma with vertical bar |, except when inside double quotes, and remove double quotes

Using csvkit: $ csvformat -D '|' file.csv 12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc 25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc 87455|Capital of America, Inc.||...
user avatar
  • 268k
20 votes

AWK - print range of columns

The utility cut has a compact notation: cut -d, -f2-7 <input-file> producing: column2,column3,column4,column5,column6,column7 Answering the comment by @PlasmaBinturong: my intent was address ...
user avatar
  • 820
19 votes

Join multiple sed commands in one script for processing CSV file

This would also work: sed 's/^"//; s/"|"/|/g; s/""$/"/' Example: $ echo '"this"|" and "ths""|" and "|" this 2"|" also "this", "thi", "and th""' | sed 's/^"//; s/"|"/|/g; s/""$/"/' this| and "ths"| ...
user avatar
16 votes

Merging contents of multiple .csv files into single .csv file

awk '(NR == 1) || (FNR > 1)' *.csv > 1000Plus5years_companies_data.csv
user avatar
16 votes

Replace only first instance of a character

The g in: sed 's/,/;/g' is for globally, that is to substitute all occurrences of , with ;. If you want to do only one substitution per line, take off the g: sed 's/,/;/' And for completeness: You ...
user avatar
16 votes
Accepted

How to use Unix Shell to show only the first n columns and last n columns?

awk -F, '{print $1, $2, $(NF-1), $NF}' < input More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing ...
user avatar
  • 63.1k
15 votes
Accepted

How can I count the number of lines in a file after a grep match?

{ grep -m1 match; grep -c ''; } <file That will work w/ GNU grep and an lseek()able infile. The first grep will stop at 1 -match, and the second will -count every line remaining in input. Without ...
user avatar
  • 55.6k
15 votes

How do I keep the first 200 lines of all the csv files in a directory using bash?

Using sed with shell globbing: sed -ni '1,200p' *.csv Using globbing/sed/parallel: printf '%s\n' *.csv | parallel -- sed -ni '1,200p' {} This will find all .csv files in the current directory and ...
user avatar
  • 32.6k
14 votes
Accepted

Print lines where a specific column has a length condition

awk -F, 'length($26) != 10 { print }' /path/to/input > bad_field_length.txt
user avatar
  • 67.5k
14 votes

Converting CSV to TSV

One option might be perl's Text::CSV module e.g. perl -MText::CSV -lne 'BEGIN { $csv = Text::CSV->new() } print join "\t", $csv->fields() if $csv->parse($_) ' somefile to demonstrate ...
user avatar
  • 72.2k
14 votes

Substitute every comma outside of double quotes for a pipe

Using csvkit: $ csvformat -D '|' file.csv John|Tonny|345.3435,23|56th Street The tools in csvkit knows how to handle the intricacies of CVS files, and here we're using csvformat to replace the ...
user avatar
  • 268k

Only top scored, non community-wiki answers of a minimum length are eligible