Altering a CSV file with perl

{local $^I=".bak"; local @ARGV=("currency.csv");
 while( <> ){
    s/\$,[\d.]+,/\$,$exchange_rate,/;
    print;
 }
}

If you know everything in the file will be the same, and you just want to re-write the file with the updated US rate, you can use:

open(my $out, ">currency.csv") or die "Could not open file: $!\n";
print $out "currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,$,$exchange_rate,us.png\n";
close($out);

Select allOpen in new window

USD,$,$exchange_rate,us.png\n";
should be
USD,\$,$exchange_rate,us.png\n";
(unless $,='$,')

Thanks ozo... copied/pasted

Hi Adam,

Thank you and Ozo for your prompt suggestions.
I think I will give your solution a go as it looks the most straightforward and everything else will be the same, just the US currency rate that changes. Do I add the extra bit (unless $,='$,') which Ozo suggested?

Finally could you tell me which modules I need to include in the file such as DBD::CSV or LWP::Simple ?
I only mention these two as they were used in another example I found but it didn't work anyway!

Regards,

Brian

Hello again Adam,
I tried the above code, both with and without the bit Ozo added but it just crashes with the following error:

500 Internal Server Error.

If I include the code I have so far that does the other bit perhaps you could take a look to see if I am including something that clashes with the open and close function you suggested.

------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w
#use strict
#use warnings

# Output a header. We must do this!
print "Content-type: text/html\n\n";

#----------------------------------------------------#
$input = get('http://www.I have removed the name of my exchange rate source from here')
     or die("Unable to access exchange rate server, please try later");

$codefound = index($input,"GBP-DOLLAR");
if ($codefound <0)   {exit;}  # Exit if not a valid exchange symbol.
$codefound +=35;      #This may need changing, it does not take into account length of different month names
$exchange_rate = substr($input,$codefound,6);
$spot_rate = $exchange_rate;


$exchange_rate = $exchange_rate * 107/100;      #Change percentage on this line.

# End of script!
----------------------------------------------------------------------------------

Regards,

Brian

Do you have access to your error log?  This will give you more details about the actual error.  You could also add this line near the top (typically after the "use strict" and "use warnings" lines), it will cause more detailed error messages to be displayed to your browser:
    use CGI::Carp 'fatalsToBrowser';

But, if the script was working before, and the new code is what is causing the problem, then I'm guessing the web server user does not have permission to open the file for write access.   If this is the case, you will have to change the permission on the file.

You may also need to use the full path to the file.

The change ozo made was to correct a bug in what I posted.... the $ sign needed a backslash in front of it.  Here is the code again, with the fix ozo suggested.  You will have to change "/path/to/currency.csv" to your actual path.  If you are on widows, use forward slashes instead of backslashes.

open(my $out, ">/path/to/currency.csv") or die "Could not open file: $!\n";
print $out "currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,\$,$exchange_rate,us.png\n";
close($out);

Select allOpen in new window

Hi Adam,
I am away until tomorrow afternoon but will get on it when I return.

My server (which is a dedicated server) is unix, I have full permissions for all, in fact I changed  the relevant files to 777. I will try the full path as you suggested when I return to see if that helps and also the errors to browser.

Regards,

Brian

#!/usr/bin/perl -w
#use strict
#use warnings

# Output a header. We must do this!
print "Content-type: text/html\n\n";

#----------------------------------------------------#
$input = get('http://www.I have removed the name of my exchange rate source from here')

did you mean to
use LWP::Simple;
or some such?

(unless $,='$,')
was meant to be a comment, not code

$codefound = index($input,"GBP-DOLLAR");
if ($codefound <0)   {exit;}  # Exit if not a valid exchange symbol.
$codefound +=35;      #This may need changing, it does not take into account length of different month names
$exchange_rate = substr($input,$codefound,6);

what is the format of $input?
it it is not fixed width fields, index and substr nay not be the best way to parse it,

Hi ozo, input is always the content of a csv file which only changes in the USD rate, see below.

currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,$,1.93,us.png
-----------------------

I have tried the code you and Adam have suggested but in every case it gives an "Error: 500 Internal Server Error" even with  use CGI::Carp 'fatalsToBrowser';

I have since found another piece of code that almost seems to work, I have reproduced the whole file below, this code which uses  "IO::CSVHeaderFile does at least get as far as allowing Carp to write to the browser. The code is:
-------------------------------------------------------
#!/usr/bin/perl -w
#use strict
#use warnings
use CGI::Carp 'fatalsToBrowser';

################################################################################

 use LWP::Simple;
 use IO::CSVHeaderFile;


# Output a header. We must do this!
print "Content-type: text/html\n\n";

$filename = "/home/bwardsmi/westcountryviolins.com/cgi-bin/currency-test/currency-test.csv";

#-------------------------------------------------------------------------------
$input = get('http://www.#!/usr/bin/perl -w
#use strict
#use warnings
use CGI::Carp 'fatalsToBrowser';

################################################################################

 use LWP::Simple;
 use IO::CSVHeaderFile;


# Output a header. We must do this!
print "Content-type: text/html\n\n";

$filename = "/full path to file/currency-test.csv";

#-------------------------------------------------------------------------------
$input = get('http://www.I have removed the name of my exchange rate source from here')
     or die("Unable to access exchange rate server");

$codefound = index($input,"GBP-DOLLAR");
if ($codefound <0)   {exit;}  # Exit if not a valid exchange symbol.
$codefound +=35;      #This may need changing, it does not take into account length of different month names
$exchange_rate = substr($input,$codefound,6);
$spot_rate = $exchange_rate;

$exchange_rate = $exchange_rate * 107/100;      #Change percentage on this line.

#----------------------------------------------------#


my $csv = IO::CSVHeaderFile->new( "> $filename" ,
        {col => ['ColHeaderTitle1','ColHeaderTitle2','ColHeaderTitle1'], noheaders => 1} );
    $csv->csv_print({ColHeaderTitle1 => 'First', ColHeaderTitle2 => 'Second'}) or return;
    $csv->csv_print(['Uno', 'Duo', 'Tre']) or return;
    $csv->csv_print(
        ColHeaderTitle1 => 'One',
        ColHeaderTitle2 => 'Two',
        ColHeaderTitle1 => 'Three with the same name as One'
        ) or return;
  $csv->close;

# End of script!')

The error that this shows is "Can't call method "csv_print" on an undefined value at /home/bwardsmi/westcountryviolins.com/cgi-bin/currency-test/currency-bbc-3.pl line 39."

I realise that this does not do what I want but apart from the "print" it seems to work, how would I adapt this? sorry to be such a pain.

Regards,

Brian
 

$input = get('http://www.#!/usr/bin/perl -w
#use strict
#use warnings
use CGI::Carp 'fatalsToBrowser';


is not valid code
the quoted string extends from $input = get('
to
'fatalsToBrowser
so fatalsToBrowser looks like code that is part of the expression passed to get
which makes no sense

I don't see the string "GBP-DOLLAR" in

currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,$,1.93,us.png

so if that's what's in $input,  index($input,"GBP-DOLLAR") would return -1

Can't call method "csv_print" on an undefined value at
would suggest that IO::CSVHeaderFile->new returned under
you might want so test whether it succeeds and check $! if it does not

Hi ozo,
Sorry I printed some bits twice, it should be:
###################################
#!/usr/bin/perl -w
#use strict
#use warnings
use CGI::Carp 'fatalsToBrowser';
use LWP::Simple;
use IO::CSVHeaderFile;

# Output a header. We must do this!
print "Content-type: text/html\n\n";

$filename = "/full path to file/currency-test.csv";

#-------------------------------------------------------------------------------
$input = get('http://www.I have removed the name of my exchange rate source from here')
     or die("Unable to access exchange rate server");

$codefound = index($input,"GBP-DOLLAR");
if ($codefound <0)   {exit;}  # Exit if not a valid exchange symbol.
$codefound +=35;      #This may need changing, it does not take into account length of different month names
$exchange_rate = substr($input,$codefound,6);
$spot_rate = $exchange_rate;

$exchange_rate = $exchange_rate * 107/100;      #Change percentage on this line.

#----------------------------------------------------#


my $csv = IO::CSVHeaderFile->new( "> $filename" ,
        {col => ['ColHeaderTitle1','ColHeaderTitle2','ColHeaderTitle1'], noheaders => 1} );
    $csv->csv_print({ColHeaderTitle1 => 'First', ColHeaderTitle2 => 'Second'}) or return;
    $csv->csv_print(['Uno', 'Duo', 'Tre']) or return;
    $csv->csv_print(
        ColHeaderTitle1 => 'One',
        ColHeaderTitle2 => 'Two',
        ColHeaderTitle1 => 'Three with the same name as One'
        ) or return;
  $csv->close;

# End of script!')

###############

The bit just above here using the CSVHeaderFile is the new bit I added, I found a lot of post where people said it was difficult to write to csv files and this bit above was on the cpan site but I don't have a clue how to adapt it to my needs,

When I run it the error is:
"Can't call method "csv_print" on an undefined value at /home/bwardsmi/westcountryviolins.com/cgi-bin/currency-test/currency-bbc-3.pl line 39."

Which is to be expected as I am not using this bit correctly, how can I change it to write to the file what I want, any data would do initially. I suppose the mere fact it is giving me this message and not failing with a 500 server error means that at least the methos of using the CSVHeaderFile is valid!

Regards,

Brian

A fully general csv file may be difficult for some people to write, but a csv file is only

currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,$,1.93,us.png

it should not be any more difficult to write than to use an unfamiliar module.

Hi ozo,
I appreciate what you are saying and certainly it would be easier for me to understand if it would work with the original code:

--------------------------------------------------
open(my $out, ">/path/to/currency.csv") or die "Could not open file: $!\n";
print $out "currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,\$,$exchange_rate,us.png\n";
close($out);
--------------------------------------------------

That would be great but it does not even make it as far as sending errors to the browser, it just crashes with a 500 server error.

By the way the use LWP::Simple; is needed in order to fetch the currency rate in the first place, that part of the file works fine but when I add the above code that adam suggested it crashes. Could it be that I need to add some more "use module" lines?

Regards,

Brian

what does your program  do if you simply omit the code

open(my $out, ">/path/to/currency.csv") or die "Could not open file: $!\n";
print $out "currency_id,currency_symbol,currency_rate,currency_image
GBP,£,1,uk.png
USD,\$,$exchange_rate,us.png\n";
close($out);

and where in your program did you add that code?

Hi ozo,
It fetches the exchange rate from the server and before I added the code you just mentioned above I had it send the results of the currency rate update to a formatted browser page which worked fine.

Regards,

Brian

Hi ozo,
I am now trying a different approach that does seem to work, although I have to learn how to format the string to be written.

Instead of using IO::CSVHeaderFile I am using TEXT::CSV_XS The code to open and write to the file is:

#################################################
my $csv = Text::CSV_XS->new;

  open my $csv_fh, ">", "currency-test.csv" or die "currency-test.csv: $!";

  my @sample_input_fields = (
      currency_id, currency_symbol, currency_rate, currency_image, GBP);
  if ($csv->combine (@sample_input_fields)) {
      my $string = $csv->string;
      print $csv_fh "$string\n";
      }
  else {
      my $err = $csv->error_input;
      print "combine () failed on argument: ", $err, "\n";
      }
  close $csv_fh or die "currency-test.csv: $!";
#################################################

This works! It prints "currency_id,currency_symbol,currency_rate,currency_image,GBP," to the file, however the £ sign throws an error so perhaps I need to use the ascii code but also I cannot get it to include a newline, I tried using \n at the end of currency_image but it does not like that either. Still I am getting there! At least it does open the file and print a line of CSV text into it!!

Regards,

Brian

What error does the £ sign throw?
I see no  £ sign in your code, which appears to only write the first line of currency-test.csv

Where in the code that was working fine did you add code that adam suggested in order to cause it to crash?

Hi ozo,
Finally sorted, I think that the problem has been that opening csv files with the standard commands has problems. I have simplified the script but still using a csv module Text::CSV_XS. I have added the finished script below, it works a treat!
----------------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w
#use strict
#use warnings
use CGI::Carp 'fatalsToBrowser';

use LWP::Simple;
use Text::CSV_XS;

# Output a header. We must do this!
print "Content-type: text/html\n\n";


$string = "currency_id,currency_symbol,currency_rate,currency_image";
$string_one = "GBP,£,1,uk.png";
$string_two = "USD,\$,";
$string_three = ",us.png";

#-------------------------------------------------------------------------------
$input = get('http://www.currency server url here')
     or die("Unable to access exchange rate server");

$codefound = index($input,"GBP-DOLLAR");
if ($codefound <0)   {exit;}  # Exit if not a valid exchange symbol.
$codefound +=35;      #This may need changing, it does not take into account length of different month names
$exchange_rate = substr($input,$codefound,6);
$spot_rate = $exchange_rate;

$exchange_rate = $exchange_rate * 107/100;      #Change percentage on this line.
$exchange_rate = sprintf "%.2f",$exchange_rate; # Rounds to 2 decimal places.

#----------------------------------------------------#
my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ });

  open my $csv_fh, ">", "currency-test.csv" or die "currency-test.csv: $!";
     print $csv_fh "$string\n","$string_one\n","$string_two","$exchange_rate","$string_three\n";
   close $csv_fh or die "currency-test.csv: $!";

# End of script!
----------------------------------------------------------------------------------------------------------------------

I apreciate all your help with this and feel that you should have some points for the time you put into it, what do you think, I won't click "accept solution" until I have received your thoughts on the matter, should I give some points to adam?

Regards,

Brian

Hi ozo,
What is returned from the currency source server is the html source code including source details for the following display:

Wed May 27 2009 09:30 GMT
GBP-DOLLAR      1.6004
GBP-EURO      1.1460
FTSE      4417.62 (+5.90)
DOW      8473.49 (+196.17)
NASDAQ      1750.43 (+71.80)

I realize my solution is not very elegant but it does work and I need to move on to other things, I really only need to write a perl script once in a blue moon!

I take your point about how I am not really using "my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ }); or use Text::CSV_XS;" but it was the only way I could find that would open the file correctly. Trying to open the csv file with the standard open function as suggested by adam just failed, it would not work no matter what I tried.

I tried to make more use of my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ }); or use Text::CSV_XS to format and join the different strings but it is very difficult to use, it does not support new lines unless it is set to binary and then there was a problem with it trying to print the binary code for the new line character, it would not print the pound sign and so on.

What I now have works!!

Regards,

Brian