How to write complete hash into CSV?

CSharma has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I'm working on a script to convert json file to csv format; I'm using Cpanel::JSON::XS and have read the file into hash; now trying to write that into csv; here is the piece of that code. Actually I'm stuck in inner hashes, making me confused. Could please someone help me out here?

foreach my $line (@{$offers->{'offers'}{'offer'}})      {
        foreach my $row (sort keys %$line)      {
                print $line->{$row} . "\t";
        }
print "\n";
}
[download]

Here's the hash which I've read from json file.

$VAR1 = {
          'offers' => {
                        'offer' => [
                                     {
                                       'originalPrice' => {},
                                       'categoryId' => '25000800',
                                       'sku' => 'PBSRW21',
                                       'shipAmount' => {
                                                         'integral' =>
+ '100000100',
                                                         'value' => '$
+1000001.00'
                                                       },
                                       'merchantProductId' => '0081383
+91',
                                       'revType' => '2',
                                       'url' => {
                                                  'value' => 'http://r
+d.bizrate.com/rd?t=https%3A%2F%2Fwww.acitydiscount.com%2FJohn-Boos-21
+-Wall-Mounted-Solid-Sorting-Shelf-18-Gauge-Stainless-PB-SRW-21.0.1383
+91.1.1.htm%3Futm_source%3Dbizrate%26utm_medium%3Dcse%26utm_campaign%3
+Dcse_biz%26ppcid%3D8%26link%3D13170103&mid=91353&cat_id=25000800&atom
+=10704&prod_id=&oid=4608507152&pos=1&b_id=18&bid_type=0&bamt=a5fedf14
+fd8fec0d&cobrand=1&ppr=85e317571cc20ee0&rf=af1&af_assettype_id=12&af_
+creative_id=2932&af_id=[PUBLISHER_ID]&af_placement_id=1&dv=5786a95dec
+b116ac2d79e70c5ddae351'
                                                },
                                       'id' => '4608507152',
                                       'atomId' => '10704',
                                       'images' => {
                                                     'image' => [
                                                                  {
                                                                    'y
+size' => '400',
                                                                    'x
+size' => '400',
                                                                    'v
+alue' => 'http://d1-pub.bizrate.com/resize?sq=400&uid=4608507152'
                                                                  }
                                                                ]
                                                   },
                                       'rawUrl' => 'https://www.acityd
+iscount.com/John-Boos-21-Wall-Mounted-Solid-Sorting-Shelf-18-Gauge-St
+ainless-PB-SRW-21.0.138391.1.1.htm?utm_source=bizrate&utm_medium=cse&
+utm_campaign=cse_biz&ppcid=8&link=13170103',
                                       'shipType' => 'UNKNOWN',
                                       'stock' => 'IN',
                                       'description' => 'John Boos - P
+B-SRW-21 Weight 5 lbs. Width 21 in. Depth 18 in. Height 16 in. Rating
+s: NSF. John Boos 21 Wall Mounted Solid Sorting Shelf 18 Gauge Stainl
+ess - in Shelving, Wall at ACityDiscount',
                                       'merchantId' => '91353',
                                       'brandId' => '271910',
                                       'manufacturer' => 'John Boos',
                                       'type' => 'OFFER',
                                       'title' => 'John Boos 21 Wall M
+ounted Solid Sorting Shelf 18 Gauge Stainless - PB-SRW-21',
                                       'price' => {
                                                    'integral' => '956
+3',
                                                    'value' => '$95.63
+'
                                                  }
                                     },
                                     {
                                       'originalPrice' => {},
                                       'categoryId' => '25000800',
                                       'sku' => 'DT6R23X',
                                       'shipAmount' => {
                                                         'integral' =>
+ '100000100',
                                                         'value' => '$
+1000001.00'
                                                       },
                                       'merchantProductId' => '0080147
+82',
                                       'revType' => '2',
                                       'url' => {
                                                  'value' => 'http://r
+d.bizrate.com/rd?t=https%3A%2F%2Fwww.acitydiscount.com%2FAdvance-Tabc
+o-62-Wall-Mounted-Tubular-Sorting-Shelf-Stainless-Knock-Down-DT-6R-23
+-X.0.14782.1.1.htm%3Futm_source%3Dbizrate%26utm_medium%3Dcse%26utm_ca
+mpaign%3Dcse_biz%26ppcid%3D8%26link%3D13170103&mid=91353&cat_id=25000
+800&atom=10704&prod_id=&oid=5266795020&pos=1&b_id=18&bid_type=0&bamt=
+a5fedf14fd8fec0d&cobrand=1&ppr=d3a45a91cc13e426&rf=af1&af_assettype_i
+d=12&af_creative_id=2932&af_id=[PUBLISHER_ID]&af_placement_id=1&dv=57
+86a95decb116ac2d79e70c5ddae351'
                                                },
                                       'id' => '5266795020',
                                       'atomId' => '10704',
                                       'images' => {
                                                     'image' => [
                                                                  {
                                                                    'y
+size' => '400',
                                                                    'x
+size' => '400',
                                                                    'v
+alue' => 'http://d1-pub.bizrate.com/resize?sq=400&uid=5266795020'
                                                                  }
                                                                ]
                                                   },
                                       'rawUrl' => 'https://www.acityd
+iscount.com/Advance-Tabco-62-Wall-Mounted-Tubular-Sorting-Shelf-Stain
+less-Knock-Down-DT-6R-23-X.0.14782.1.1.htm?utm_source=bizrate&utm_med
+ium=cse&utm_campaign=cse_biz&ppcid=8&link=13170103',
                                       'shipType' => 'UNKNOWN',
                                       'stock' => 'IN',
                                       'description' => 'Advance Tabco
+ - DT-6R-23-X,DT-6R-23 Weight 30 lbs. Width 18 in. Depth 62 in. Heigh
+t 11.25 in. Ratings: NSF. Advance Tabco 62 Wall Mounted Tubular Sorti
+ng Shelf Stainless Knock Down - in Shelving, Wall at ACityDiscount',
                                       'merchantId' => '91353',
                                       'brandId' => '41378',
                                       'manufacturer' => 'Advance Tabc
+o',
                                       'type' => 'OFFER',
                                       'title' => 'Advance Tabco 62 Wa
+ll Mounted Tubular Sorting Shelf Stainless Knock Down - DT-6R-23-X',
                                       'price' => {
                                                    'integral' => '380
+19',
                                                    'value' => '$380.1
+9'
                                                  }
                                     }
                                   ]
                      }
        };
[download]

Comment on How to write complete hash into CSV? Select or Download Code

Replies are listed 'Best First'.
Re: How to write complete hash into CSV? by Eily (Monsignor) on Jun 07, 2016 at 08:06 UTC
Hello CSharma. CSV data is flat, so there is no straightforward way to represent nested structures. You can still come up with a representation for your structure (eg: database-like representation with several tables and indexes from one table to another), but you'll have to define it first so that we can understand what you're doing. To do that, please write a simple example, a structure with dummy (but short) data and what you except to find in your CSV. I haven't tried to understand your current structure because of how long it is. The code you have provided does not check anywhere if all attributes defined in one element are the same in the following, this may be the case, but you have to be sure about it to avoid some values being shifted left or right. If the members of the elements you need to represent are fixed, or you know the list of attributes beforehand, you can iterate over a fixed list of keys, in the other case another strategy might be required (like writing each new attribute at the end of table).	[reply]
Re: How to write complete hash into CSV? by GotToBTru (Prior) on Jun 07, 2016 at 12:01 UTC
I'm going to guess you want to pick out only certain elements of the JSON to form each row of the csv file. I suggest you study up on hashes and references here and here. If you want really useful help from us, provide details on what the csv should look like when you're done. But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)	[reply]
Re: How to write complete hash into CSV? by Marshall (Canon) on Jun 07, 2016 at 12:38 UTC
The first problem appears to be how to access this data structure? Below I used a simple technique to reconstruct your original hash and show some ways to access this stuff. I used "readmore" tags because your example data is so long. An important point is that $hash{offers}{offer} is an array of anon hashes (references to hash). I show how to print the keys of those hashes. Past the "how to access this data" question, the idea of a CSV file brings up a raft of other issues. It would be helpful if you back up a bit and explain what your overall objective is? The data that you have doesn't normally fit well with a CSV representation. I for one am wondering what you intend to do with that CSV? There are other possibilities including SQLite that may be more suited to what your objective is? Read more... (13 kB)	[reply] [d/l]
Re: How to write complete hash into CSV? by anonymized user 468275 (Curate) on Jun 08, 2016 at 13:40 UTC
One could argue that it is already in CSV format for a single very big cell ;) So you could clarify your output as for example 'CSV: one row per hash key or array element, one indentation per substructure'. In which case a simple approach would be, for my own example definition: (Updated to process arrays nested in there) my $csv = ''; traverse(0,$VAR1,\$csv,';'); sub traverse { my ($plevel, $node, $csvref, $dlm) = @_; if (ref($node) eq 'HASH')) { while (my ($k, $v) = each %$node) { $$csvref .= ($plevel x $dlm) . $k; if (ref($v)') { $$csvref .= "\n"; traverse($plevel+1, $v, $csvref, $dlm); } else { $$csvref .= $dlm . $v . "\n"; } } } else { # array. scalars are filtered before the recurse for my $elm (@$node) { if (ref($elm)) { traverse($plevel+1, $elm, $csvref, $dlm); } else { $$csvref .= ($plevel x $dlm) . $elm . "\n"; } } } } [download] One world, one people	[reply] [d/l]
Re: How to write complete hash into CSV? by mhearse (Chaplain) on Jun 08, 2016 at 21:00 UTC
You could cheat and use one of several JSON to CSV sites: http://konklone.io/json/	[reply]