Tag Info

Hot answers tagged

16

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


14

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.


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

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


10

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


9

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.


9

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

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


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

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


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

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

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


6

This will accomplish what you ask: awk -F';' '{print $1 ";" $2 ";" $3;}' <input >output The awk utility is well designed for this task. It can easily cut up individual lines into fields, then manipulate them based on that. The -F';' argument tells awk to use ; as the field separator. The quotes are necessary because the shell would interpret ; as a ...


6

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

The simplest way: sort -u -t, -k1,2 -k4 -u: output only first line of equals -t,: use comma as field separator -k1,2 -k4: sort only on fields 1,2 and 4 and the rest Another option is rearranging the data with sed (note the GNU option -r) on both sides - this requires the records to be mostly fixed-length, otherwise it's going to fail (and only hardly ...


6

There are many ways of doing this. Since you have not explained your issue with any detail, I am assuming that you are doing something like this: values=$(grep foo csv1.csv) echo "$values" > csv2.csv At any rate, you seem to be extracting some lines of one csv file and inserting them into another. To add an empty line to the beginning of the new csv ...


5

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


5

I'm using Perl's xls2csv to convert xls files to csv. Not sure tho if it works with xlsx too. About: It can't be comma separated unfortunately since some columns have commas in them that's why quoting has been introduced: 1,2,"data,data, more data"


5

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


5

It appears the join command can only join on one field [1,2], so: awk ' BEGIN {FS=OFS="\t"} NR==FNR {a[$1 FS $3] = $2 FS $4; next} $1 FS $2 in a {print a[$1 FS $2], $3} ' a.tsv b.tsv Update due to comment: since the given key is not unique, here's a technique to build up multiple entries from "a.tsv" awk ' BEGIN {FS=OFS="\t"} NR==FNR ...


5

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


5

One way to do this is with awk. You can set a threshold to get the first line the threshold is hit and last line it falls below. Something like this might work: awk -F, -vthreshold_up=20 -vthreshold_down=10 'BEGIN { cur = "gt"; } { if (cur == "gt" && ...


5

This will do the trick: echo "<table>" ; print_header=true while read INPUT ; do if $print_header;then echo "<tr><th>$INPUT" | sed -e 's/:[^,]*\(,\|$\)/<\/th><th>/g' print_header=false fi echo "<tr><td>${INPUT//,/</td><td>}</td></tr>" ; done < Medical.csv ; echo ...


5

I would recommend using a battle-tested CSV parsing module. For example: perl -MText::CSV -E ' $csv = Text::CSV->new({binary=>1}); while ($row = $csv->getline(STDIN)) {say $row->[3]} ' < file.csv col4 4th column 4th column2 or this produces the same results: ruby -rcsv -e 'CSV.foreach(ARGV.shift) {|row| puts row[3]}' file.csv ...


5

The only thing I'm aware of that commonly used a bare CR as a line terminator is old Mac systems (before Mac OS X) but unless it's a really old file that seems unlikely. In any case the mac2unix program in the dos2unix package should be able to fix it for you.


5

I guess you meant grep -f not grep -F but you actually need a combination of both and -w: grep -Fwf table.csv ids.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 ...


5

This one-liner produces the output with the minus signs: awk -F, '{if (NR>1) {print $1 "-" a "," $2 "-" b} a=$1 ; b=$2}' numbers 201310-201309,699-694 201311-201310,700-699 201312-201311,705-700 201401-201312,713-705 201402-201401,740-713 This version shows the actual differences: awk -F, '{if (NR>1) {print $1-a, $2-b } a=$1 ; b=$2}' numbers 1 5 1 ...


4

R is not my favorite programming language, but it is good for things like this. If your csv file is *********** foo.csv *********** col1, col2, col3 "this, is the first entry", this is the second, 34.5 'some more', "messed up", stuff Inside the R interpreter type > x=read.csv("foo.csv", header=FALSE) > x col1 ...


4

Given a space-delimited file in the following format: 1 2 3 4 5 You can remove field 2 with awk like so: awk '{ sub($2,""); print}' file which returns 1 3 4 5 Replace column 2 with column n where appropriate. To duplicate column 2, awk '{ col = $2 " " $2; $2 = col; print }' file which returns 1 2 2 3 4 5 To switch column 2 and 3, awk ...



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