Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to print string with comma in single column of csv 1

Status
Not open for further replies.
Mayur09

Mayur09

Programmer
May 13, 2016
12
US
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?
 
Sort by date Sort by votes
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: 
[COLOR=#804040][b]use strict[/b][/color];
[COLOR=#804040][b]use warnings[/b][/color];

[COLOR=#804040][b]my[/b][/color] [COLOR=#008080]$DBG_INFO[/color] = [COLOR=#ff00ff]1[/color];

[COLOR=#804040][b]my[/b][/color] [COLOR=#008080]$csv_header[/color] = [COLOR=#ff00ff]"[/color][COLOR=#ff00ff]Job Nr; Begin Time; End Time; Command[/color][COLOR=#ff00ff]"[/color];
[COLOR=#804040][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#008080]$csv_header[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];

[COLOR=#804040][b]my[/b][/color] [COLOR=#008080]$line[/color];
[COLOR=#804040][b]while[/b][/color]([COLOR=#008080]$line[/color] = [COLOR=#008080]<DATA>[/color] ) {
  [COLOR=#804040][b]chomp[/b][/color]([COLOR=#008080]$line[/color]);
  [COLOR=#008080]&process_line[/color]();
}

[COLOR=#804040][b]sub  [/b][/color][COLOR=#008080]trim [/color]{ 
  [COLOR=#804040][b]my[/b][/color] [COLOR=#008080]$s[/color] = [COLOR=#804040][b]shift[/b][/color]; 
  [COLOR=#008080]$s[/color] =~ [COLOR=#804040][b]s/[/b][/color][COLOR=#ff00ff]^[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]|[/color][COLOR=#6a5acd]\s[/color][COLOR=#6a5acd]+[/color][COLOR=#ff00ff]$[/color][COLOR=#804040][b]//g[/b][/color]; 
  [COLOR=#804040][b]return[/b][/color] [COLOR=#008080]$s[/color]
}

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

  [COLOR=#804040][b]my[/b][/color] [COLOR=#008080]$csv_line[/color]  = [COLOR=#ff00ff]"[/color][COLOR=#008080]$job[/color][COLOR=#ff00ff]; [/color][COLOR=#008080]$beg[/color][COLOR=#ff00ff]; [/color][COLOR=#008080]$end[/color][COLOR=#ff00ff]; [/color][COLOR=#008080]$cmd[/color][COLOR=#ff00ff]"[/color];
  [COLOR=#804040][b]print[/b][/color] [COLOR=#ff00ff]"[/color][COLOR=#008080]$csv_line[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color];

}

[COLOR=#0000ff]# your log data[/color]
[COLOR=#0000ff]__DATA__[/color]
[COLOR=#0000ff]111111, 2016-05-13 7:52:55, 2016-05-13 8:01:10, foo[/color]
[COLOR=#0000ff]222222, 2016-05-13 8:00:01, 2016-05-13 8:05:20, bar, baz[/color]
[COLOR=#0000ff]333333, 2016-05-13 9:25:30, 2016-05-13 9:10:30, spam, eggs, foobar [/color]
 
Upvote 0 Downvote
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"
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.



 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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"'
 
Upvote 0 Downvote
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/
 
Upvote 0 Downvote
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.



 
Upvote 0 Downvote
Mayur09 said:
But how can I tell my script that I am using field_delimiter = semicolon
Click to expand...

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;
 
Upvote 0 Downvote
Then I don't know, you need to find out where it should be changed in your script.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Before the first line [tt]$a = "\"". $a . "\"";[/tt] 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.

: Online engineering calculations
: Magnetic brakes for fun rides
: Air bearing pads
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

newbie1238585
  • Locked
  • Question
Perl how to read string in csv file * as wildcard operator
Replies
2
Views
204
mikrom
mikrom
rob54321
  • Locked
  • Question
Perl how to read line from file with interpolation 1
Replies
6
Views
345
rob54321
rob54321
Dino_1000
  • Locked
  • Question
Use the perl debugger to dump all the data in a string variable
Replies
0
Views
153
Dino_1000
Dino_1000
ss_ham
  • Locked
  • Question
in Perl find call looking to exclude folder and ignore duplicate finds.
Replies
2
Views
198
ss_ham
ss_ham
Anurag Sinha
  • Locked
  • Question
calling perl script on click of the button
Replies
0
Views
137
Anurag Sinha
Anurag Sinha

Part and Inventory Search

Sponsor

Back
Top