0

I have the following data in a very large csv:

"sortorder","name","description"
"1","B.1","Boiler room"
"2","1.1","First office"
"3","1.2","Second office"
etc...

Which I would like to end up in something like this:

{ name => 'B.1', description => 'Boiler room', sortorder => 1 },
{ name => '1.1', description => 'First office', sortorder => 2 },
{ name => '1.2', description => 'Second office', sortorder => 3 },
etc.

How can I accomplish this in something with perl or bash?

1

General sed solution (without fields resorting)

sed '
    1{
        h
        d
    }
    G
    :a
    s/\(^\|,\)\([^=]\+\n\)"\?\([^,]\+\)"\?,\?/\1 \3 => \2/
    ta
    s/^/{/
    s/\n/ },/
    ' file.csv

But if you'd like a fist field become the last - just add

s/\([^,]*\), \([^\n]*\)/\2, \1/

after ta

1

A little ruby one-liner

  ruby -rcsv -ne '
    row = CSV.parse_line($_)
    if $. == 1 then
      keys = row
    else
      h = Hash[keys.zip(row)]
      puts h.to_s + ","
    end
  '

outputs

{"sortorder"=>"1", "name"=>"B.1", "description"=>"Boiler room"},
{"sortorder"=>"2", "name"=>"1.1", "description"=>"First office"},
{"sortorder"=>"3", "name"=>"1.2", "description"=>"Second office"},

or Perl, that generates the complete data structure (array of hashes)

use strict;
use warnings;
use v5.10;

use Text::CSV;
use autodie;
use Data::Dump      qw/ dump /;

my @rows;
open my $fh, "<", shift @ARGV;
my $csv = Text::CSV->new( {binary => 1} );
my $keys = $csv->getline($fh);
while (my $row = $csv->getline($fh) ) {
    my %h;
    @h{ @$keys } = @$row;
    push @rows, \%h;
}

# print it
say dump \@rows;

outputs

[
  { description => "Boiler room", name => "B.1", sortorder => 1 },
  { description => "First office", name => 1.1, sortorder => 2 },
  { description => "Second office", name => 1.2, sortorder => 3 },
]
  • +1 for using Text::CSV - .csv files can have all sorts of little quirks and corner cases that make writing your own a pain. also the required output looked suspiciously like a perl array of hashes anyway. – cas Jul 12 '16 at 11:16
0

Using perl script

#!/usr/bin/perl

open INFILE, "<", "inputfile";
open OUTFILE, ">", "outputfile";

$header_line = <INFILE>;
chop($header_line);
@headers = split /,/, $header_line;
foreach $header_column (@headers) {
    $header_column =~ s/^"(.*)"$/$1/;
}

while(<INFILE>) {
    chop($_); # clear the newline at the end of string
    @fields = split /,/; # split string by ,
    foreach $field (@fields) {
        $field =~ s/^"(.*)"$/$1/;
    }   
    for($i = 0; $i <= $#headers; $i++) {
        $fields[$i] = $headers[$i] . " => " . $fields[$i];
    }
    print OUTFILE "{ " . join(', ', @fields) . "},\n";

}

And with your inputfile

"sortorder","name","description"
"1","B.1","Boiler room"
"2","1.1","First office"
"3","1.2","Second office"

produces the output

{ name => 'name', description => 'description', sortorder => 'sortorder' },
{ name => 'B.1', description => 'Boiler room', sortorder => '1' },
{ name => '1.1', description => 'First office', sortorder => '2' },
{ name => '1.2', description => 'Second office', sortorder => '3' },

The script keeps the order of the columns from the first line.

  • Beware; this will break if any field has a , in it (eg in the description) and will produce wrong results if any field has a " in it (the " will get dropped). It also assumes the fields are in a fixed order and never vary. – Stephen Harris Jul 11 '16 at 16:57
  • Thanks @StephenHarris for notes, commented previous version of the script. – Konstantin Morenko Jul 11 '16 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.