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 ...
- 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 ...
- 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 ...
- 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')....
- 1,169
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")'
- 597
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
- 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.
- 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 ...
- 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 ....
- 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 ...
- 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
- 936
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 ...
- 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 - | ...
- 12.9k
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 ...
- 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 ...
- 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 ...
- 15.3k
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 ...
- 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
- 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
...
- 467k
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.||...
- 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 ...
- 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"| ...
- 37.9k
16
votes
Merging contents of multiple .csv files into single .csv file
awk '(NR == 1) || (FNR > 1)' *.csv > 1000Plus5years_companies_data.csv
- 161
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 ...
- 467k
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 ...
- 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 ...
- 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 ...
- 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
- 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
...
- 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 ...
- 268k
Only top scored, non community-wiki answers of a minimum length are eligible