Change delimiters and quote characters in CSV files
Perl
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
LICENSE
README
csv2csv

README

Convert the delimiter or quote character of a CSV file.

Requires perl, Text::CSV, and Getopt::Long.

Examples:

Imagine we have a file, colors.csv, with the following data:

Id,Color,Color Name,Description,Hex #,Inventory,Add Date
1,red,Red,Having the color of blood,#f00,0.25,2014-10-16
2,green,Green,Having the color of growing grass,#0f0,10.18,2014-08-25
3,blue,Blue,Having the color of the clear sky,#00f,4.67,2014-09-17

To do "nothing" with the file at all:
~$ csv2csv --in-file colors.csv
Id,Color,"Color Name",Description,"Hex #",Inventory,"Add Date"
1,red,Red,"Having the color of blood",#f00,0.25,2014-10-16
2,green,Green,"Having the color of growing grass",#0f0,10.18,2014-08-25
3,blue,Blue,"Having the color of the clear sky",#00f,4.67,2014-09-17

Note that this quoted a number of fields, which shouldn't affect how the CSV is
interpreted.

Alternatively, one can do nothing with:
~$ cat colors.csv | csv2csv
Id,Color,"Color Name",Description,"Hex #",Inventory,"Add Date"
1,red,Red,"Having the color of blood",#f00,0.25,2014-10-16
2,green,Green,"Having the color of growing grass",#0f0,10.18,2014-08-25
3,blue,Blue,"Having the color of the clear sky",#00f,4.67,2014-09-17

To change the delimiter:
~$ cat colors.csv | csv2csv --out-delimiter '|'
Id|Color|"Color Name"|Description|"Hex #"|Inventory|"Add Date"
1|red|Red|"Having the color of blood"|#f00|0.25|2014-10-16
2|green|Green|"Having the color of growing grass"|#0f0|10.18|2014-08-25
3|blue|Blue|"Having the color of the clear sky"|#00f|4.67|2014-09-17

Or to a tab:
~$ cat colors.csv | csv2csv --out-delimiter '\t'
Id	Color	"Color Name"	Description	"Hex #"	Inventory	"Add Date"
1	red	Red	"Having the color of blood"	#f00	0.25	2014-10-16
2	green	Green	"Having the color of growing grass"	#0f0	10.18	2014-08-25
3	blue	Blue	"Having the color of the clear sky"	#00f	4.67	2014-09-17

Additional options exist to change the quote character (default ") and the eol
(default \n) for both input and output interpretation.

Additionally, there is a --transform option for transforming input:
~$ cat colors.csv | csv2csv --transform '$row->[1] = uc($row->[1])'
Id,COLOR,"Color Name",Description,"Hex #",Inventory,"Add Date"
1,RED,Red,"Having the color of blood",#f00,0.25,2014-10-16
2,GREEN,Green,"Having the color of growing grass",#0f0,10.18,2014-08-25
3,BLUE,Blue,"Having the color of the clear sky",#00f,4.67,2014-09-17

Note that you receive an arrayref. You can specify --with-headers to indicate
that your file has headers, and you will instead receive a hashref:
~$ cat colors.csv | csv2csv --with-headers --transform '$row->{"Color"} = uc($row->{"Color"})'
Id,COLOR,"Color Name",Description,"Hex #",Inventory,"Add Date"
1,RED,Red,"Having the color of blood",#f00,0.25,2014-10-16
2,GREEN,Green,"Having the color of growing grass",#0f0,10.18,2014-08-25
3,BLUE,Blue,"Having the color of the clear sky",#00f,4.67,2014-09-17

You can return a new arrayref or hashref, to pick out particular colums:
~$ cat colors.csv | csv2csv --with-headers --transform 'return {"Id" => $row->{"Id"}, "Color" => $row->{"Color"}}'
Id,Color
1,red
2,green
3,blue

Here is a trick for removing unnamed columns:
~$ cat derp.csv
id,name,age,,,
1,foo,12,,,
2,bar,13,,,
3,baz,14,,,
~$ cat derp.csv | csv2csv --with-headers --transform 'delete $row->{""}'
id,name,age
1,foo,12
2,bar,13
3,baz,14

And this is how you can remove rows that don't have any data (commonly
generated by exporting Excel documents):
~$ cat herp.csv
id,name,age,,,
1,foo,12,,,
2,bar,13,,,
3,baz,14,,,
,,,,,
,,,,,
,,,,,
~$ cat herp.csv | csv2csv --with-headers --transform 'return undef if !grep { $_ } values $row'
id,name,age,,,
1,foo,12,,,
2,bar,13,,,
3,baz,14,,,

Returning undef suppresses the row, and can be used as a kind of CSV grep:
~$ cat colors.csv | csv2csv --with-headers --transform 'return undef if $row->{"Color"} !~ /Color|green/'
Id,Color,"Color Name",Description,"Hex #",Inventory,"Add Date"
2,green,Green,"Having the color of growing grass",#0f0,10.18,2014-08-25

Note that you must account for the header.