Parse Row to Columns

[Buck Turgidson]

I have a very large csv file from a spreadsheet. The first 2 columns are a
description, and the next 52 columns are a value for a week of the year
(using only 3 weeks in this example)

Is there a way in Perl to transform these into a format that is loadable
into a relational database such as Oracle?

For example, I'd like to change the following into the latter format. The
first column would be a derived week number. That first column is something
I could live without if I had to.

Any help with a simple perl would be appreciated.



washer,sunday,33,44,55
dryer,monday,667,333,44
refrig,monday,555,876,99
....
....



1 washer sunday 33
2 washer sunday 44
3 washer sunday 55
1 dryer monday 667
2 dryer monday 333
3 dryer monday 444
1 refrig monday 555
2 refrig monday 876
3 refrig monday 99
....
....

 

[Tad McClellan]

Is there a way in Perl to transform these into a format that is loadable
into a relational database such as Oracle?

For example, I'd like to change the following into the latter format.

[ snip input, repeated in code below ]

[ desired output: ]

1 washer sunday 33
2 washer sunday 44
3 washer sunday 55
1 dryer monday 667
2 dryer monday 333
3 dryer monday 444
1 refrig monday 555
2 refrig monday 876
3 refrig monday 99

-----------------------------------------
#!/usr/bin/perl
use warnings;
use strict;

while ( <DATA> ) {
chomp;
my( $item, $day, @nums ) = split /,/;

print $_ + 1, " $item $day $nums[$_]\n" for 0 .. $#nums;
}

__DATA__
washer,sunday,33,44,55
dryer,monday,667,333,44
refrig,monday,555,876,99

 

[Ben Morrow]

I have a very large csv file from a spreadsheet. The first 2 columns are a
description, and the next 52 columns are a value for a week of the year
(using only 3 weeks in this example)

Is there a way in Perl to transform these into a format that is loadable
into a relational database such as Oracle?

For example, I'd like to change the following into the latter format. The
first column would be a derived week number. That first column is something
I could live without if I had to.

For each line of the file

Split off the first two columns and keep them.

For each of the remaining columns

Calculate the week number, perhaps using one of the date/time
modules from CPAN, of which there are many.

Write out a line with the calculated number, the two columns
you've saved, and the column you're working with.

Now translate this into Perl. If you get stuck, post what you've got
here and we'll help you.

Ben

 

[Uri Guttman]

JG> use strict;
JG> while(<DATA>){
JG> chomp;
JG> my($appliance,$day,@values) = split(/,/);
JG> for my $i ( 0 .. $#values ) {

i try to not use index variables when not needed or at least avoid
indexing itself. so loop over the values and bump $i instead

JG> printf "%d %s %s %d\n", ($i+1), $appliance,
JG> $day, $values[$i];


$my $i ;
for my $val ( @values ) {
printf "%d %s %s %d\n", ++$i, $appliance, $day, $val ;
}

and choose different format specs if you want to keep the fields aligned
or use a format module.

uri