Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Output to a >CSV file

by pragovnj (Acolyte)
on Jan 28, 2020 at 20:58 UTC ( [id://11111987]=perlquestion: print w/replies, xml ) Need Help??

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

<p Hi, I have a perl code which reads data from BigIron. I provide a URL link to the Users, which they can paste on an IE browser and get the data in html format. http://sample.web.com/Ethernet?sd=20191014&ed=20191015&sep Is there a way to output this URL as a CSV or a Excel file?

#!/usr/bin/perl require 'ConvertDates.pl'; use DBI; use Date::Manip ; use CGI; $parms = CGI->new; #parse parameters $start_date = $parms->param("sd"); $end_date = $parms->param("ed"); $separator= $parms->param("sep"); $HOME="/export/home/pmdss"; $CONFIG="${HOME}/config"; $row=0; $rows=0; $NumOfRts=1500; $CntOfRts=0; print "Content-type: text/plain\n\n" ; chomp($ds_mach=`cat ${CONFIG}/BigIron`); chomp($dr_mach=`cat ${CONFIG}/BigIronDR`); system("/usr/sbin/ping $ds_mach >/dev/null 2>/dev/null"); if ( ($? >> 8) eq 0 ) { $id = "pmdss\@$ds_mach"; } else { system("/usr/sbin/ping $dr_mach >/dev/null 2>/dev/null"); if ( ($? >> 8) eq 0 ) { $id = "pmdss\@$dr_mach"; } else { print "A serious error has occurred, please contact pr +oduction support or try again later...\n" ; exit 0; } } $cmd = "ssh $id '. ~/q; \$Q/AgnEthernetList3-1.X -s $start_date -e $en +d_date' 2>/dev/null |"; #print "$cmd\n"; open (input , $cmd); while ($INPUT_LINE = <input>) { chomp($INPUT_LINE); ($dat{l_node}, $dat{l_slot}, $dat{l_ifNum}, $dat{r_node}, $dat +{r_slot}, $dat{r_line}, $dat{r_port}, $dat{kbps}, $dat{subnetwork}, $dat{subnetwork2}, $dat{r_domain_id}, $dat{i +core_domid}, $dat{cust_port}, $dat{track_port},$dat{vlan_stacking_inv}, $dat{cos_level}, $da +t{equip_type_id}) = split(/ /, $INPUT_LINE); $KEY = $dat{l_node} . "." . $dat{l_slot} . "." . $dat{l_ifNum +}; $key2 = int($CntOfRts++/$NumOfRts); ($nspString{$key2} = $nspString{$key2} . ":") if ($nspString{$ +key2} ne ""); $nspString{$key2} = $nspString{$key2} . "$dat{l_node},$dat{l_s +lot},$dat{l_ifNum}"; $arraykey{$KEY} = $KEY; $r_node{$KEY} = $dat{r_node}; $r_slot{$KEY} = $dat{r_slot}; $r_port{$KEY} = $dat{r_port}; $kbps{$KEY} = $dat{kbps}; $subnetwork{$KEY} = $dat{subnetwork}; $subnetwork2{$KEY} = $dat{subnetwork2}; $r_domain_id{$KEY} = $dat{r_domain_id}; ($r_domain_id{$KEY} = $dat{icore_domid}) if ($r_domain_id{$KEY +} eq ""); $cust_port{$KEY} = $dat{cust_port}; $track_port{$KEY} = $dat{track_port}; $vlan_stacking_inv{$KEY} = $dat{vlan_stacking_inv}; $cos_level{$KEY} = $dat{cos_level}; $rows = $rows + 1; } close (input); foreach $key (sort keys nspString) { $cmd = "ssh $id '. ~/q; \$Q/AgnEthernetList3-2.X -nsp $nspStri +ng{$key}' 2>/dev/null |"; #print "$cmd\n"; open (input , $cmd); while ($INPUT_LINE = <input>) { chomp($INPUT_LINE); ($dat2{pvcs}, $dat2{lcir}, $dat2{rcir}, $rswitch, $rsl +ot, $rport) = split(/ /, $INPUT_LINE); $KEY = $rswitch . "." . $rslot . "." . $rport; $pvcs{$KEY} = $dat2{pvcs}; $lcir{$KEY} = $dat2{lcir}; $rcir{$KEY} = $dat2{rcir}; } close (input); } $cmd = "ssh $id '. ~/q; \$Q/AgnNniGatewayList4-4.X -date $end_date' 2> +/dev/null |"; open (input , $cmd); #print "$cmd\n"; while ($INPUT_LINE = <input>) { chomp($INPUT_LINE); ($dat{object_id}, $dat{domain}, $dat{long_name}) = split(/ /, + $INPUT_LINE); $toNetwork{$dat{object_id}} = $dat{long_name}; $domain{$dat{object_id}} = $dat{domain}; } close (input); if ($rows > 0) { print "l_node\t" . "l_slot\t" . "l_port\t" . "r_node\t" . "r_slot\t" . "r_port\t" . "kbps\t" . "subnetwork\t" . "toNetwork\t" . "pvcs\t" . "lcir\t" . "rcir\t" . "domain\t" . "track_port\t" . "vlan_stacking_inv\t" . "cos_level\t" . "placeholder"; foreach $key (keys arraykey) { ($l_node,$l_slot,$l_ifNum) = split(/\./, $key); if (substr($l_node,10,3) eq "ME2") { if ($l_slot >= 10000) { if ( substr($l_slot,1,1) eq "0" ) { $L_SLOT = substr($l_slot,2,1) +. "-" . substr($l_slot,4,1) ; } else { $L_SLOT = substr($l_slot,1,2) +. "-" . substr($l_slot,4,1) ; } } else { $L_SLOT = $l_slot ; } } else { $L_SLOT = $l_slot ; } if (TRIM($toNetwork{$r_domain_id{$key}}) ne "") { $_2Network = $toNetwork{$r_domain_id{$key}}; } else { $_2Network = $subnetwork2{$key}; } print "\n" . $l_node . "\t" . $L_SLOT . "\t" . $l_ifNum . "\t" . $r_node{$key} . "\t" . $r_slot{$key} . "\t" . $r_port{$key} . "\t" . $kbps{$key} . "\t" . $subnetwork{$key} . "\t" . $_2Network . "\t" . $pvcs{$key} . "\t" . $lcir{$key} . "\t" . $rcir{$key} . "\t" . $domain{$r_domain_id{$key}} . "\t" . $track_port{$key} . "\t" . $vlan_stacking_inv{$key} . "\t" . $cos_level{$key} . "\t" . "X"; } } else { print `cat nodata`; } sub TRIM { $A = shift; $_ = $A; s/^ +//; s/ +$//; $A = $_; return $A; }

Replies are listed 'Best First'.
Re: Output to a >CSV file
by haukex (Archbishop) on Jan 28, 2020 at 21:08 UTC
Re: Output to a >CSV file
by NetWallah (Canon) on Jan 29, 2020 at 01:16 UTC
    I'm (possibly mis-)interpreting your question as being related to HTML.

    Your code shows that you are currently sending only TEXT output:
    print "Content-type: text/plain\n\n" ;

    If you want other outputs available, you will need to change to multipart HTML output, and add CSV or Excel content.

    See RFC 4180.

                    "From there to here, from here to there, funny things are everywhere." -- Dr. Seuss

Re: Output to a >CSV file
by Anonymous Monk on Jan 29, 2020 at 03:33 UTC

    Hi

    You need to organize your code, box up everything into subroutines, Every loop, every every giant if/else block, every system call, every level of nested nesting.

    And name your variables good, gets easier once you start coping with scoping, Re^3: A Beginner Needs Homework help

    So in the end you have your final program something like this, cgi101

    Main( @ARGV ); exit( 0 ); sub Main { my $config = "${HOME}/config"; my $cgi = CGI->new; my $type = $cgi->param('type'); $type eq 'html' and return HtmlPage( $cgi, $config ); $type eq 'xls' and return XlsPage( $cgi, $config ); $type eq 'csv' and return CsvPage( $cgi, $config ); return HtmlPage( $cgi, $config ); # default } sub XlsPage { my( $cgi ) = @_; MakeXls( $cgi , '/path/to/foo.xls' ); SendFile( $cgi, '/path/to/foo.xls', 'foo-downloaded.xls', 'application/vnd.ms-excel', ); } sub XlsPage { my( $cgi ) = @_; MakeCsv( $cgi , '/path/to/foo.csv' ); SendFile( $cgi, '/path/to/foo.csv', 'foo-downloaded.csv', 'text/csv', ); }

    Use $cgi->header for headers, like to send files to be downloaded by users

    sub SendFile { my $cgi = shift || CGI->new; my $file = shift or die "What file to send?!"; my $filename = shift || 'filename'; my $mimetype = shift || 'application/octet-stream'; open my($fh),'<:raw', $file or die "Can't read ($file): $!\n$^E\n +"; binmode STDOUT; print $cgi->header( -type => $mimetype, -attachment => $filename, -Content_length => -s $fh, ); # print $_ while readline $fh; local $/; ## slurp mode print readline($fh); close $fh; close STDOUT; }

    So starting with the program you have, first step, create the outline, replace blocks with subroutine calls, use your own good names

    Well, removing the comments to see how real shape of goals, you've got sub Main101

    #!/usr/bin/perl require 'ConvertDates.pl'; use DBI; use Date::Manip ; use CGI; Main( @ARGV ); exit( 0 ); sub Main { my $cgi = CGI->new; my $start_date = $parms->param("sd"); my $end_date = $parms->param("ed"); my $separator= $parms->param("sep"); my $HOME="/export/home/pmdss"; my $CONFIG="${HOME}/config"; my $ds_mach = CatConfig( "${CONFIG}/BigIron" ); my $dr_mach = CatConfig( "${CONFIG}/BigIronDR" ); my $id = PingerIdOrDeath( $ds_mach , $dr_mach ); my $slotsPortsNodes = sshMeSomeSlotsPortsNodes( $id, $start_date, + $end_date ); my( $toNetwork, $domain ) = sshMeSomeObjectIdsDomainNames( $id, $ +end_date ); if ($rows > 0) { print CsvSlotsHeader( $slotsPortsNodes ); print CsvSlotsData( $slotsPortsNodes ); } else { print NoData(); } } __END__

    Then fill in the blanks , notice subroutines take arguments, return values, a hash of hashes, or whatever fits the problem

    sub sshMeSomeSlotsPortsNodes { my( $id, $start_date, $end_date ) = @_; my %network; ... while ($INPUT_LINE = <input>) { my %dat; ( $dat{l_node}, $dat{l_slot}, $dat{l_ifNum}, $dat{r_node}, $dat{r_slot}, $dat{r_line}, $dat{r_port}, $dat{kbps}, $dat{subnetwork}, $dat{subnetwork2}, $dat{r_domain_id}, $dat{icore_domid}, $dat{cust_port}, $dat{track_port}, $dat{vlan_stacking_i +nv}, $dat{cos_level}, $dat{equip_type_id} ) = split( / /, $INPUT_LINE ); $network{r_node}{$KEY} = $dat{r_node}; $network{r_slot}{$KEY} = $dat{r_slot}; $network{r_port}{$KEY} = $dat{r_port}; ... $network{arraykey}{$KEY}=$KEY; ... } return \%network; }

    Now plug it into the cgi101 sub Main, maybe as sub MakeCsv or something

    Every time you make a sub, backup the file you're working on -01.pl -02.pl -03.pl -04.pl...

      > Every time you make a sub, backup the file you're working on -01.pl -02.pl -03.pl -04.pl...

      Or use a version control tool like git.

      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

        > > Every time you make a sub, backup the file you're working on -01.pl -02.pl -03.pl -04.pl...

        > Or use a version control tool like git.

        do it even if you're using version control tool like git

        each time you start work on a new subroutine start a new file, or even a new directory

        as you try things out/make changes ... its amazing what you can learn about yourself when you revisit your attempts days/weeks/months/years later

          A reply falls below the community's threshold of quality. You may see it by logging in.
      Hi, Sorry for the delayed reply. Thanks for the response. What I am not sure is you have commented all my codes which refer to the CGI queries and fields. Can you re-post your complete code which includes my queries as well or point to me in the right direction? Thanks, Pragov

        What I am not sure is you have commented all my codes which refer to the CGI queries and fields.

        As all the words outside of code tags explain

        Because that is the first step in fixing your program

        The code as you have posted it cannot be debugged by anybody

        Can you re-post your complete code which includes my queries as well or point to me in the right direction?

        This is what I have attempted to do in Re: Output to a >CSV file

        The "..." is where you fill in the blanks

        #!/usr/bin/perl -- use CGI::Carp qw(fatalsToBrowser warningsToBrowser); use DBI; use Date::Manip ; use CGI; Main( @ARGV ); exit( 0 ); sub Main { my $cgi = CGI->new; my $start_date = $parms->param("sd"); my $end_date = $parms->param("ed"); my $separator= $parms->param("sep"); my $HOME="/export/home/pmdss"; my $CONFIG="${HOME}/config"; my $ds_mach = CatConfig( "${CONFIG}/BigIron" ); my $dr_mach = CatConfig( "${CONFIG}/BigIronDR" ); my $id = PingerIdOrDeath( $ds_mach , $dr_mach ); my $slotsPortsNodes = sshMeSomeSlotsPortsNodes( $id, $start_date, + $end_date ); my( $toNetwork, $domain ) = sshMeSomeObjectIdsDomainNames( $id, $ +end_date ); if ($rows > 0) { print CsvSlotsHeader( $slotsPortsNodes ); print CsvSlotsData( $slotsPortsNodes ); } else { print NoData(); } } sub CatConfig { my( $file ) = @_; ... sub PingerIdOrDeath { my( $ds_mach, $dr_mach ) = @_; ... sub sshMeSomeSlotsPortsNodes { my( $id, $start_date, $end_date ) = @_; my %network; ... while ($INPUT_LINE = <input>) { my %dat; ( $dat{l_node}, $dat{l_slot}, $dat{l_ifNum}, $dat{r_node}, $dat{r_slot}, $dat{r_line}, $dat{r_port}, $dat{kbps}, $dat{subnetwork}, $dat{subnetwork2}, $dat{r_domain_id}, $dat{icore_domid}, $dat{cust_port}, $dat{track_port}, $dat{vlan_stacking_i +nv}, $dat{cos_level}, $dat{equip_type_id} ) = split( / /, $INPUT_LINE ); $network{r_node}{$KEY} = $dat{r_node}; $network{r_slot}{$KEY} = $dat{r_slot}; $network{r_port}{$KEY} = $dat{r_port}; ... $network{arraykey}{$KEY}=$KEY; ... } return \%network; } sub sshMeSomeObjectIdsDomainNames { my( $id, $end_date ) = @_; ... sub CsvSlotsHeader { my($slotsPortsNodes ) = @_; ... sub CsvSlotsData { my($slotsPortsNodes) = @_; ... sub NoData ...

        Questions?

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11111987]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2024-03-29 08:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found