Modify Data type in CSV

[DrAxeman]

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;

Thanks

[graff]

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;

[DrAxeman]

Perfect! Thank you!

[sk]

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

-SK