Hot answers tagged

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 - | ...


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]}'


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 ...


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