66
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.
- 867
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,229
43
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 ...
- 341k
35
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 - | ...
- 14.4k
33
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 ...
- 520
33
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,852
33
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
- 985
31
votes
Merging contents of multiple .csv files into single .csv file
awk '(NR == 1) || (FNR > 1)' *.csv > 1000Plus5years_companies_data.csv
- 327
25
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 ...
- 16.7k
25
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 ...
- 7,824
24
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 ...
- 9,104
22
votes
Is there a robust command line tool for processing csv files?
Miller is another nice tool for manipulating name-based data, including CSV (with headers). To extract the first column of a CSV file, without caring about its name, you’d do something like
printf '&...
- 450k
21
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
- 77.4k
21
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.||...
- 341k
21
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 ...
- 838
20
votes
Merging contents of multiple .csv files into single .csv file
use paste
paste -d ',' file1.csv file2.csv ... fileN.csv
- 993
18
votes
Accepted
Using AWK to select rows with specific value in specific column
The file that you run the script on has DOS line-endings. It may be that it was created on a Windows machine.
Use dos2unix to convert it to a Unix text file.
Alternatively, run it through tr:
tr -...
- 341k
17
votes
Merging contents of multiple .csv files into single .csv file
Use csvstack from csvkit:
csvstack *.csv > out.csv
- 279
16
votes
Is there a robust command line tool for processing csv files?
I'd recommend xsv, a "fast CSV command line toolkit written in Rust".
Written by Ripgrep's author.
Featured in How we made our CSV processing 142x faster (Reddit thread).
- 261
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 ...
- 558k
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 ...
- 67.8k
16
votes
Accepted
Convert JSON array into CSV
The issue is not really that the JSON that you show is an array, but that each element of the array (of which you only have one) is a fairly complex structure. It is straight forward to extract the ...
- 341k
15
votes
Accepted
Is there a command line utility to transpose a csv-file?
ruby -rcsv -e 'puts CSV.parse(STDIN).transpose.map &:to_csv' < in.csv > out.csv
- 266
15
votes
Accepted
Compact way to get tab-separated fields into variables
Yes:
while read -r width height size thedate thetime; do
# use variables here
done <file
This will read from standard input and split the data on blanks (spaces or tabs). The last variable ...
- 341k
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 ...
- 38.1k
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
...
- 82.3k
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 ...
- 341k
14
votes
How can SQLite command ".import" read directly from standard input?
I found another solution that still uses sqlite3, but that doesn't read /dev/stdin or a temporary named pipe. Instead, it uses .import with the pipe operator to invoke cat - to read directly from ...
- 547
14
votes
awk script to prepare csv file
awk '
BEGIN {
split("Monday Tuesday Wednesday Thursday Friday Saturday Sunday",days)
FS=OFS=","
}
NR > 1 {
gsub(/"/,"")
...
- 33k
14
votes
Find and add quotes in between particular string
Using csvformat from csvkit, and assuming that the end result should be a CSV file with comma as delimiter (as described in the text of the question):
$ csvformat -d '|' file
1,"a,b",4
1,&...
- 341k
Only top scored, non community-wiki answers of a minimum length are eligible