Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Modify Data type in CSV

by DrAxeman (Beadle)
on Aug 06, 2005 at 23:05 UTC ( #481566=perlquestion: print w/replies, xml ) Need Help??
DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

I'd first like to say, this is a great site. Everybody here has been patient and very informative! Thank you to everybody who has helped me over the last 2 days.

I'm still working on a script to process csv files. Before I can process the data, currently I need to "pre-process" the data using OpenOffice. Some of the data in the CSV file is in scientific notation (9.9871239340160043e-005). I found a perl module (Data Types) will allow the modification of Data Type.
my $decimal = '1.2foo'; $decimal = to_decimal($decimal) unless is_decimal($decimal);
This appears to be what I need. I just don't know how to write a script to convert the data. My CSV files have headers in the first row, so I need to ignore that row. Also, my script is getting complex, and my perl skills are limited, so I am posting my script. If it's easier to create a seperate script to do this before my current script, feel free to tell me. I'm learning as I'm going.
#!/usr/bin/perl use strict; use warnings; use DBI; #use Text::CSV; # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'}; my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0"); $sth->execute; my @origcols = @{$sth->{NAME}}; my @cols; # = @origcols; shift @origcols; foreach ( @origcols ) { #$_ =~ s/\.//g ; push ( @cols , $_ ); # unless /Bandwidth.*|MSTCPLoop.*/ ; }; my $avgSQL = 'SELECT ' . join(', ', map { "avg($_) \n" } @cols ) . ' FROM results'; my @avgs = $dbh->selectrow_array($avgSQL); my %avgsHash; @avgsHash{ @cols } = @avgs; # uses a hash slice to populate %avgs +Hash print "Col,Avg\n"; printf("%s,%f\n", $_, $avgsHash{$_}) for @cols;

Replies are listed 'Best First'.
Re: Modify Data type in CSV
by graff (Chancellor) on Aug 06, 2005 at 23:27 UTC
    I'm not entirely sure what you're asking for here. Your code looks okay, but I really wonder about the parts that are commented out -- the net effect is that I'm not sure what you're trying to do.

    If your question involves reformatting the data that comes out of the second query (for averages), I'm not sure why you need to do this; if a scalar variable is assigned a "string" value like "9.9871239340160043e-005", and then use that variable in an arithmetic operation, it just works as a number, and you don't need to do any explicit conversion yourself.

    But in general, if you're wondering how to do things to values as you move them through a hash slice assignment, that is what "map" is for:

    @avgsHash{ @cols } = map { some_conversion($_) } @avgs;
      Perfect! Thank you!
Re: Modify Data type in CSV
by sk (Curate) on Aug 06, 2005 at 23:33 UTC
    DrAxeman, In your previous post: Pre-process csv files before using, I replied with an inplace edit code. In that I use something called $. which keeps track of the current line number processed. So if you really want to do conversion of some sort then you can check for  $. != 1 and then apply the conversion for the rest of the code.

    update: As graff pointed you shouldn't need the conversion function. Take this code for example

    perl -e'$val = 9e-05; print "value = ", $val*2, $/;' __END__ value = 0.00018

    If you find the command line version of the program confusing just focus on the pieces after -e'. Everything is a regular perl code. I am just assingning a value using scientific notation and use it in regular airthmetic and get expexted results. Unless you are doing something fancy with your conversion i wouldn't go for a module


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://481566]
Approved by graff
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2017-06-26 07:32 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (573 votes). Check out past polls.