Tag Info

Hot answers tagged

26

sort has the -o, --output option that take a filename as argument. If it is the same as the input files, it write the result to a temporary file, then overwrite the original input file (somewhat as sed do). From GNU sort info page: `-o OUTPUT-FILE' `--output=OUTPUT-FILE' Write output to OUTPUT-FILE instead of standard output. Normally, `sort' ...


22

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


21

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 )


18

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


17

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


16

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.


16

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


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.


12

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


11

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


11

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


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

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


8

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


8

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


8

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


7

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!


7

#!/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 ...


7

That's pretty easy: sed '1d;s/\([^,]*\),\([^,]*\),\([^,]*\)/.\/mycommand --name="\1" --age="\2" --address="\3"/e' file.csv 1d will delete caption line. s command will modify the string like in your example e in the end of s command will execute the string. this is GNU extension, so if you don't have GNU sed, you can use xargs instead e: sed ...


7

CSV parsing is not easily done with POSIX tools only, unless you are using a simplified CSV variant with no quoting (so that commas can't appear in a field). Even then this task doesn't seem easy to do with awk or other text processing to tool. You can use Perl with Text::CSV, Python with csv, R with read.csv, Ruby with CSV, … (All of these are part of the ...


6

Similar to Iain's answer, you can also use tr: $ echo a,b,c | tr ',' '\n' a b c Both answers assume that the CSV is simple (that is, all commas are field separators). If you have something like a,"b,c",d where b,c is a single field, then things get more difficult


6

I'm not sure if that is enough for you, but you could make use of column program and read the selected parts of the file using head and/or tail like this: head -n 300 myfile.csv | tail -n 100 | column -ts ',' | less head -n-300 myfile.csv | head -n 100 | column -ts ',' | less You could wrap it up in some script to view different parts of the file at a ...


6

Aside from how to cut and re-arrange the fields (covered in the other answers), there is the issue of quirky CSV fields. If your data falls into this "quirky" category, a bit of pre and post filtering can take care of it. The filters shown below require the characters \x01,\x02,\x03,\x04 to not appear anywhere in your data. Here are the filters wrapped ...


6

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


6

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


6

It's dangerous to overwrite the input file with the output file, because if the program or the system crashes while the file is being written, you've lost both. A few programs (mostly GNU versions) have an in-place option (e.g. -i on perl and GNU sed; -o on GNU sort). They work by putting the data in a temporary file and then moving it into place. For ...


6

It can be done with awk using " as the field separator. But doing that, you must remember that $1 is empty, $2 holds the first string, $3 is the space between strings, $4 is the second string, etc. Also, it's more reliable to swap the two strings instead of just printing all the fields and hoping you put enough $ns. Bearing these in mind, the following ...


6

There is a good response, using sed simply one time with a loop: echo '123,"ABC, DEV 23",345,534,"some more, comma-separated, words",202,NAME'| sed ':a;s/^\(\([^"]*,\?\|"[^",]*",\?\)*"[^",]*\),/\1 /;ta' 123,"ABC DEV 23",345,534,"some more comma-separated words",202,NAME Explanation: :a; is a label for furter branch ...



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