Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Export CSV v2

by Perl3r (Initiate)
on Oct 08, 2012 at 23:39 UTC ( #997896=perlquestion: print w/replies, xml ) Need Help??
Perl3r has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,
Ok so i have the following script which I have been working on, with the help of some fantastic people on this forum.. I have come to a bit of a wall though, and was hoping that some more eyes might be able to assist.
The background is that I have a CSV file, that contains a collection of IP addresses, and settings that I want to export to a router configuration template..
The first line of the CSV is column descriptions, the second line is a list of variables that is used in the template file - i.e. ##rtrname## in the template file, is replaced with the value in the CSV file.. The last column in the CSV is a list of which template to use - ie router.txt
The problem however is that the CSV file needs to be edited, and maintained within MS Excel. I have successfully got the script running, if the CSV file is maintained in something like TextEditor, but as soon as you edit in Excel, it messes with the format, and adds extra info to the end of each line.. I started to use Text::CSV to try and overcome this, but I have encountered some problems with that
My code as it stands at the moment is:

#!/usr/bin/perl # use strict; use warnings; use Text::CSV; $ARGV[0] or die "Usage: $0 <filename> [<filename>] ..."; my ( $template_file_name, $templateText, %hash ) = ''; my $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 2, }) or die "Cannot use CSV: " . Text::CSV->error_diag(); for my $csvFile (@ARGV) { print "\nProcesing file $csvFile ...\n"; open my $csvfh, '<', $csvFile or die "Unable to open $csvFile: $!" +; # Ignore column names in first line my $columnNames = $csv->getline($csvfh); # Get vars from second line my @vars = @{ $csv->getline($csvfh) }; # Process each csv line while ( my $row = $csv->getline($csvfh) ) { next unless @$row; my $templateFN = pop @$row; if ( $template_file_name ne $templateFN ) { $template_file_name = $templateFN; undef $templateText; } # keys: fields from second line; values: fields from csv line @hash{@vars} = @$row; $templateText //= getTemplateText($template_file_name); my $templateTextCopy = $templateText; $templateTextCopy =~ s/$_/$hash{$_}/g for keys %hash; my $ofile_name = $hash{'##rtrname##'} . '.txt'; print "Writing to file: $ofile_name\n"; open my $fh, '>', $ofile_name or die "$ofile_name: $!"; print $fh $templateTextCopy; close $fh; } close $csvfh; } print "\nDone!\n"; sub getTemplateText { my ($template_file_name) = @_; local $/; open my $fh, '<', $template_file_name or die "$template_file_name: + $!"; $templateText = <$fh>; close $fh; return $templateText; }

The problem that i get when I run this code, is the following error:

Can't use an undefined value as an ARRAY reference at line 22, <$csvfh> line 1.

an example of the CSV is as follows

Location,Router Name,Loopback IP,OSPF Process ID,OSPF Area,Network,Mas +k,GIG0/0 IP Address,0/0 Subnet Mask,Template ##location##,##rtrname##,##loop0-ip##,##ospf-id##,##ospf-area##,##ospf +-network##,##ospf-mask##,##eth00-ip##,##eth00-sm##, Sydney,SYD-RTR-001,,99,0,,, +,,router

Any assistance would be greatly appreciated!!
Thanks in advance

Replies are listed 'Best First'.
Re: Export CSV v2
by GrandFather (Sage) on Oct 09, 2012 at 00:10 UTC

    You would get that error for an empty file or a file that has more than one blank line at the start. If you are running the script on *nix, but create the file under Windows then you may have a line end issue.

    If you can't figure out the problem then you need to generate a script we can test. See I know what I mean. Why don't you? for hints on putting a sample script together that doesn't require separately provided external test files.

    True laziness is hard work

      Thanks Grandfather..
      File was generated on a Mac OS X, and was edited in MS Excel. Code is being run on a Mac OS X aswell, but would like it to be able to run on any *nux system.
      The CSV file has no blank lines at the start, but i do believe it to be a line end issue, just not sure how to resolve..

        Try adding:

        $csv->eol ("\x0d\x0a");

        after the my $csv = Text::CSV_XS->new ... statement. Probably Excel is generating Windows line ends even on a Mac and Perl is expecting either *nix of (legacy) Mac line endings.

        True laziness is hard work
Re: Export CSV v2
by choroba (Bishop) on Oct 09, 2012 at 00:52 UTC
    Have you looked at line 22?
    # Get vars from second line my @vars = @{ $csv->getline($csvfh) };
    When does getline return an undef? Your input probably contains only one line, already consumed by $columnNames.
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

      If getline () returns undef it has to be end-of-file, because in all other cases auto_diag => 2 will cause a croak (). It is possible to get an empty list there, but that would not result in an error on that point.

      Enjoy, Have FUN! H.Merijn

      Thanks choroba
      Input files contains at the moment 5 lines.. eg

      Location,Router_Name,Loopback IP,OSPF Process ID,OSPF Area,Network,Mas +k,GIG0/0 IP Address,0/0 Subnet Mask,Template ##location##,##rtrname##,##loop0-ip##,##ospf-id##,##ospf-area##,##ospf +-network##,##ospf-mask##,##eth00-ip##,##eth00-sm##, Sydney,SYD-RTR-001,,99,0,,, +,,router-C3840 Canberra,CBR-RTR-001,,99,0,,,172.25.16 +.10,,router-C2600 Melbourne,MEL-RTR-001,,99,0,,,172.25.1 +6.14,,router-C3840


        I am suspicious of that comma at the end of line 2.


        Then you are probably setting the end-of-line to something else than actually present in the data.
        لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://997896]
Approved by GrandFather
[jrmcc]: Your problem was pregnant, missing a period!

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (13)
As of 2018-05-22 16:47 GMT
Find Nodes?
    Voting Booth?