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

"Hi ,

I am a QA engineer from bangalore working on perl,I came across a problem while generating report into a csv file where I am also printing the Excel formulas into it.

I have to print something like this 

print LOG",=EXACT(B24,D24)\n";

[download]

but what happens when i print this in the log.csv the comma takes D24 to the next field .

Is there a way to print the formula to a single cell.

I tried putting an escape sequence something like 

print LOG",=EXACT(B24\,D24)\n";

[download]

but still it gave me the same result.

Thanks and Regards

Pradeep.S";

20050120 Janitored by Corion: Added formatting

Replies are listed 'Best First'.
Re: Comma problem
by friedo (Prior) on Jan 20, 2005 at 13:23 UTC
    CSV files are somewhat more complex than they might initially appear. I reccomend using Text::CSV to generate your file instead of trying to do it directly.
[reply]
      Text::CSV will fail if you have any newlines embedded in your data. I strongly recommend using Text::CSV_XS which not only handles the newlines but is also much faster.
[reply]
Re: Comma problem
by borisz (Canon) on Jan 20, 2005 at 13:51 UTC
    You should use a CSV module, I suggest Text::xSV. Your particular problem is that you need to write 
      print LOG qq{,"=EXACT(B24,D24)"}, "\n";

    [download]
    but that is just a guess.
    Boris
[reply]
[d/l]
Re: Comma problem
by florg (Friar) on Jan 20, 2005 at 13:40 UTC

    The problem is that backslash is not the escape character. Try to put the whole field in which you want to embed commas into double quotes. Probably something like 

    print LOG ",\"=EXACT(B24,D24)\"\n";

    [download]

    In the long run you will have a lot less trouble though if you heed friedo's advice and choose a module to do the dirtwork.

[reply]
[d/l]
Re: Comma problem
by idsfa (Vicar) on Jan 20, 2005 at 16:13 UTC

    While I wholeheartedly agree that you should use a module to parse your CSV, I have to wonder if you wouldn't be better off using Spreadsheet::WriteExcel for your task.

        use Spreadsheet::WriteExcel;

    # Create a new Excel workbook
    my $workbook = Spreadsheet::WriteExcel->new('out.xls');

    # Add a worksheet
    $worksheet = $workbook->add_worksheet();
    $worksheet->write('A24', 'Label');
    $worksheet->write('B24', 'Value1');
    $worksheet->write('C24', 'Value2');
    $worksheet->write('D24', 'Value3');
    $worksheet->write('E24', '=EXACT(B24,D24)');

    [download]
    The intelligent reader will judge for himself. Without examining the facts fully and fairly, there is no way of knowing whether vox populi is really vox dei, or merely vox asinorum. -- Cyrus H. Gordon
[reply]
[d/l]
Re: Comma problem
by prad_intel (Monk) on Jan 22, 2005 at 11:14 UTC
    Hi Monks,

    Its great time surfing here and i am on this all my free time.

    For the comma problem , I found out a solution with which I am able to put formulas into CSV without using modules.

    print LOG ',"=EXACT(B24,D24)"';

    [download]

    just check this out if it works for you too

    Thanks and Regards

    Pradeep.S

    Intelligence is a constant in this world , Population is increasing

[reply]
[d/l]

Back to Seekers of Perl Wisdom