Read a CSV file with uneven commas but fixed number of columns

Question

I want to able to read this CSV file into an array of arrays or hashes for manipulation. How can I go about it?

For example my file contains the following (the first line is the header):

Name,Age,Items,Available
John,29,laptop,mouse,Yes
Jane,28,desktop,keyboard,mouse,yes
Doe,56,tablet,keyboard,trackpad,touchpen,Yes

First column is name, second is Age, third is Items, But items can contain more than one thing separated by commas, and last column is Person availability.

How can I accurately read this?

Answer 1

Well-formed CSV quotes fields that contain a comma as part of the value. If your CSV is well-formed use the Text::CSV module:

use Text::CSV;

my $csv = Text::CSV->new();
while (my $row = $csv->getline(\*DATA)) {
    my $name      = $row->[0];
    my $age       = $row->[1];
    my @items     = split /,/, $row->[2];
    my $available = $row->[3];
    print "$name/$age/@items/$available\n";
}

__DATA__
Name,Age,Items,Available
John,29,"laptop,mouse",Yes
Jane,28,"desktop,keyboard,mouse",yes
Doe,56,"tablet,keyboard,trackpad",touchpen,Yes

Output:

Name/Age/Items/Available
John/29/laptop mouse/Yes
Jane/28/desktop keyboard mouse/yes
Doe/56/tablet keyboard trackpad touchpen/Yes

If your CSV is not well-formed you'll need to implement a custom parse based on knowledge of your data. Assuming that the Items column is the only multi-valued field you can split on a comma and then remove the fields with a known position. Whatever is left is the items.

while (my $line = <DATA>) {
    chomp $line;
    my @record    = split /,/, $line;
    my $name      = shift @record;
    my $age       = shift @record;
    my $available = pop   @record;
    my @items     = @record;

    print "$name/$age/@items/$available\n";
}

__DATA__
Name,Age,Items,Available
John,29,laptop,mouse,Yes
Jane,28,desktop,keyboard,mouse,yes
Doe,56,tablet,keyboard,trackpad,touchpen,Yes

Alternately, you could use array slicing to get the same result:

 my ($name, $age, $available, @items) = @record[0, 1, -1, 2 .. @record - 2];

Answer 2

Since your data is, in reality, a properly-formatted CSV file, you can use the standard tools to read and store it

Here's the data I'm now assuming that you have

Name,Age,Items,Available
John,29,"laptop,mouse",Yes
Jane,28,"desktop,keyboard,mouse",yes
Doe,56,"tablet,keyboard,trackpad,touch pen",Yes

Solution

Like my original answer, this code uses Text::CSV to parse each line of input. But instead of having to reformat it, each row may be pushed directly onto array @data

Also as before, it conforms to the standard of reading from STDIN. But this time I have used Data::Dump to reveal the in-memory data structure that has been built. If you run it on the command line you should use

$ perl unpack_csv.pl text.csv

use strict;
use warnings 'all';

use Text::CSV;

my $csv = Text::CSV->new;

my @data;

while ( <> ) {
    $csv->parse($_);
    my @row = $csv->fields;
    push @data, \@row;
}

use Data::Dump;
dd \@data;

Answer 3

Update

I now realise that the OP's file may well contain properly-formatted CSV data, which makes this answer superfluous

However the question has not been changed to show the real data, so I am leaving this answer here in case the question's subject line and content entices people with a problem that this will solve

---

I recommend that you use an intermediate program to format your CSV file properly. Once you have a standard-format file, the resulting output can then be processed using Perl with Text::CSV, Excel, or anything similar

This program uses Text::CSV to read your input data and write the Items column enclosed in quotes if necessary

It works by using Text::CSV->parse to split each line into fields, and then reserving the first two and final fields for new fields 1, 2 and 4. Whatever is left is joined with a comma , and used for field 3. The four resulting values are passed back to Text::CSV->combine and printed

It conforms to the standard of reading from STDIN and writing to STDOUT, so if you run it on the command line you should use

$ perl reformat_csv.pl text.csv > new_text.csv

use strict;
use warnings 'all';

use Text::CSV;

my $csv = Text::CSV->new;

while ( <> ) {

    $csv->parse($_);
    my @row = $csv->fields;

    my $f1 = shift @row;
    my $f2 = shift @row;
    my $f4 = pop @row;

    my $f3 = join ',', @row;

    $csv->combine($f1, $f2, $f3, $f4);
    print $csv->string, "\n";
}

output

Name,Age,Items,Available
John,29,"laptop,mouse",Yes
Jane,28,"desktop,keyboard,mouse",yes
Doe,56,"tablet,keyboard,trackpad,touchpen",Yes