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

.csv file in to hash of hashes for a Berkley DB (for a hash newbie)

by Doozer (Beadle)
on Jun 12, 2013 at 08:15 UTC ( #1038405=perlquestion: print w/ replies, xml ) Need Help??
Doozer has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have been working with perl for a little while and have now entered in to the world of hashes. I will outline what I want to achieve and then show examples of my data and scripts below.

I currently have a .csv file which holds information for 324 pieces of equipment (Set Top Boxes). The first column holds our unique equipment locator and the following columns hold further information such as serial number, date installed, asset numbers, etc.

Each piece of equipment has its own row which is comma delimited (example below):

Zone1 - Box1,01/06/2011,Amstrad,4F3107,0362940644,540 442 969,R007.061.38.00U,R007.061.38.00U - 03/06/2013,, - R007.061.38.00U,,172.16.121.4 D,00:19:fb:2c:56:2c,03/06/2013,0,03/06/2013

You can see from the example that the first field is our equipment locator ID which I would like to use as the main hash keys. I would then like to have the remaining details as hash values for that individual Set Top Box. This is kind of what I would want:

our %h; %h = ( 'Zone1 - Box1' => { 'Date Active' => 'Value', 'Manufacturer' => 'Value', 'HW Version' => 'Value', 'Serial' => 'Value', 'Card No' => 'Value', 'Current Software' => 'Value', 'OS Version' => 'Value', 'EPG Version' => 'Value', 'Box Type' => 'Value', 'Designation' => 'Value', 'IP Address' => 'Value', 'MAC Address' => 'Value', 'Code Date' => 'Value', }, 'Zone1 - Box2' => { 'Date Active' => 'Value', 'Manufacturer' => 'Value', 'HW Version' => 'Value', 'Serial' => 'Value', 'Card No' => 'Value', 'Current Software' => 'Value', 'OS Version' => 'Value', 'EPG Version' => 'Value', 'Box Type' => 'Value', 'Designation' => 'Value', 'IP Address' => 'Value', 'MAC Address' => 'Value', 'Code Date' => 'Value', }, )

The aim is to be able to store all of the information for each Set Top Box but also make it searchable so I can select a Set Top Box by its unique ID (Zone1 - Box1 for example) and it will show me all of the details.My code is below.

#!/usr/bin/perl use strict; use warnings; use DB_File ; our (%hash) ; unlink "STB_Box_DUT_Details" ; tie %hash, "DB_File", "STB_Box_DUT_Details", O_RDWR|O_CREAT, 0666, $DB +_HASH || die "Cannot open file 'STB_Box_DUT_Details': $!\n"; my $stbdetails = '/path/to/my/file.csv'; my $search = $ARGV[0]; if (defined $search) { chomp $search; } open DUT, "<$stbdetails" or die $!; my @boxes = <DUT>; close DUT; my $count = '0'; foreach my $line (@boxes) { $count++; my @box = split (',', $line); my $location = $box[0]; my $date_active = $box[1]; my $manufacturer = $box[2]; my $hwver = $box[3]; my $serial = $box[4]; my $cardno = $box[5]; my $currentsw = $box[6]; my $os_ver = $box[7]; my $epg_ver = $box[8]; my $boxtype = $box[9]; my $designation = $box[10]; my $boxip = $box[11]; my $boxmac = $box[12]; my $codedate = $box[13]; $hash{$location} = { 'Date Active' => $date_active, 'Manufacturer' => $manufacturer, 'HW Version' => $hwver, 'Serial' => $serial, 'Card No' => $cardno, 'Current Software' => $currentsw, 'OS Version' => $os_ver, 'EPG Version' => $epg_ver, 'Box Type' => $boxtype, 'Designation' => $designation, 'IP Address' => $boxip, 'MAC Address' => $boxmac, 'Code Date' => $codedate, }; } print "STBs added to the hash - $count\n"; foreach my $STB (keys %hash) { if ($STB =~ /$search/) { while ( (my $key1, my $value1) = each %{ $hash{$STB}}) { print "$key1 is $value1\n"; } } }

If I execute this script it reports that all 324 boxes were added to the hash. If I set a box reference for $ARGV[0] ($search) I keep on getting "Can't use string ("HASH(0x7ff9aa090b58)") as a HASH ref while "strict refs" in use" error. I don't think I am adding the new details for each box to the hash correctly. Any help would be very much appreciated :)

Comment on .csv file in to hash of hashes for a Berkley DB (for a hash newbie)
Select or Download Code
Re: .csv file in to hash of hashes for a Berkley DB (for a hash newbie) (DB_File stores strings/bytes)
by Anonymous Monk on Jun 12, 2013 at 08:24 UTC

    DB_File stores strings/bytes, so when you store a reference, its storing the string, it can't store perl datastructures, it won't serialize them

    You might have been thinking of MLDBM or Storable

Re: .csv file in to hash of hashes for a Berkley DB (for a hash newbie)
by Corion (Pope) on Jun 12, 2013 at 08:35 UTC

    When I recently needed a quick persistence solution for an object forest, I used DBM::Deep.

Re: .csv file in to hash of hashes for a Berkley DB (for a hash newbie)
by space_monk (Chaplain) on Jun 12, 2013 at 08:37 UTC
    I was going to comment on the reason for your problem but I've been beaten to it. However I will make one comment that I make for most questions involving CSV files:

    Do not parse CSV files yourself! Use existing libraries such as Text::CSV, Text::CSV_XS, Tie::CSV_File,Tie::Handle::CSV, anything except do it yourself methods.They will automatically handle quoted fields and other CSV gotchas without you noticing most of the time. Reading CSV files line by line and using split is a poor way of doing things, only to be used in the most basic of circumstances. If someone has done all the difficult stuff for you, take advantage of their hard work.

    If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)
      Thanks for all the quick responses. I have had a look at DBM::Deep and it looks like it might be what I need. I will also get some practice in with Text::CSV etc. Funny how commenting out the line which ties the hash to a DB file makes the script work (with a couple of minor tweaks). Its easy to overlook simple checks when you have been working on the same problem all day :p

      I don't think I am adding the new details for each box to the hash correctly.

      Apart from the valuable remarks of my Brothers who came to answer this before,
      your code to add the details is working well.

      So keep your efforts on fixing the data serializing aproach and when that's done, have a look at the CSV parsers.

      Cheers, Sören

      (hooked on the Perl Programming language)

Re: .csv file in to hash of hashes for a Berkley DB (for a hash newbie)
by kcott (Abbot) on Jun 12, 2013 at 09:20 UTC

    G'day Doozer,

    "I don't think I am adding the new details for each box to the hash correctly."

    That's not your problem (although there's probably a better way to achieve this). See DB_File - "How do I store complex data structures with DB_File?":

    Although DB_File cannot do this directly, there is a module which can layer transparently over DB_File to accomplish this feat.

    Check out the MLDBM module, available on CPAN in the directory modules/by-module/MLDBM.

    The value you're associating with each $hash{$location} is the stringified representation of the hashref { 'Date Active' => $date_active, ... } (i.e. HASH(0xhex-digits)) — this is the string you can't use as a hash reference.

    Regarding my comment "there's probably a better way to achieve this", consider using a hash slice (see perldata - Slices) instead of assigning each element of @box to a separate variable which is subsequently used just once as a value. Here's an example using a cutdown version of your code and data:

    $ perl -Mstrict -Mwarnings -E ' my @STB_keys = ("Date Active", "Manufacturer"); my %hash; my $line = "Zone1 - Box1,01/06/2011,Amstrad"; my @box = split /,/ => $line; my $location = shift @box; @{$hash{$location}}{@STB_keys} = @box; say $hash{"Zone1 - Box1"}{"Date Active"}; say $hash{"Zone1 - Box1"}{"Manufacturer"}; ' 01/06/2011 Amstrad

    Also, splitting a CSV file on a comma will break if any value contains a comma: Text::CSV is a better choice.

    -- Ken

Re: .csv file in to hash of hashes for a Berkley DB (for a hash newbie)
by Tux (Monsignor) on Jun 12, 2013 at 10:05 UTC

    And reading the data can be so easy with Text::CSV_XS. In my perception even easier than with your snippet. Mind that you have two trailing columns that you do not use.

    use Text::CSV_XS; my $stbdetails = "stbdetails.csv"; open my $fh, "<", $stbdetails or die "$stbdetails: $!"; my $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1 }); $csv->bind_columns (\my ( $location, $date_active, $manufacturer, $hwver, $seria +l, $cardno, $currentsw, $os_ver, $epg_ver, $boxty +pe, $designation, $boxip, $boxmac, $codedate, $_flag, $_date, )); my %hash; while ($csv->getline ($cfh)) { $hash{$location} = { "Date Active" => $date_active, "Manufacturer" => $manufacturer, "HW Version" => $hwver, "Serial" => $serial, "Card No" => $cardno, "Current Software" => $currentsw, "OS Version" => $os_ver, "EPG Version" => $epg_ver, "Box Type" => $boxtype, "Designation" => $designation, "IP Address" => $boxip, "MAC Address" => $boxmac, "Code Date" => $codedate, }; }

    Enjoy, Have FUN! H.Merijn
      I have ended up going with MLDBM.

      DBM::Deep was good but MLDBM did exactly what I wanted. I only needed to read this .csv file into a database once. Now using MLDBM I can open the database and search for any field of any Set Top Box with ease.

      Thanks again for all the help on this.

Alternative answer - use the CSV file as the database
by space_monk (Chaplain) on Jun 12, 2013 at 20:27 UTC

    Not sure why you are using a Berkeley DB, but as a alternative you could use the CSV file itself as the database. Take a look at DBD::CSV.

    If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)
      I wanted to use DBD::CSV on my Mac, but it won't install - several tests fail. Not sure how to work around that issue.

        Currently, some test failures can be ignored. Keep an open eye, we plan to make a new DBD::CSV release soon. There have been a lot of changes in the underlying modules that needed to be accounted for.


        Enjoy, Have FUN! H.Merijn

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1038405]
Approved by Happy-the-monk
Front-paged by perlfan
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (11)
As of 2014-12-18 09:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (48 votes), past polls