Files containing data arrange in a table, often with commas (hence Comma Separated Values), to separate columns. Rows are separated by newlines (but not all newlines are row separators as fields can be quoted to contain the separator newlines. Use this tag for full-fledged CSV data not the simpler ...

learn more… | top users | synonyms

42
votes
4answers
13k views

sort inplace - like sed --in-place - exists?

Am I blind or is there no option like --in-place for sort? In order to save results to the input file, sed uses -i (--in-place). Redirecting the output of sort to the input file sort < f > f ...
34
votes
13answers
16k views

Is there a robust command line tool for processing csv files?

I work with CSV files and sometimes need to quickly check the contents of a row or column from the command line. In many cases cut, head, tail, and friends will do the job; however, cut cannot easily ...
24
votes
2answers
1k views

How to merge two text files into one file without using cat or sed [duplicate]

I have two files File1: a b c File2: 1 2 3 now I need to combine them to one csv file a;1 b;2 c;3 As the files are really huge, I would rather not use cat and sed to process the second file. ...
23
votes
4answers
69k views

Convert a .xlsx (MS Excel) file to .csv on command line with semicolon separated fields

I realize that this is not an entirely unix/linux related question. But since this is something I'll do on linux, I hope someone has an answer. I have an online excel file (.xlsx) which gets updated ...
21
votes
5answers
6k views

Command line friendly spreadsheets

Does such a thing exist? Text-based spreadsheets that display well in a CLI environment. I'm aware that I could cat foobar.csvand do as I please, but it isn't particularly practical or attractive. I ...
19
votes
5answers
38k views

How to manipulate a CSV file with sed or awk?

How can I do the following to a CSV file using sed or awk? Delete a column Duplicate a column Move a column I have a big table with over 200 rows, and I'm not that familiar with sed.
19
votes
4answers
9k views

command to layout tab separated list nicely

Sometimes, I'm getting as an input tab separated list, which is not quite aligned, for instance var1 var2 var3 var_with_long_name_which_ruins_alignment var2 var3 Is there an easy way to render ...
16
votes
7answers
25k views

Remove comma between the quotes only in a comma delimited file

I have a input file delimited with commas (,). There are some fields enclosed in double quotes that are having a comma in them. Here is the sample row 123,"ABC, DEV 23",345,534.202,NAME I need to ...
13
votes
5answers
1k views

How can I count the number of lines in a file after a grep match?

I am trying to count the number of lines after a problematic row in a csv file. I am aware I can use the grep -a # syntax to output # number of lines after a match has been found. I'm only interested ...
11
votes
8answers
3k views

How do I get the character count of words in a particular column?

I have a CSV file like this: abd,123,egypt,78 cde,456,england,45 How can I get the character count of only the 3rd column words? I can't figure out how to get wc to do this.
10
votes
6answers
757 views

Remove lines from a file depending on lines found in another file

File file1.txt contains lines like: /api/purchase/<hash>/index.html For example: /api/purchase/12ab09f46/index.html File file2.csv contains lines like: <hash>,timestamp,ip_address ...
10
votes
6answers
18k views

Using jq to extract values and format in CSV

I have the below JSON file: { "data": [ { "displayName": "First Name", "rank": 1, "value": "VALUE" }, { "displayName": "Last Name", "rank": 2, ...
10
votes
2answers
457 views

Reindexing a large CSV file

I went through the answers in this helpful thread, but my problem seems to be different enough that I can't think of good answer (at least with sed). I have a large CSV file (200+ GB) with rows that ...
9
votes
2answers
2k views

How to display TSV (csv) in console, when empty cells are missed by: `column -t -s $'\t' `

I have file with columns spearated with tab. I have file when some rows have empty cells (on begining, in middle). In such cases column -t -s $'\t' simply fails: Input: $ echo -e 'A\tB\tC\tD\n\tb1\...
8
votes
3answers
4k views

Remove only the commas present within the double quotes

In a text file, I want to remove , (commas) and also the " (quotes) (only if the double quotes contains numbers seperated by commas). 56,72,"12,34,54",x,y,"foo,a,b,bar" Expected ouput 56,72,...
7
votes
4answers
2k views

uniq a csv file ignoring a column, awk maybe?

Given this file (annotations are not part of file, but form part of explanation)... x,a,001,b,c,d,y x,a,002,b,c,e,yy x,bb,003,b,d,e,y x,c,004,b,d,e,y x,c,005,b,d,e,y # nb - dupe of row 4 x,dd,006,b,...
7
votes
5answers
28k views

Parsing a delimited text file in bash as command arguments

I have a text file split up like so: field1,field2,field3 xield1,xield2,xield3 dield1,dield2,dield3 gield1,gield2,gield3 Each of these columns will be a parameter to a program, and I would like ...
6
votes
7answers
711 views

Keep only the lines containing exact number of delimiters

I have a huge csv file with 10 fields separated by commas. Unfortunately, some lines are malformed and do not contain exactly 10 commas (what causes some problems when I want to read the file into R). ...
6
votes
3answers
2k views

adding an empty first line inside csv file

I input data into a csv by 'echo'-ing some values into another csv. I tried echo "" and in the next line echo "values" but i get intermittent empty lines.. I need empty line only at the start....
6
votes
2answers
2k views

Join multiple sed commands in one script for processing CSV file

Having a CSV file like this: HEADER "first, column"|"second "some random quotes" column"|"third ol' column" FOOTER and looking for result like: HEADER first, column|second "some random quotes" ...
6
votes
3answers
12k views

Filter a .CSV file based on the 5th column values of a file and print those records into a new file

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , ...
6
votes
3answers
2k views

Use `less` to view comma-aligned data

I have a big CSV file. I would like to view my file using less or some command like it which doesn't have to read the whole file at once to show me part of it. Is there a command out there which can ...
5
votes
4answers
3k views

A unix command to truncate each line of a file

I have a CSV file and I want to truncate it from the third semicolon. For example, if I have this file: 1;foo;bar;baz;x;y;z 2;foo;bar;baz;x;y;z 3;foo;bar;baz;x;y;z I want to get the following ...
5
votes
5answers
20k views

extract the 4th column from a csv file using unix command

I have a csv file formatted as below. "col1","col2","col3","col4" "1","text1","<p>big html text</p> ","4th column" "2","text2","<p>big2 html2 text2</p> ",...
5
votes
7answers
794 views

Remove lines from tab-delimited file with missing values

I have a large (~900MB) tab-delimited text file that I will be processing in a downstream program. I need to delete any line with a missing value. The correct number of columns are on each line (so ...
5
votes
5answers
8k views

How can I convert tab delimited data to comma delimited data?

I'm requesting a list of ec2 snapshots via amazon's ec2 command line tool: ec2-describe-snapshots -H --hide-tags > snapshots.csv The data looks something like this: SnapshotId VolumeId ...
5
votes
6answers
9k views

How to swap columns in such a file?

I have a text file, each line is stored like this : "Video or movie" "parent" "Media or entertainment" "1" "1" "1" "0" "0" I want to swap the columns 3 with 2, i.e. "Video or movie" "...
5
votes
2answers
6k views

Is there a command line utility to transpose a csv-file?

Given a file like so First, Last, Age Cory, Klein, 27 John Jacob, Smith, 30 Is there a command line utility to transpose the contents so the output appears like so First, Cory, John Jacob Last, ...
5
votes
3answers
190 views

Robustly segmenting a csv file containing noisy data

Say I have a csv file with these columns: timestamp,cpu,memory An example of the shape of the actual data would be this chart: Having the data visualized in this way, it's easy for the human eye ...
5
votes
3answers
14k views

Search for values in 2 columns from CSV

I have a csv files with say 20 odd columns. I need to get the 14th and 15th column values where value in 14th column is say "VALUE1" and value in 15th column is "VALUE2". My condition gets satisfied ...
5
votes
3answers
276 views

update line based on previous line

I have a issue since I'm not familiar with awk. I have csv file generated from sar -d output converted to csv style: 12:33:41 unix,restarts 12:35:00,lofi4096,0,0.0,0,0,0.0,0.0 ,iscsi0,0,0.0,0,...
5
votes
4answers
3k views

Concatenate filename inside of CSV file for each line for multiple CSV files

I have lots of CSV files and I need the filename of each specific CSV file in every line of each file. Original file content of abc123.csv: ColVal_1;ColVal_2;ColVal_3 ColVal_4;ColVal_5;ColVal_6 ...
4
votes
4answers
832 views

How to Prefix a column values with an apostrophe ( ' )?

I have a CSV file with multiple columns and 1000's of records, I need to prefix all the values of one of the columns (lets say 2nd column) with an apostrophe ' except in the first line or header line, ...
4
votes
4answers
1k views

Convert key=value blocks to CSV

I am trying to transpose a content of a file into another. I am new to shell scripting any help would be really appreciated Input file Test.txt: HLRSN = 3 IMSI = 404212109727229 KIVALUE = ...
4
votes
5answers
222 views

How do I print all lines of a file with duplicate values in a certain column

Given a file like this: 1,768,12,46576457,7898 1,123,435,134,146 2,345,6756856,12312,1311 5,234,567465,12341,1341 1,3245,4356345,2442,13 9,423,2342,121,463 9,989,342,121,1212 I would like to list ...
4
votes
3answers
1k views

Using CSV line as command parameters

I have a CSV file like: Name,Age,Address Daniel Dvorkin,28,Some Address St. 1234 (... N ...) Foo Bar,90,Other Address Av. 3210 And I have a command that take this parameters: ./mycommand --name="...
4
votes
2answers
240 views

awk pairwise differences

I run into this problem a lot with data, if I have some integers, I frequently want to look at differences between adjacent integers, I usually solve this in Ruby or Python, but I think such a thing ...
4
votes
3answers
7k views

Select lines from text file which have ids listed in another file

I use a lot of grep awk sort in my unix shell to work with medium-sized (around 10M-100M lines) tab-separated column text files. In this respect unix shell is my spreadsheet. But I have one huge ...
4
votes
3answers
128 views

Delete consecutive lines in CSV with duplicate values in one field, but keep the last line

I have a long CSV file with two columns, which includes runs of consecutive duplicates like this: ... 1500,1533 1554,1678 1554,1703 1554,1728 1593,1766 ... I need to delete all of these duplicates ...
4
votes
3answers
2k views

replace column of a csv file with column from another file

I have two sample files like this: $ cat file1 abc,sachin cat,kumar $ cat file2 xyz pressure $ cat file3 xyz,sachin pressure, kumar I want the first column of file1 to be replaced with file2. ...
4
votes
2answers
40 views

Is it possible to print an iterated range of arguments via cli from a csv? (see example)

I have hundreds of logs spread across a few thousand VM's and I'm trying to create a few scripts to quickly scan logs and I have figured out the majority of the presentation and wanted to know if ...
4
votes
1answer
2k views

merge csv files by first column

I have 3 csv files like this. csv 1: 1,aaaa,bbb,2014-04-01 2,qwe,rty,2014-04-03 3,zxc,cvb,2014-04-05 csv 2: 2,j,k,2014-04-01 3,a,s,2014-04-04 5,g,h,2014-04-08 csv 3: 2,a,s,d,f,g,2014-04-01 3,...
4
votes
1answer
6k views

How to export data in the terminal to a file (e.g. csv file)

I am a complete beginner in using Linux and I would like to know how to export a bunch of data in the command line to a csv or spreadsheet file (or even text would work). Right now I am simply using ...
4
votes
3answers
328 views

How can I change the first line of a big gzip file without decompressing all of it?

I currently have a compressed file, A.gz which contains lots of tabulated data, including a header in the first line. I want to create another file, B.gz, which has the same data as the previous file, ...
4
votes
3answers
316 views

Compare two files with four columns

I have two csv files with some log information. I need to compare line to line the field1 (file1) with field2 (file2) with a range of 1 minute (-00:00:01 < time < 00:00:01) and the field5 (file1)...
3
votes
4answers
20k views

How can I find and replace with a new line?

I have a CSV delimited by commas and I want to delimit it by newlines instead. Input: a, b, c Output: a b c I've written Java parsers that do this stuff, but couldn't this be done with vim or ...
3
votes
4answers
9k views

Extracting column from comma separated text

I have a long comma-separated delimited file with 20K lines. Here's a sample: "","id","number1","number2","number3","number4","number5","number6","number7" "1","MRTAT_1of3.RTS",17.1464602742708,17....
3
votes
4answers
3k views

How to rename files with sed and csv

I'm new with Linux, sed, and awk but I don't mind challenging myself on new ideas. That being said, I understand the purpose and how to use rename and sed for a common event such as adding a $date or ...
3
votes
5answers
663 views

Efficient data extraction from multiple files to a single CSV file

I have a large collection of XML files with the same exact structure: $ cat file_<ID>.xml ... ... ... <double>1.2342</double> <double>2.3456</double> ...
3
votes
2answers
3k views

Print some fields of /etc/passwd

I have a question concerning the usage of wild cards in Linux. Here I have some lines from a long list of lines in the /etc/passwd -file. root:x:0:0:root:/root:/bin/bash daemon:x:1:1:daemon:/usr/...