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 ...
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 ...
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 ...
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')....
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")'
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
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.
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 ...
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 ....
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 ...
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
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 ...
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 - | ...
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 ...
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 ...
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 ...
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 ...
23
votes
How do I get the character count of words in a particular column?
awk -F, '{sum+=length($3)}; END {print +sum}' file
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
...
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.||...
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 ...
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"| ...
16
votes
Merging contents of multiple .csv files into single .csv file
awk '(NR == 1) || (FNR > 1)' *.csv > 1000Plus5years_companies_data.csv
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 ...
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 ...
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 ...
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 ...
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
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
...
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 ...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
csv × 834text-processing × 329
awk × 327
shell-script × 154
sed × 152
linux × 104
bash × 95
shell × 46
command-line × 42
grep × 37
scripting × 33
files × 28
text-formatting × 27
columns × 25
json × 19
perl × 18
regular-expression × 17
sort × 17
date × 16
jq × 16
python × 14
array × 12
split × 12
join × 11
macos × 9