INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to print string with comma in single column of csv

How to print string with comma in single column of csv

How to print string with comma in single column of csv

(OP)
I am new to Perl. I am processing thousands of autosys job logs and printing the information in CSV file. First column captures Job Name, second: start date and Time, Third: End date and Time, and last fourth : Command (Command the Autosys job is running).

Command can have multiple commas in it, which is distorting output CSV file. I want entire command to be printed in column four.

I am unable to use Text::CSV, getting below error,
Can't locate Text/CSV.pm in @INC (@INC contains: E:/vendor_apps/Perl/lib E:/vendor_apps/Perl/site/lib .) at AutosysLogInfo.pl line 18.
BEGIN failed--compilation aborted at AutosysLogInfo.pl line 18.

Can anyone help me with this? Is there alternative way which can be done without using additional module.
can you help me with sample code?

RE: How to print string with comma in single column of csv

You can do this programmatically:
If the line of your log contains more the four parts, separated by comma, i.e. the command consists of more parts separated by comma, then you will take them all from the fourth util the last part into the 4. CSV column.

You didn't provide any example data, so here's an example:
if the log is something like this

CODE

111111, 2016-05-13 7:52:55, 2016-05-13 8:01:10, foo
222222, 2016-05-13 8:00:01, 2016-05-13 8:05:20, bar, baz
333333, 2016-05-13 9:25:30, 2016-05-13 9:10:30, spam, eggs, foobar 

and you need to get a CSV like this

CODE

ob Nr; Begin Time; End Time; Command
111111; 2016-05-13 7:52:55; 2016-05-13 8:01:10; foo
222222; 2016-05-13 8:00:01; 2016-05-13 8:05:20; bar, baz
333333; 2016-05-13 9:25:30; 2016-05-13 9:10:30; spam, eggs, foobar 

then it could be done like in this script

mayur09.pl

CODE

use strict;
use warnings;

my $DBG_INFO = 1;

my $csv_header = "Job Nr; Begin Time; End Time; Command";
print "$csv_header\n";

my $line;
while($line = <DATA> ) {
  chomp($line);
  &process_line();
}

sub  trim { 
  my $s = shift; 
  $s =~ s/^\s+|\s+$//g; 
  return $s
}

sub process_line {
  print "$line\n" if $DBG_INFO;
  my @line_array = split(/,/, $line);
  my $nr_fields = $#line_array + 1;
  print "numner of fields: $nr_fields\n" if $DBG_INFO;
  my ($job, $beg, $end, $cmd);
  $job = trim($line_array[0]);
  $beg = trim($line_array[1]);
  $end = trim($line_array[2]);
  if ($#line_array > 3) {
    $cmd = trim(join(',', @line_array[3..$#line_array]));
  }
  else {
    $cmd = trim($line_array[3]);
  }

  my $csv_line  = "$job; $beg; $end; $cmd";
  print "$csv_line\n";

}

# your log data
__DATA__
111111, 2016-05-13 7:52:55, 2016-05-13 8:01:10, foo
222222, 2016-05-13 8:00:01, 2016-05-13 8:05:20, bar, baz
333333, 2016-05-13 9:25:30, 2016-05-13 9:10:30, spam, eggs, foobar  

RE: How to print string with comma in single column of csv

I used an semicolon as field separator in the CSV file. If the field separator in your CSV should be comma, then you need to enclose the command which contains commas into ".." e.g.:

CODE

ob Nr, Begin Time, End Time, Command
111111, 2016-05-13 7:52:55, 2016-05-13 8:01:10, foo
222222, 2016-05-13 8:00:01, 2016-05-13 8:05:20, "bar, baz"
333333, 2016-05-13 9:25:30, 2016-05-13 9:10:30, "spam, eggs, foobar" 

RE: How to print string with comma in single column of csv

(OP)
while( <RKSAutosysJobLog> )
{
chomp;
$CurrentLine = $_;

if( $CurrentLine =~ /^\* Started at\s+(.+)$/ )
{
my(@mySplitWords1) = split(/ /,$1);
$StartDate = $mySplitWords1[0];
$StartTime = $mySplitWords1[1];
}

if( $CurrentLine =~ /^AUTO_JOB_NAME\=(.+)$/ )
{
$AutosysJobName = $1;

}

if( $CurrentLine =~ /^\* Ended at\s+(.+)$/ )
{
my(@mySplitWords2) = split(/ /,$1);
$EndDate = $mySplitWords2[0];
$EndTime = $mySplitWords2[1];
}

if( $CurrentLine =~ /^\* EXIT\=(.+)$/ )
{
$ExitCode = $1;
}

if( $CurrentLine =~ /^\* COMMAND\=(.+)$/ )
{
$Command = "\"" . $1 . "\"";
}

if( $CurrentLine =~ /^\* End Task \*/ )
{
$myRecord = "$FileName,";
$myRecord = $myRecord . "$AutosysJobName,";
$myRecord = $myRecord . "$ExitCode,";
$myRecord = $myRecord . "$StartDate,";
$myRecord = $myRecord . "$StartTime,";
$myRecord = $myRecord . "$EndDate,";
$myRecord = $myRecord . "$EndTime,";
$myRecord = $myRecord . "$Command\n";
push @AllJobRunDetails, $myRecord;
}
}

and Finally I am printing @AllJobRunDetails in CSV File
Here $Command can hold string like, "%RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s "cmd.exe,cmd,perl.exe,perl,<Unknown>" -k 1 -i 180 -u PR2AUTOSYS"
I am Using a comma as a field seperator in CSV.
so my record in CSV file is comming as

Header with 7 columns as below,
JobName, ExitCode, StartDate, StartTime, EndDate, EndTime, Command

Record with 11 Columns as below,
inarks27_uxrks100_axa_reboot_gdcpw4752, 0, 5/14/2016, 23:46:11, 5/14/2016, 23:46:12, %RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s cmd.exe, cmd, perl.exe, perl, <Unknown>" -k 1 -i 180 -u PR2AUTOSYS"

Would like to highlight one more thing here, if you compair string in $Command and Printed record in CSV file,
Two " are missing. First just before %RKS and second just before cmd.exe

RE: How to print string with comma in single column of csv

(OP)
Hi mikrom, with some investigation I found the problem but still unable to find solution.

My actual Command is: %RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s "cmd.exe,cmd,perl.exe,perl,<Unknown>" -k 1 -i 180 -u PR2AUTOSYS

As I am using comma as a field separator in CSV, I need to Put this string in double quotes (like "..")

so My New Command is : "%RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s "cmd.exe,cmd,perl.exe,perl,<Unknown>" -k 1 -i 180 -u PR2AUTOSYS"

but the original string has "cmd.exe,cmd,perl.exe,perl,<Unknown>" (i.e. double quotes in it), so while printing CSV I guess it is considering "%RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s " as first and cmd.exe,cmd,perl.exe,perl,<Unknown> as second and " -k 1 -i 180 -u PR2AUTOSYS" as a third string or something like that.

If I modify original command by replacing ( " )double quotes by ( ' ) Single quote
My Modified Command is: %RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s 'cmd.exe,cmd,perl.exe,perl,<Unknown>' -k 1 -i 180 -u PR2AUTOSYS

and then put the string in double quotes (like "..") and print, in a single column I am getting command as ,

%RKS_PERL_EXE% %RKS_PERL_COMMON_SCRIPT_LOCATION%\RksBoxReBoot.pl -e RKS_BOX_NTPROCINFO_EXE -s 'cmd.exe,cmd,perl.exe,perl,<Unknown>' -k 1 -i 180 -u PR2AUTOSYS

so keeping entire command in single column is resolved, but still this is not my actual command Because I have replaced ( " )double quotes by ( ' ) Single quote.

Hope you are getting me...!
see if you can suggest something to me.



RE: How to print string with comma in single column of csv

Could you please provide some actual sample log data (with any sensitive information scrubbed) and your expected results for any of that data?

Otherwise we're just making guesses. Thanks!

RE: How to print string with comma in single column of csv

Or maybe other standard is to use quotes for string delimiting.
For example like this

CODE

111, 2016-05-13 7:52:55, 2016-05-13 8:01:10, foo
222, 2016-05-13 8:00:01, 2016-05-13 8:05:20, 'bar, baz'
333, 2016-05-13 9:25:30, 2016-05-13 9:10:30, 'spam, eggs, foobar -"a b c"' 

RE: How to print string with comma in single column of csv

I could import the CSV-format I posted above (with field_delimiter = comma, string delimiter = quote) without problems into DB2 table using standard import command.

But it doesn't work with Excel. If I want to import it into Excel, only this format (with field_delimiter = semicolon) works for me

CODE

111; 2016-05-13 7:52:55; 2016-05-13 8:01:10; foo
222; 2016-05-13 8:00:01; 2016-05-13 8:05:20; bar, baz
333; 2016-05-13 9:25:30; 2016-05-13 9:10:30; spam, eggs, foobar - "a b c" 
It's strange.

Why you coudn't use semicolon instead of comma as field separator ?
/IMO Comma isn't good choice, because it will be used as decimal point or thousands separator in numbers/

RE: How to print string with comma in single column of csv

(OP)
Mikron Thanks for suggestion, But how can I tell my script that I am using field_delimiter = semicolon, string delimiter = quote
I am very new to perl (One or two weeks), and using field_delimiter = comma, string delimiter = Double quotes till now.

What code needs to be included so that my script will start using semicolon as field_delimiter and quote as string delimiter.



RE: How to print string with comma in single column of csv

Quote (Mayur09)


But how can I tell my script that I am using field_delimiter = semicolon

I guess you only have to change this part in your script above

CODE

$myRecord = "$FileName,";
 $myRecord = $myRecord . "$AutosysJobName,";
 $myRecord = $myRecord . "$ExitCode,";
 $myRecord = $myRecord . "$StartDate,";
 $myRecord = $myRecord . "$StartTime,";
 $myRecord = $myRecord . "$EndDate,";
 $myRecord = $myRecord . "$EndTime,";
 $myRecord = $myRecord . "$Command\n";
 push @AllJobRunDetails, $myRecord; 
to

CODE

$myRecord = "$FileName;";
 $myRecord = $myRecord . "$AutosysJobName;";
 $myRecord = $myRecord . "$ExitCode;";
 $myRecord = $myRecord . "$StartDate;";
 $myRecord = $myRecord . "$StartTime;";
 $myRecord = $myRecord . "$EndDate;";
 $myRecord = $myRecord . "$EndTime;";
 $myRecord = $myRecord . "$Command\n";
 push @AllJobRunDetails, $myRecord; 

RE: How to print string with comma in single column of csv

(OP)
No, It didn’t work...!

RE: How to print string with comma in single column of csv

Then I don't know, you need to find out where it should be changed in your script.

RE: How to print string with comma in single column of csv

(OP)
One more small problem with handling Commas in CSV.

My variable $a contains a string : 31,456987321
I want to retain this comma and print this string as it is in single column of CSV. So have enclosed the $a in double quotes

i.e.,

$a = "\"". $a . "\"";
print CSV_FILE_handler $a;
close CSV_FILE_handler;

But In out Put CSV file the value is coming as 31,456,987,321.
456987321 This is a whole string for me, but is being considered as number and getting separated with comma.

Is there any way, I can avoid this and get 31,456987321 in CSV file as it is.

RE: How to print string with comma in single column of csv

Before the first line $a = "\"". $a . "\""; you must be doing something that puts your string in numeric context, and, voila, your string is changed by perl (and there is no simple way to come back).
You should inspect your code before that point to see where this numeric context is created, and, if you can't avoid that, save the string before that point into a separate variable.

http://www.xcalcs.com : Online engineering calculations
http://www.megamag.it : Magnetic brakes for fun rides
http://www.levitans.com : Air bearing pads

RE: How to print string with comma in single column of csv

(OP)
Before ,$a = "\"". $a . "\""; Now I ahve added two line of code, which is making my string lil diffrent but in better shape.

my(@mySplitWords) = split (/,/, $a);
$a = join(", ",@mySplitWords);
$a = "\"". $a . "\"";
print CSV_FILE_handler $a;
close CSV_FILE_handler;

In out Put CSV file the value is coming as 31, 456987321

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close