69
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 1.5 has a similar filter, @tsv, for generating tab-separated-value files.)
Of course, if the headers and values are all guaranteed to be free of commas and ...
60
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 with that and might need:
sed -e '/^FOOTER/d' -e 's/^\"//' -e 's/\"$//' -e 's/\"|\"/|/g' csv > csv1
That said, it looks like your fields are defined by | ...
53
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 formats (JSON, SQL, XLS),
the equivalent of cut, grep, sort and others, but CSV-aware,
join different CSV files,
do general SQL queries on data from CSV files.
48
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').writerows(csv.reader(sys.stdin))
Test runs
$ echo 'A,,C,"D,E,F","G",I,"K,L,M",Z' | ./csv2tab
A C D,E,F G I K,L,M Z
$ ./...
44
I prefer to make each record a row in my CSV.
jq '.data | map([.displayName, .rank, .value] | join(", ")) | join("\n")'
43
Try paste command :
paste -d';' File1 File2 > File3
42
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 200 lines of each CSV file to a new file using head and a redirection. The new file's name is the same as the old but with .200 appended to the end of the name....
34
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.
33
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 .data, which returns the array with the data in it. We then map over the array twice, first selecting the displayName, then selecting the value, giving us two ...
32
Use awk:
awk -F, '{ print > $2 ".csv" }' file.csv
This will create the two files 577.csv and 132.csv in your current directory.
The command above assumes that you can only have 132 or 577 as the second field. It will create one file name for each of the values found in the second field of the entire file.csv.
If there are other values apart from the ...
28
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
27
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 you input data has DOS-style line endings (as @phk suggested), you can modify the command as follows:
sed 's/\r//;s/^\|$/"/g'|paste -sd, -
25
I guess you meant grep -f not grep -F but you actually need a combination of both and -w:
grep -Fwf ids.csv table.csv
The reason you were getting false positives is (I guess, you did not explain) because if an id can be contained in another, then both will be printed. -w removes this problem and -F makes sure your patterns are treated as strings, not ...
25
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 first 200 lines.
(b) Truncate the file to that length using truncate from GNU coreutils, or with the truncate found on some BSD systems:
SZ="$( head -n 200 -- ...
24
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 the Text::CSV package,
$ perl -MText::CSV -E '$csv = Text::CSV->new({binary=>1});
while ($row = $csv->getline(STDIN))
{
print "$row->[0],$row->[1],$...
24
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 be replaced, and their replacement. In this case we only have sets of 1 character. We redirect input.csv input tr's stdin stream via < shell operator, and pipe ...
23
awk -F, '{sum+=length($3)}; END {print +sum}' file
23
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
)
23
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 11, the condition NF == 11 is true, awk then performs the default action print $0.
22
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 shells, ctrl-v tab) or in Bash, use a $'...' C-style string (in which case the backslash in \2 needs to be doubled). If you want to keep the quotes, use \1 instead of \2 (in ...
22
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.||HORIZONCAPITAL|USA|......etc
csvkit is a collection of CSV manipulation/querying tools written in Python. These do proper CSV parsing and csvformat may be used ...
19
From https://stackoverflow.com/a/2776078 :
$ apt-get install csvtool
And then convert
$ csvtool transpose input.csv > ouput.csv
Or in pipeline
$ ... | csvtool transpose - | ...
answered Oct 5 '16 at 13:22
Grzegorz Wierzowiecki
12k20 gold badges80 silver badges126 bronze badges
19
awk -F '","' 'BEGIN {OFS=","} { if (toupper($5) == "STRING 1") print }' file1.csv > file2.csv
Output
"12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""
"23525","74535243123","string , with commas, and - hypens and: semicolans","string with or, without commas","string 1","...
18
With awk, you could do:
awk -F, -vOFS=, '{l=$0; $3=""}; ! ($0 in seen) {print l; seen[$0]}'
answered Sep 17 '13 at 11:02
Stéphane Chazelas
425k76 gold badges832 silver badges1259 bronze badges
18
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"| and | this 2| also "this", "thi", "and th"
pretty version
sed '
s/^"//
s/"|"/|/g
s/""$/"/
$d
'
16
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 can also specify which occurrence to substitute. For instance, to substitute only the second occurrence:
sed 's/,/;/2'
With GNU sed, you can also substitute all ...
answered Jul 25 '16 at 13:38
Stéphane Chazelas
425k76 gold badges832 silver badges1259 bronze badges
16
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 some columns twice --
awk -v n=2 '{
for(i=1; i <= n && i <= NF; i++)
printf "%s%s", $i, OFS
for(i=NF-n+1; i <= NF && i &...
15
{ 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 GNU grep:
{ sed '/match/q'; grep -c ''; } <file
Of course, w/ grep you can use any/all of its other options besides, and stopping at one match is not at ...
15
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
echo 'A,,C,"D,E,F","G",I,"K,L,M",Z' |
perl -MText::CSV -lne 'BEGIN { $csv = Text::CSV->new() }
print join "\t", $csv->fields() if $csv->parse($_)
'
A ...
15
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 feed them to GNU parallel which will execute a sed command on them to keep only the first 200 lines. Note this will overwrite the files in place.
Or using ...
Only top voted, non community-wiki answers of a minimum length are eligible
