Hot answers tagged

38

sort has the -o, --output option that takes a filename as argument. If it is the same as the input file, it writes the result to a temporary file, then overwrites the original input file (exactly the same thing as what sed -i does). From GNU sort info page: `-o OUTPUT-FILE' `--output=OUTPUT-FILE' Write output to OUTPUT-FILE instead of standard ...


23

You can use Python's csv module. A simple example: import csv reader = csv.reader(open("test.csv", "rb")) for row in reader: for col in row: print col


22

awk -F, '{sum+=length($3)}; END {print +sum}' file


22

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 )


20

So, the answer becomes: column -t file_name P.S.: Just want to point out that the credit goes to Alex as well. The original hint was provided by him as a comment to the question, but was never posted as an answer.


17

I am probably a little bit too late, but there is another tool worth mentioning: csvkit http://csvkit.readthedocs.org/ It has a lot of command line tools that can: convert to and from csv from various formats (json, sql, xls) cut, grep, sort and others join different csv files!


17

If the quotes are balanced, you will want to remove commas between every other quote, this can be expressed in awk like this: awk -F'"' -v OFS='' '{ for (i=2; i<=NF; i+=2) gsub(",", "", $i) } 1' infile Output: 123,ABC DEV 23,345,534.202,NAME Explanation The -F" makes awk separate the line at the double-quote signs, which means every other field ...


17

With awk, you could do: awk -F, -vOFS=, '{l=$0; $3=""}; ! ($0 in seen) {print l; seen[$0]}'


16

For the sake of variety, here's another way with cut: cut -d \; -f -3


15

There's sc and oleo (neither of which I have any experience with). Being a GNU project, however, oleo's handling is more likely to be emacsesque than vimmy.


14

OpenOffice comes with the unoconv program to perform format conversions on the command line. unoconv -f csv filename.xlsx For more complex requirements, you can parse XLSX files with Spreadsheet::XLSX in Perl or openpyxl in Python. For example, here's a quickie script to print out a worksheet as a semicolon-separated CSV file (warning: untested, typed ...


13

cut -d / -f 4 file1.txt | paste -sd '|' | xargs -I{} grep -v -E {} file2.csv Explanation: cut -d / -f 4 file1.txt will select the hashes from the first file paste -sd '|' will join all the hashes into a regular expression ex. H1|H2|H3 xargs -I{} grep -v -E {} file2.csv will invoke grep with the previous pattern as an argument, xargs will replace {} with ...


13

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.


12

This depends on whether your CSV file uses commas only for delimiters, or if you have madness like: field one,"field,two",field three This assumes you're using a simple CSV file: Removing a column You can get rid of a single column many ways; I used column 2 as an example. The easiest way is probably to use cut, which lets you specify a delimiter -d ...


12

Not near a terminal to test, but how about the oft-overlooked nl command? Something like: cut -f 2 -d , original.csv | nl -w 1 -p -s , > numbered.csv


11

Possible awk solution: awk 'NR == FNR { x[$4] = 1; next; } { if (!($1 in x)) print $0; }' FS="/" file1.txt FS="," file2.txt First we read file1.txt using FS (field separator) "/" and create array x with keys values from field $4 which is the hash you want. Next we read second file file2.txt setting FS to be , and check if value of field $1 does not exist ...


10

Sounds like a job for Perl with Text::CSV. perl -MText::CSV -pe ' BEGIN {$csv = Text::CSV->new();} $csv->parse($_) or die; @fields = $csv->fields(); print @fields[1,3]; ' See the documentation for how to handle column names. The separator and quoting style can be tuned with parameters to new. See also Text::CSV::Separator for ...


10

UPDATE: Actually, a much easier way is to set the record separator in gawk: $ gawk 'BEGIN{RS="\"\n"; FS=","}{print $4}' myFile.csv "col4 "4th column "4th column2 However, this will remove the trailing " from the end of each column. To fix that you can print it yourself: $ gawk 'BEGIN{RS="\"\n"; FS=","}{print $4"\""}' myFile.csv "col4" "4th column" "4th ...


10

If perl is OK, here is a short (and probably fast, if not necessarily simple :) ) way of doing it: perl -pe 's:"(\d[\d,]+)":$1=~y/,//dr:eg' file The e flag to the s::: operator (which is just another way of writing s///) causes the replacement to be treated as an expression which is evaluated every time. That expression takes the $1 capture from the regex ...


9

awk is your best bet. awk prints fields by number, so... awk 'BEGIN { FS=","; OFS=","; } {print $1,$2,$3}' file To remove a column, not print it: awk 'BEGIN { FS=","; OFS=","; } {print $1,$3}' file To change the order: awk 'BEGIN { FS=","; OFS=","; } {print $3,$1,$2}' file Re-direct to an output file. awk 'BEGIN { FS=","; OFS=","; } {print ...


9

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


9

Simply: awk -v RS= -v OFS=, '{print $3,$6,$9,$12,$15,$18}' An empty record separator (RS=) enables the paragraph mode whereby records are separated by sequences of empty lines. Inside a record, the default field separator applies (records are separated by blanks) so in each record, the fields we are interested in are the 3rd, 6th, 9th... We change the ...


9

Here's one way. $ cat foo aaa bbb ccc ddd eee fff $ awk '/^ddd/{a=FNR}END{print FNR-a}' foo 2 $


8

#!/usr/bin/awk -f BEGIN { FS = "\t"; OFS = "," } { for(i = 1; i <= NF; i++) { if ($i + 0 == $i) { $i = "=" $i } else gsub(/"/, "\"\"", $i); $i = "\"" $i "\"" } print } Assuming you name this convert.awk, you can either call with either ec2-describe-snapshots -H --hide-tags | awk -f convert.awk > snapshots.csv ...


8

You can just use Debian's column. It provides the option -n which makes it work exactly how you want. Alternatively, you can put a space in the empty columns, using sed: sed ':x s/\(^\|\t\)\t/\1 \t/; t x' < in.tsv | column -t -s $'\t' example: $ sed ':x s/\(^\|\t\)\t/\1 \t/; t x' < in.tsv | column -t -s $'\t' A B C D b1 d1 ...


8

awk -F , -v OFS='\t' 'NR == 1 || $6 > 4 {print $1, $6, $7, $8}' input.txt


8

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


8

With awk: awk -F"\t" '$1!=""&&$2!=""&&$3!=""' file Actually it is that simple. awk splits the input at the field separator tab \t specified with the -F flag. This could also be omitted, when your content has no spaces in the fields. $1!=""&&... is a condition. When this condition is true, awk simply prints the line. You could ...


8

To try and avoid storing the whole file in memory, you could do: awk -F , ' !count[$1]++ {save[$1] = $0; next} count[$1] == 2 { print save[$1] delete save[$1] } {print}'



Only top voted, non community-wiki answers of a minimum length are eligible