Parse Row to Columns

B

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
....
....
 
T

Tad McClellan

Buck Turgidson said:
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
 
B

Ben Morrow

Quoth "Buck Turgidson said:
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
 
U

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top