Altering a CSV file with perl

Could someone please help me with the following?

I have a perl file that retrieves the current currency dollar pound rate from an online source and saves it in a variable called $exchange_rate.
From within this same perl file I then want to open a CSV file called "currency.csv" it just contains the following data:

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

I want to then replace the dollar amount (in this example it is 1.93) with the contents of the variable $exchange_rate and then save and close the file.

I would be grateful if someone could give me a bit of perl that would do this and please let me also know which modules need to be included.

Regards,

Brian
violinmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
{local $^I=".bak"; local @ARGV=("currency.csv");
 while( <> ){
    s/\$,[\d.]+,/\$,$exchange_rate,/;
    print;
 }
}
0
Adam314Commented:
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);

Open in new window

0
ozoCommented:
USD,$,$exchange_rate,us.png\n";
should be
USD,\$,$exchange_rate,us.png\n";
(unless $,='$,')
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Adam314Commented:
Thanks ozo... copied/pasted
0
violinmanAuthor Commented:
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
0
violinmanAuthor Commented:
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
0
Adam314Commented:
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);

Open in new window

0
violinmanAuthor Commented:
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
0
ozoCommented:
#!/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?
0
ozoCommented:
(unless $,='$,')
was meant to be a comment, not code
0
ozoCommented:
$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,
0
violinmanAuthor Commented:
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
 

0
ozoCommented:
$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
0
ozoCommented:
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
0
ozoCommented:
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
0
violinmanAuthor Commented:
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
0
ozoCommented:
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.
0
violinmanAuthor Commented:
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
0
ozoCommented:
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?
0
violinmanAuthor Commented:
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
0
violinmanAuthor Commented:
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
0
ozoCommented:
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
0
ozoCommented:
Where in the code that was working fine did you add code that adam suggested in order to cause it to crash?
0
violinmanAuthor Commented:
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
0
ozoCommented:
You don't seem to be using $csv, so there seems to be no reason to do my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ }); or use Text::CSV_XS;
and I'm still wondering what $input looks like.  
there may be a better way to parse it than using index and substr
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
violinmanAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.