<

Using Perl to preview in a Web Page and convert a JSON document into an Excel workbook

Published on
11,694 Points
5,194 Views
Last Modified:
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel.

I primarily created the Web based tool to eliminate the  "Can you pull...." requests appearing in my inbox, and as Mac fanboys there weren't any toys around that would allow them to easily get at the data. When I say easily, the requirement was for a Manager proof point and click GUI, that could filter and flatten JSON / BSON documents with, multiple levels of nesting, into an Excel workbook. The nesting even rulled out the likes of iReports.

Anyway a day or two of hacking, while sat monitoring a training session (explains the quality of the code), and I had a rough little toy (Perl CGI + JS + Dojo Grid) that offered a fairly primitive query builder, that would allow them to: Browse, Filter, aggregate and Export data from a  MongoDB.

Had a quiet life for a bit, till a requirement to do something similar for exported JSON files, from a couple of the Social Media sites cropped up. Had a quick think, simply dropped 80% of the code in an evening, and came up with a little toy that would: Upload, Preview OR Convert a JSON document into an Excel Workbook. When I say JSON document, essentially a text file containing an anonymous JSON array of documents e.g.
[
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"},
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"},
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}
]

Open in new window

As similar requests have cropped up on a number of the Q&A sites I though I might as well dig out the minimal version of the toy, cut it down some more, and share.

So assuming you have a PC / Mac / Server with a CGI enabled web-server, if not there are numerous tutorials out there that explaing the basics e.g. 1 2. You'll want to start off by installing the necessary Perl libraries, enter, ideally as root (possibly via sudo):
cpan YAML Data::Dumper Spreadsheet::WriteExcel Excel::Writer::XLSX Tie::IxHash Encode Scalar::Util JSON File::Basename IO::File CGI

Open in new window

Note: If you hit problems with the install first check your firewall allows you to reach ftp.perl.org. You will spend an age thinking about then probably just going for the default options, and / or installing any missing binaries (If playing on a Mac you'll need Xcode from the App store and then the Command Line tools installed, from Xcode's preferences panel).

Once you have the required libraries you can have a stab at getting the simple Upload and preview / convert script running.

I've split the proof of concept logic below into 3:
The CGI Script - A simple Upload and Export form, which offers a very basic Dogo Grid based preview option, along with an optional filter to limit the numer of rows / documents previewed, and a very basic output column filter (Trivial to replace with a dual listbox set-up)
A UI library for the page - For this example just some empty common Header, Footer text, Style Sheet constants held externally
A JSON function library - To do the main work of parsing and converting the JSON, to the chosen output format

If your new to Perl please note the .cgi script needs to be placed in your web-servers cgi-bin directory, or in a directory with Exec permissions, and have a execute attribute set on it. The two libraries will either need to reside in the same directory as the CGI script, or in a directory that is in your @INC path, see bottom of the output from a: env -i perl -V

So first the CGI script:  json2Excel.cgi
#!/usr/bin/perl -w
# Purpose: Attempt to convert a file containing an anonymous JSON array in to an Excel  workbook, with a Dojo data Grid preview
#
#
# To-Do:  Chunk file reading / processing,
#         Add a proper UI / Fix CSS
#         Add some proper exception logic
#         Tidy Code

use strict;
use JSON;
use CGI;
use CGI::Carp qw ( fatalsToBrowser ); 
use ARJsonLib;
use ARUiLib;

sub process_form($);
sub build_form($$$$);

#File upload limit 5MB
$CGI::POST_MAX = 1024 * 5000;

my $cgi                          = new CGI;
   $cgi->charset('UTF-8');
my $sStatusMsg                  = "";
my $sResults                    = "";
my $sFormFields                 = "";

if ($cgi->param) {
    ($sStatusMsg, $sResults, $sFormFields)=process_form($cgi);
}
# Re-build the page
build_form($cgi, $sStatusMsg, $sResults, $sFormFields);
exit;

# -------------------------------------------------
sub process_form($) {
  my ($cgi) = @_;
  my ($sMsg, $sResults, $sFilename, @aOutFields, $sPageFields, $sOutFormat);
  my ($sFields)                      = "";
  my ($iPreviewRows)                 = 0;
  my $sUploadDir                     = "/tmp";     # Change to suit !!!!!!
  my $safe_filename_characters       = "a-zA-Z0-9_.-";

  if ($cgi->param('fields')){
        ($sFields)                      = ($cgi->param('fields')  =~ /^([_a-z][a-z-_,.0-9~]+)$/i);
        @aOutFields                     = split("~~", $sFields);
  }
  if ($cgi->param('previewRows')){
        ($iPreviewRows)                 = ($cgi->param('previewRows')  =~ /^([0-9]+)$/)  if ( ($cgi->param('previewRows')  =~ /^[0-9]+$/) );
  }
  if ($cgi->param('file')){
        $sFilename                      = $cgi->param('file');
  }
  ($sOutFormat) = ($cgi->param('outFormat')       =~ /^([a-z]{3,4})$/i)          if($cgi->param('outFormat'));
  $sOutFormat = "xls"                                                                       if (! $sOutFormat);
      
  if ( !$sFilename ) { return("Error: Filename missing",$sResults); }

  my ( $sName, $sPath, $sExtension ) = fileparse ( $sFilename, '\..*' ); 
  $sFilename                         = $sName . $sExtension;
  $sFilename                         =~ tr/ /_/;
  $sFilename                         =~ s/[^$safe_filename_characters]//g;
  if ( $sFilename                    =~ /^([$safe_filename_characters]+)$/ ) { 
    $sFilename = $1;
  } else { 
   return ("Error: Filename contains invalid characters",$sResults);
  } 
  if ( ! $sExtension =~ /^\.(json|txt|text)$/i ) {
     return("Error: Invalid file extention only: .txt, .text and .json are supported - $sExtension",$sResults);
  } 

  my $oUploadFilehandle              = $cgi->upload("file");
  open ( UPLOADFILE, ">$sUploadDir/$sFilename" ) or die "$!";
  binmode UPLOADFILE;
  while ( <$oUploadFilehandle> ) { 
        print UPLOADFILE;
  } 
  close UPLOADFILE;
  my $sJsonFIle="$sUploadDir/$sFilename";

  # from file content
  local $/;
  open( my $fh, '<', "$sUploadDir/$sFilename" );
  my $json_text   = <$fh>;
  my $roPasedJson = JSON->new->utf8(1)->decode($json_text);

  #Get fields/keys in collection
  my $raFields = findKeysInJsonColl($roPasedJson);
  my @aFields  = @$raFields;
  @aOutFields = @aFields  if ( @aOutFields eq 0 );

  if ($sFields) {
        $sPageFields = " VALUE=\"$sFields\"";
  }
  else {
        $sPageFields = " VALUE=\"".join('~~',@aFields)."\"";
  }

  if ( $cgi->param('preview') =~ /true/ ){
        $sResults .= convertToDojoGrid("", $roPasedJson, \@aOutFields, $iPreviewRows);
  } else {
        $sFilename                        = "$sName.$sOutFormat";
        my $rows                          = convertToExcel("$sUploadDir/$sFilename", "", $roPasedJson, \@aOutFields, $sOutFormat);
        $sMsg                             = "Rows/Docs written to Excel: $rows";
        
        open(DLFILE, "<$sUploadDir/$sFilename") || Error('open', 'file');

        print $cgi->header(
                -type            => 'application/x-download',
                -Content_length  => -s "$sUploadDir/$sFilename",
                -attachment      => $sFilename,
        );

        binmode DLFILE;
        print while <DLFILE>;
        close (DLFILE) || Error ('close', 'file');
        unlink("$sUploadDir/$sFilename");        
  }

  unlink("$sJsonFIle");

  return ($sMsg, $sResults, $sPageFields);
}

# --------------------------------------------
sub build_form($$$$){

  my ($cgi, $sStatusMsg, $sResults, $sShowFields) = @_;
  my ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector) = get_page_head_and_tail('json2Excel.cgi');
  my $sPage = "";

  my ($sPreview, $sFilename);
  my $iPreviewRows=0;
  if($cgi->param('preview')){
        $sPreview = "checked=checked" if($cgi->param('preview') =~ /true/);
  }
  if($cgi->param('previewRows')){
        $iPreviewRows = $iPreviewRows + ($cgi->param('previewRows') =~ /^([0-9]+)$/)[0];
  }
  if ($cgi->param('file')){
        $sFilename = "<script type=\"text/javascript\" defer>document.getElementById('file').setAttribute('value','".$cgi->param('file')."');</script>";
  }

  #$sStatusMsg =~ s/[\n\r\"]/\\n/gm;   #Escape any CR's / LN's that will upset the JavaScript;
       
  $sPage.=<<END_HTML;
  <div class="wrapper">
$sHeader
$sMenu
    <div class="clearer"></div>
      <div class="content" style="width: auto;">
        <div class="spacer"></div>
          <h1>Convert a file containing an anonymous JSON array into a Excel (xls) workbook - example 1</h1>
        <div class="spacer"></div>
<FORM ENCTYPE="multipart/form-data" METHOD="POST">
<p> Please select a file to upload (Expects files of type: .text, .txt, .js, or .json extensions will work, depending on OS / mime types defined locally): <br />
<INPUT TYPE="FILE" NAME="file" ID="file" ACCEPT='text/plain,text/javascript,application/json,application/javascript' >
</p>
<p> <strong>Optional:</strong>&nbsp;Please specify the <strong>fields</strong> to appear in the output (As a list and '~~' separated e.g. name~~age~~address.0.street): <br /> 
<INPUT TYPE="text" SIZE=80 NAME="fields" $sShowFields> 
</p>
<p> <input type="checkbox" name="preview" value="true" $sPreview/> Preview in Browser - max rows (0 = all): <INPUT TYPE="text" NAME="previewRows" value="$iPreviewRows" size=6><br />
</p>
<p>Output Format: <select name="outFormat">
<option selected="" value="xls">xls</option>
<option value="xlsx">xlsx</option>
</select>
</p>
<p> <INPUT TYPE="submit"> </p>
</FORM>
 $sFilename
END_HTML
  $sPage.="<script language=\"JavaScript\">\n<!--\nalert(\"$sStatusMsg\");\n//-->\n</script>\n" if($sStatusMsg);
  $sPage.=$sResults;
  $sPage.=<<END_HTML;
<hr />
<h2>Info</h2>
<p>This toy expects a file containing a set of JSON documents in an anonymous ARRAY, rather than a single document (row).<br /> So please ensure you wrap your JSON document(s) in square brackets. If parsing multiple documents simply comma separate them within the square brackets e.g.</p>
<p><strong style="color: red;">[</strong><br />
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<strong style="color: red;">,</strong><br />
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<strong style="color: red;">,</strong><br />
{"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<br />
<strong style="color: red;">]</strong></p>
<p><strong>Note:</strong>&nbsp; Haven't paid much attention to detecting / handling non Latin encodings</p>
      </div>
    </div>
    <div class="clearer"></div>
$sFooter
  </div>
END_HTML

  print $cgi->header(),
        $cgi->start_html(
                        -title=>'Convert a file containing an anonymous JSON array into a Excel (xls) workbook - example 1',
                        -style=>[
                                        {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dijit/themes/claro/claro.css'},
                                        {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dojox/grid/resources/Grid.css'},
                                        {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dojox/grid/resources/claroGrid.css'}
                                ],
                        -class=>'claro'
                        ),
        $sPage,
        $cgi->end_html;

} 
# --------------------------------------------

Open in new window

Hopefully fairly self explanatory, but note:
 

1.

The 5MB upload limit ($CGI::POST_MAX) at the top. Feel free to up or remove, as an XLS sheet can handle 65,536 documents (rows) while an XLSX format sheet can handle 2 million. The above limit is primarily a hangover from the script being stuck on a Development server, that I'd prefer not be used for bulk data processing.

2.

The $sUploadDir variable specified the directory where the JSON files are uploaded to, and the resulting workbooks are written to, per the comment alter to suit.

3.

I've externalised the Dojo includes (http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/), if you want to play it may be an idea to grab your own copy, expand, and host locally.

The UI or "make it pretty" Library, as mentioned above is essentially empty for this example, so simply cut and paste and create a dummy style sheet for now e.g.
touch /path/to/web-server-root/style/ARstyle.css

The lib: ARUiLib
#
# Purpose: Assorted UI functions
#
# ---------------------------------------------------------------
# $Id:
# ---------------------------------------------------------------
# $Log:
#
# To-Do:  
#

use strict;
use File::Basename;

my $arUiLib = 1;

# --------------------------------------------
sub get_page_head_and_tail($){

  my ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector);

$sStyle=<<EOS;
<link href="/style/ARstyle.css" type="text/css" rel="stylesheet"/>
EOS

 $sMenu=<<EOS;
    <div class="menu">
      <div class="clearer"></div>
    </div>
EOS

 $sHeader=<<EOS;
    <div class="header-wrapper">
      <div class="header-content" style="width: 98%;">
        <div class="pageName">JSON to Excel</div>
        <div class="clearer"></div>
      </div>
    </div>
EOS

$sFooter=<<EOS;
    <div class="footer-wrapper">
        <div class="footer-content">
                <div>JSON to Excel - Style to your own taste</div>
        </div>
    </div>
EOS

  return ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector);
}

Open in new window

Next the JSON library:  ARJsonLib.pm
#
# Purpose: Assorted JSON / BSON functions
#
# ---------------------------------------------------------------
# $Id:
# ---------------------------------------------------------------
# $Log:
#
# To-Do:  Chunk file reading / processing,
#         Add some proper exception logic
#         Tidy Code

use strict;
use Spreadsheet::WriteExcel;
use Excel::Writer::XLSX;
use Tie::IxHash;
use Scalar::Util 'reftype';
use JSON;
use IO::File;

my $arjsonLib = 1;

sub getKeysInHash($$);
sub getKeysInCollection($$);
sub getCollKeys($$);
sub getValue;

# --------------------------------------------
sub getKeysInHash($$){
  my ($rJsonHash, $sPrefix) = @_;
  my (@aKeys, $refType, $raKeys);

  while( my ($k, $v) = each %$rJsonHash ) {
                $refType = ref($rJsonHash->{$k});
                if ( $refType && $refType eq 'DateTime' ) {
                        push (@aKeys, "$sPrefix$k");
                }
                elsif ( $refType && $refType eq 'HASH' ) {
                        $raKeys = getKeysInHash($v, "$sPrefix$k.");     
                        push (@aKeys, @$raKeys);
                }
                elsif ( $refType && $refType eq 'ARRAY' ) {
                        $raKeys = getKeysInCollection($v, "$sPrefix$k.");
                        push (@aKeys, @$raKeys);
                }
                else {
                        push (@aKeys, "$sPrefix$k")     if (isNotARef($rJsonHash->{$k}) );
                }
  } # End key / value while
  return \@aKeys;
}

# --------------------------------------------
sub getKeysInCollection($$){
  my ($rJsonCollection, $sPrefix) = @_;
  my (@aKeys, $raKeys, $rhDoc, $refType, $iCnt);


  if ($rJsonCollection) {
        $refType = reftype($rJsonCollection);
        if ( $refType && $refType eq 'HASH' ) {
                $raKeys = getKeysInHash($rJsonCollection, $sPrefix); 
                push (@aKeys, @$raKeys); 
        } # End HASH REF logic
        elsif ( $refType && $refType eq 'ARRAY' ) {
                $iCnt=0;
                foreach $rhDoc ( @{$rJsonCollection} ) {
                        if ($sPrefix) { 
                                $raKeys = getKeysInCollection($rhDoc, "$sPrefix$iCnt.");
                                if ( (@$raKeys) ) {
                                        push (@aKeys, @$raKeys); 
                                } else {
                                        push (@aKeys, "$sPrefix$iCnt");
                                }
                        }
                        else {
                                $raKeys = getKeysInCollection($rhDoc, "");
                                push (@aKeys, @$raKeys); 
                        }
                $iCnt++;
                } #End Foreach item (ARRAY object)
        } # End ARRAY REF logic
  }
  return \@aKeys;
}
# --------------------------------------------
sub findKeysInJsonColl($){
  my ($rJsonCollection) = @_;
  my (@aKeys, $raFields,  $sKey, %hKeys);

  #Get ALL keys in collection
  $raFields = getKeysInCollection($rJsonCollection, "");

  #Eliminate duplicates
  foreach $sKey ( @{$raFields} ) {
        $hKeys{$sKey} = 1;
  }
  while( my ($k, $v) = each %hKeys ) {
        push(@aKeys, $k);
  }
  @aKeys = sort @aKeys; 
  return \@aKeys;
}
# --------------------------------------------
sub convertToExcel($$$$$){
  my ($sFilename, $rhMongoCursor, $oJsonData, $raOutFields, $sFileFormat)=@_;
  my ($iRow,$iCol,$rhDoc,$oTitleFormat,$sKey,$workbook,$sVal,$oFormat);

  # Create a new workbook and add a worksheet.
  $sFileFormat = 'xls' if ( ! $sFileFormat );
  if ( $sFileFormat =~ /^xlsx$/i ){
        $workbook  = Excel::Writer::XLSX->new("$sFilename") || die ("Couldn't create: $sFilename");
  } 
  elsif ( $sFileFormat =~ /^xls$/i ){
        $workbook  = Spreadsheet::WriteExcel->new("$sFilename") || die ("Couldn't create: $sFilename");
  }
  else {
        die ("Error: Unknown file format");
  }
  my $worksheet = $workbook->add_worksheet();

  # Define a few commonly used pre-set Formats /Styles that can be applied by NAME
  # Title format
  $oTitleFormat = $workbook->add_format(
                                        bg_color => 22,   
                                        color    => 8,   
                                        pattern  => 1,
                                        border   => 1,
                                        bold     => 1
                                      );
  #Add any additional columns
  $iCol=0;
  $iRow=0;
  foreach $sKey ( @{$raOutFields} ) {
   $worksheet->write($iRow, $iCol++, $sKey, $oTitleFormat);
  }
  #Freeze the first Row
  $worksheet->freeze_panes(1, 0);

  #Write any passed JSON Data 
  if ($oJsonData){ 
    foreach $rhDoc ( @{$oJsonData} ) {
        #Add any additional columns, untangling the nesting in the field names (Frigged, should write something recursive)
        $iRow++;
        $iCol=0;
        foreach $sKey ( @{$raOutFields} ) {
                $sVal = getValue($sKey, $rhDoc, 'true');
                if ( ref($sVal) eq 'HASH' ) {
                        $oFormat = undef;
                        if ( $sVal->{ 'format' } ) {
                                if ( ref($sVal->{ 'format' }) eq 'HASH') {
                                        $oFormat = $workbook->add_format($sVal->{ 'format' });
                                } 
                        }
                        $worksheet->write($iRow, $iCol, $sVal->{ 'value' }, $oFormat );
                } else {
                        $worksheet->write($iRow, $iCol, $sVal);   
                }
                $iCol++;
        } # End - foreach $sKey
    } #End Foreach row (JSON object)
  }
} # End Sub

# --------------------------------------------
sub convertToDojoGrid($$$$){
  my ($rhMongoCursor, $oJsonData, $raOutFields, $iPreviewRows)=@_;

  my ($iRow,$iCol,$rhDoc,$titleFormat,$sKey,$sPage,$sData,$sVal);
  $sPage="<h2>Preview</h2>\n";

  #Add any additional columns
  $iCol=0;
  $iRow=0;
  $sData="";
  foreach $sKey ( @{$raOutFields} ) {
    $sData.="<th field=\"$sKey\" width=\"auto\">$sKey</th>\n";
  }
  $sPage.=<<END_HTML;
    <div style="width: 90%; height: 200px">
        <table id="jsonData" dojoType="dojox.grid.DataGrid">
            <thead>
                <tr>
END_HTML
  $sPage.=$sData;
  $sPage.=<<END_HTML;
                </tr>
            </thead>
        </table>
    </div>
<script type="text/javascript"
 src="http://ajax.googleapis.com/ajax/libs/dojo/1.5/dojo/dojo.xd.js"
    djConfig="parseOnLoad:true"></script>

<script type="text/javascript">
    dojo.require("dojox.grid.DataGrid");
    dojo.require("dojo.data.ItemFileReadStore");
</script>

<script type="text/javascript">
dojo.ready(function() {
END_HTML

  $sData="var oDataForGrid = {\nitems: [";

  #Read and write any data from the passed Json object
  if ($oJsonData){
    foreach $rhDoc ( @{$oJsonData} ) {
        #Add any additional columns
        $sData.=",\n"                                               if($iRow>0);
        $iRow++;
        $iCol=0;
        $sData.="\t{\n";
        foreach $sKey ( @{$raOutFields} ) {
                $sData.=",\n"                                           if($iCol>0);
                $sVal = (getValue($sKey, $rhDoc));
                $sVal =~ s/\x22/\\\"/g;
                $sData.="\t\"$sKey\":\"".$sVal."\"";
                $iCol++;
        } # End - foreach $sKey
        $sData.="\n\t}";
        last if( $iPreviewRows && $iPreviewRows eq $iRow); 
    } #End Foreach row (JSON object)
  }

  #Read and write any data from the passed cursor
  if ($rhMongoCursor){
    while ($rhDoc = $rhMongoCursor->next) {
        #Add any additional columns
        $sData.=",\n"                                               if($iRow>0);
        $iRow++;
        $iCol=0;
        $sData.="\t{\n";
        foreach $sKey ( @{$raOutFields} ) {
                $sData.=",\n"                                           if($iCol>0);
                $sVal = (getValue($sKey, $rhDoc));
                $sVal =~ s/\x22/\\\"/g;
                $sData.="\t\"$sKey\":\"".$sVal."\"";
                $iCol++;
        } # End - foreach $sKey
        $sData.="\n\t}";
        last if( $iPreviewRows && $iPreviewRows eq $iRow);
    } #End Foreach row (JSON object)
  }

  $sData.="\n]\n};\n";
  $sPage.=$sData;
  $sPage.=<<END_HTML;
  var dataStore =
    new dojo.data.ItemFileReadStore(
        { data:oDataForGrid }
    );
    var grid = dijit.byId("jsonData");
    grid.setStore(dataStore);
});
</script>
END_HTML
  return $sPage;
} ## End sub

# --------------------------------------------
sub getValue{
  my ($sKey, $rhDoc,$bReturnRef)=@_;
  my ($sKeyPt1,$sKeyPt2,$sKeyPt3,$refType);
  $refType="";

  if ( ($sKey =~ /^[^.]*[.]/) ){
                ($sKeyPt1, $sKeyPt2) = ($sKey =~  /^([^.]+)\.(.+)$/ );
                $refType = reftype($rhDoc->{$sKeyPt1});
                if ( $refType && $refType eq 'HASH' ) {
                        return getValue($sKeyPt2, $rhDoc->{$sKeyPt1});
                }
                elsif ( $refType && $refType eq 'ARRAY' ) {
                        if ( ($sKeyPt2 =~ /^[0-9]+$/) ){
                                return ($rhDoc->{$sKeyPt1}[$sKeyPt2])           if ( isNotARef($rhDoc->{$sKeyPt1}[$sKeyPt2]));
                        } else {
                                if ( ($sKeyPt2 =~ /^[0-9]+[.]/) ){
                                        ($sKeyPt2, $sKeyPt3) = ($sKeyPt2 =~  /^([^.]+)\.(.+)$/ );
                                        return getValue($sKeyPt3, $rhDoc->{$sKeyPt1}[$sKeyPt2]);
                                } else {  #Default to first element of ARRAY, if no index given
                                        return getValue($sKeyPt2, $rhDoc->{$sKeyPt1}[0]);
                                }
                        }
                }
  } else {
        if ( ref($rhDoc->{$sKey}) eq "DateTime") {
                my $dt = $rhDoc->{$sKey};
                $dt->set_time_zone('UTC');
                return ($dt->datetime);
        }
        return  $rhDoc->{$sKey}                                                              if ( $bReturnRef || isNotARef($rhDoc->{$sKey}));
  }
  return ("");
} ## End sub

# --------------------------------------------
sub isNotARef($){
  my ($refToTest)=@_;

        if( ! ref($refToTest) || ref($refToTest) eq 'boolean'){
                return 1;
        } else {
                return 0;
        }
}
# --------------------------------------------

Open in new window


If all goes well you should see a page along the following lines:Previewing the example JSON document
0
Comment
Author:arober11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 26

Author Comment

by:arober11
Possibly also of interest:

Java: Json to Excel
Python: StackOverflow thread
javaScript: jsFiddle - JSON to CSV Converter
Web based:  Json 2 HTML
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month