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

22
votes
11answers
11k 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 ...
21
votes
4answers
6k 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 ...
21
votes
5answers
5k 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 ...
17
votes
4answers
49k 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 ...
16
votes
5answers
28k 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.
13
votes
4answers
5k 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 ...
12
votes
5answers
16k 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 ...
11
votes
8answers
2k 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.
9
votes
5answers
522 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 ...
9
votes
2answers
340 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 ...
8
votes
3answers
2k 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 ...
6
votes
4answers
1k 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 ...
6
votes
3answers
1k 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
4answers
4k 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, ...
6
votes
3answers
7k 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
1k 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
2k 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
2answers
4k 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
174 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
5answers
14k 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 ...
5
votes
2answers
1k 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 ...
5
votes
3answers
9k 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 ...
4
votes
5answers
13k 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> ...
4
votes
4answers
543 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
7k 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 ...
4
votes
3answers
927 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 ...
4
votes
6answers
6k 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" ...
4
votes
2answers
144 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
135 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 ...
3
votes
4answers
599 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, ...
3
votes
3answers
3k 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 ...
3
votes
1answer
1k 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
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. ...
3
votes
1answer
2k 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 ...
3
votes
4answers
1k 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 ...
3
votes
2answers
412 views

need shell script to grock a csv

Need some pointing in right direction on script to fetch and regex or sed. Site24x7 provides a URL with a CSV list of their source IP's used for monitoring. (they also provide other formats, CSV ...
3
votes
1answer
58 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 ...
3
votes
1answer
128 views

Wrapping long cells in a tsv to keep them in same column

Formatting a TSV with long cells with column makes its readability very low. It simply breaks lines like this: $ python3 -c 'for i in (1,2,3): print(((str(i)*50)+"\t")*3)' | column -s $'\t' -t ...
3
votes
1answer
470 views

An efficient filter for .sql files with just “INSERT INTO”

Several times I've found myself working on data that has been exported to .sql files that I want to process rather than import into a database. I'm only talking about "pure data" sql files. Ones that ...
3
votes
0answers
477 views

Catdoc xls2csv converting only one sheet

I have Catdoc's xls2csv installed on Debian/Squeeze. It used to work beautifully when I copied files to a folder like /var/www/xyz, converting all sheets into .csv format, separated by ^L. But now, ...
2
votes
3answers
6k 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" ...
2
votes
4answers
1k views

Format a Date field of a .CSV file with multiple commas in a string field

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 ...
2
votes
4answers
12k 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 ...
2
votes
4answers
1k views

gawk join two TSV by columns (a'la sql join)

How to join, to tsv files, examples: a.tsv c 7 r z d 6 s w f 1 f f b 8 p y a 9 q x b.tsv a q a c r ccc b p bb 0 0 0 d s dddd Here I'd like to ...
2
votes
3answers
6k views

Replacing the values in one file with the values in another file in bash

I am having a csv file naming List.csv in following format: Location,IP Address,Host Name,Domain,Domain Name, User Name,Manufacturer,Model,System Type, Serial Number, Operating System,RAM ...
2
votes
6answers
2k views

Handling comma in string values in a CSV file [duplicate]

I have a comma-separated file which has numeric and string columns. String columns are quoted and can have comma in between the quotes. How do I identify the columns with FS =","? Sample records ...
2
votes
2answers
2k views

Find the maximum length of characters in a field

Can you tell me how to find the maximum length of characters in a given field? For instance, with this input file: s.no,name 1,ATM 35,money 63,back The maximum length in field 1 is 2. So, I want ...
2
votes
2answers
295 views

Create an ASCII art table from tabular data

Given perhaps comma- or tab-delimited input, I'd like to present a series of appropriately padded columns to stdout, so I can easily scan columnar information which would otherwise present rather ...
2
votes
3answers
816 views

Extract data from a file and place in different files based on1 column value

We will generate a csv file with below values yp1234,577,1,3 yp5678,577,3,5 yp9012,132,8,9 I need to extract data and create files based on second column. If it's 577 then the whole line has to be ...
2
votes
3answers
395 views

I need data from a csv file which is not present in the second csv file

For example: I have a CSV file (first file ) with below data (vertically): 1 2 3 4 5 6 Second CSV with below data (vertically): 5 6 7 8 I want my resultant file (action needs to be performed ...