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 SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help creating CSV file based on varying value conditions 1

Status
Not open for further replies.
melsterTEK

melsterTEK

Programmer
Feb 6, 2007
27
0
0
US
I am trying to create a csv file, the catch is I have to do some data validation and/or pattern matching of some sort to create the final file.

I need to go through the given column where some columns have data values that are separated by commas “,” like so:

Requirements:
-Validate the number of coma separated values
-If validation fails, generate a valid entry with blank coma separated values

Example;
S_YSVACCEPT = "4.12,YSV5,7830" OK, 3 comma separated values
S_YSVACCEPT = "4.12,YSV5," OK, 3 comma separated values

S_YSVACCEPT = "4.12,YSV5" Bad, only 2 comma separated, you should return ", ,"
S_YSVACCEPT = "foo,faa,blah,kaa" Bad, 4 comma separated value, you should return ", ,"
S_YSVACCEPT = "0" Bad, only 1 comma separated value, you should return ", ,"

Can someone help me with this logic? Thanks.
 
Sort by date Sort by votes
I will help but you have to post what code you have so far, and let us know if this is some type of school/course work.

But in general, use the split() function to return an array and check the length of the array.

- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Hi Kevin,
I work as a dba, just started learning perl and this language is very nifty!

Here is my code...right now I have it set up to just query the recs and print them out, but no data validation (which I need help with)

Code:
#!/usr/bin/perl

# Let's define our tools
use DBI;
use Time::HiRes;

#
# ### Set up environment for running on WHPROD
#
#$ENV{LD_LIBRARY_PATH} = "/lib:/usr/lib:/opt/app/oracle/product/8.1.6/lib:/opt/app/oracle/product/8.1.6/network/lib:/usr/local/lib";
$ENV{LD_LIBRARY_PATH} = "/lib:/usr/lib:/app/oracle/product/9.2.0/lib:/app/oracle/product/9.2.0/network/lib:/usr/local/lib";
$ENV{ORACLE_HOME} = "/app/oracle/product/9.2.0";
$ENV{TNS_ADMIN} = "/app/oracle/product/9.2.0/network/admin";
$ENV{ORA_NLS33} = "/app/oracle/product/9.2.0/ocommon/nls/admin/data";

$|=1;

# Start time
my$beg_time = Time::HiRes::time();

my $dbh = DBI->connect(
"dbi:Oracle:racdb1",
"mm_owner",
"mm_owner",
{AutoCommit => 0,
RaiseError => 1,
PrintError => 1}
) or die "Can't connect to the RACDB STAGING database: $DBI::errstr\n";


# Define our sql statment here
my $sql = "SELECT
S_EXTRAINFO3,
PER_ID,
S_EXTRAINFO1,
S_EXTRAINFO2,
S_YSVACCEPT,
S_YMJACCEPT,
S_YSUBACCEPT,
S_STATUSLIB,
S_STATUSPL
FROM
MM_MIGRATION_MAP";

# Prepare sql and get a statment handle
my $sth = $dbh->prepare( $sql );

if ( !defined $sth ) {
die "Cannot prepare statement: $DBI::errstr\n";
}

# Exucte the sql and get row count
$sth->execute;
my $row_count = $sth->rows;

# Process the dataset one row at a time
while (($S_EXTRAINFO3,
$PER_ID,
$S_EXTRAINFO1,
$S_EXTRAINFO2,
$S_YSVACCEPT,
$S_YMJACCEPT,
$S_YSUBACCEPT,
$S_STATUSLIB,
$S_STATUSPL ) = $sth->fetchrow_array()) {

# Let's print the columns with pipe seperation
print "$S_EXTRAINFO3 | $PER_ID | $S_EXTRAINFO1 | $S_EXTRAINFO2 | $S_YSVACCEPT | $S_YMJACCEPT | $S_YSUBACCEPT | $S_STATUSLIB | $S_STATUSPL\n";
}

# we done here, let's close the statement handle and disconnect from db
$sth->finish;
$dbh->disconnect();

my$end_time = Time::HiRes::time();
my$elapsed = sprintf("%.4f", $end_time - $beg_time);
print "Took " . $elapsed . " seconds to generate " . $row_count . " rows\n";
exit;
 
Upvote 0 Downvote
Code: 
[gray][i]# Process the dataset one row at a time[/i][/gray]
[olive][b]while[/b][/olive] [red]([/red][red]([/red][blue]$S_EXTRAINFO3[/blue][black],[/black]
              [blue]$PER_ID[/blue][black],[/black]
        [blue]$S_EXTRAINFO1[/blue][black],[/black]
        [blue]$S_EXTRAINFO2[/blue][black],[/black]
         [blue]$S_YSVACCEPT[/blue][black],[/black]
         [blue]$S_YMJACCEPT[/blue][black],[/black]
        [blue]$S_YSUBACCEPT[/blue][black],[/black]
         [blue]$S_STATUSLIB[/blue][black],[/black]
          [blue]$S_STATUSPL[/blue] [red])[/red] [black]=[/black] [blue]$sth[/blue][black]->[/black][maroon]fetchrow_array[/maroon][red]([/red][red])[/red][red])[/red] [red]{[/red]

   [gray][i]# Let's print the columns with pipe seperation[/i][/gray]
   [black][b]print[/b][/black] [red]"[/red][purple][blue]$S_EXTRAINFO3[/blue] | [blue]$PER_ID[/blue] | [blue]$S_EXTRAINFO1[/blue] | [blue]$S_EXTRAINFO2[/blue] | [blue]$S_YSVACCEPT[/blue] | [blue]$S_YMJACCEPT[/blue] | [blue]$S_YSUBACCEPT[/blue] | [blue]$S_STATUSLIB[/blue] | [blue]$S_STATUSPL[/blue][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
[red]}[/red]
[black][b]my[/b][/black] [blue]$commas[/blue] [black]=[/black] [blue]$S_YSVACCEPT[/blue] [black]=~[/black] [red]tr/[/red][purple],[/purple][red]/[/red][purple],[/purple][red]/[/red][red];[/red]
[black][b]print[/b][/black] [red]"[/red][purple][blue]$commas[/blue] in [purple][b]\$[/b][/purple]S_YSVACCEPT[purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]

from there you can decide what to do. Add commas, remove commas, etc.

- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Kevin,
So this piece you added?

my $commas = $S_YSVACCEPT =~ tr/,/,/;
print "$commas in \$S_YSVACCEPT\n";

Does this say replace first comma I come across with another comma?

Again, going back to my condition..
if S_YSVACCEPT (for bad data/conditions)

S_YSVACCEPT = "4.12,YSV5" ->Bad, only 2 comma separated, you should return ", ,"
S_YSVACCEPT = "foo,faa,blah,kaa" ->Bad, 4 comma separated value, you should return ", ,"
S_YSVACCEPT = "0" ->Bad, only 1 comma separated value, you should return ", ,"

Will having that last "," is essentially creating my csv file right?
 
Upvote 0 Downvote
this:

Code: 
my $commas = $S_YSVACCEPT =~ tr/,/,/;
print "$commas in \$S_YSVACCEPT\n";

is just a quick and dirty way of counting the commas in the variable. It counts them by replacing all the commas with commas and returns how many times it did it to the scalar $commas.

How you implement that or if you implement that into your script is hard to say. Do you just want to add commas if there are not enough or remove some if there are too many?
If there are too many what happens to the extra data? Like this example:

Code: 
S_YSVACCEPT = "foo,faa,blah,kaa" Bad, 4 comma separated value, you should return ", ,"

what should that line be converted to?

- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Since the column (S_YSVACCEPT) can only have (3)comma separated values or 2 ",," (commas) then return ",,"

sorry the example i showed you looked like i had a space ", ,"
where it really should be ",,"

I think the logic is count the number of commas for this field. In this case if its not equal to (2)commas, produce 2 commas ",,".

This has (3) commas so it bad data, replace with ",," (2):
S_YSVACCEPT = "foo,faa,blah,kaa" Bad, 4 comma separated value, you should return ", ,"

I hope I am not confusing you..This is also example for one of the columns. I have other columns I need to do for the same..but logic is the same.

Thanks.

 
Upvote 0 Downvote
so are you saying this:

"foo,faa,blah,kaa";

should literally be changed to:

",,"

or changed to this:

"foo,faa,blah";












- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
right..
should literall be changed to: ",,"

I don't have to delete that extra value, i just need to replace it. Basically, return the correct # of comma separated blank values..

So "4.12,YSV5" equates to ",,"
And "0" also equates to ",,"
 
Upvote 0 Downvote
Also Kevin,
If I'm doing this for more than 1 column, I will need to join those values, separated by "," thus creating a csv file. Maybe it would be visually easier to look at if I use \t or "|". Can you also show me how to tie it together? Thanks again!
 
Upvote 0 Downvote
this might work:

Code: 
[gray][i]# Process the dataset one row at a time[/i][/gray]
[black][b]my[/b][/black] [blue]@results[/blue] [black]=[/black] [red]([/red][red])[/red][red];[/red]
[black][b]while[/b][/black] [red]([/red][red]([/red][blue]$S_EXTRAINFO3[/blue][black],[/black]
              [blue]$PER_ID[/blue][black],[/black]
        [blue]$S_EXTRAINFO1[/blue][black],[/black]
        [blue]$S_EXTRAINFO2[/blue][black],[/black]
         [blue]$S_YSVACCEPT[/blue][black],[/black]
         [blue]$S_YMJACCEPT[/blue][black],[/black]
        [blue]$S_YSUBACCEPT[/blue][black],[/black]
         [blue]$S_STATUSLIB[/blue][black],[/black]
          [blue]$S_STATUSPL[/blue] [red])[/red] [black]=[/black] [blue]$sth[/blue][black]->[/black][maroon]fetchrow_array[/maroon][red]([/red][red])[/red][red])[/red] [red]{[/red]

   [gray][i]# Let's print the columns with pipe seperation[/i][/gray]
   [black][b]print[/b][/black] [red]"[/red][purple][blue]$S_EXTRAINFO3[/blue] | [blue]$PER_ID[/blue] | [blue]$S_EXTRAINFO1[/blue] | [blue]$S_EXTRAINFO2[/blue] | [blue]$S_YSVACCEPT[/blue] | [blue]$S_YMJACCEPT[/blue] | [blue]$S_YSUBACCEPT[/blue] | [blue]$S_STATUSLIB[/blue] | [blue]$S_STATUSPL[/blue][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
   [black][b]my[/b][/black] [blue]$commas[/blue] [black]=[/black] [blue]$S_YSVACCEPT[/blue] [black]=~[/black] [red]tr/[/red][purple],[/purple][red]/[/red][purple],[/purple][red]/[/red][red];[/red]
   [black][b]if[/b][/black] [red]([/red][blue]$commas[/blue] [black]<[/black] [fuchsia]2[/fuchsia] [black]||[/black] [blue]$commas[/blue] [black]>[/black] [fuchsia]2[/fuchsia][red])[/red] [red]{[/red]
      [blue]$S_YSVACCEPT[/blue] [black]=[/black] [red]'[/red][purple]",,"[/purple][red]'[/red][red];[/red]
   [red]}[/red]
   [black][b]push[/b][/black] [blue]@results[/blue][black],[/black][red]"[/red][purple][blue]$S_EXTRAINFO3[/blue],[blue]$PER_ID[/blue],[blue]$S_EXTRAINFO1[/blue],[blue]$S_EXTRAINFO2[/blue],[blue]$S_YSVACCEPT[/blue],[blue]$S_YMJACCEPT[/blue],[blue]$S_YSUBACCEPT[/blue],[blue]$S_STATUSLIB[/blue],[blue]$S_STATUSPL[/blue][/purple][red]"[/red][red])[/red][red];[/red]
[red]}[/red]	
[black]now[/black] [black][b]do[/b][/black] [black]something[/black] [black]with[/black] [blue]@resuls[/blue][red];[/red]

- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
or more simply, instead of this:

Code: 
   my $commas = $S_YSVACCEPT =~ tr/,/,/;
   if ($commas < 2 || $commas > 2) {
      $S_YSVACCEPT = '",,"';
   }

like this:

Code: 
   my $commas = $S_YSVACCEPT =~ tr/,/,/;
   $S_YSVACCEPT = '",,"' if ($commas != 2);



- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
how about if $S_YSVACCEPT=0? how can i build that in?
 
Upvote 0 Downvote
If there are two commas the value is left as-is, if there is anything else it's changed to ",,".

- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
# Process the dataset one row at a time
while ((
$S_YSVACCEPT,
$S_YMJACCEPT
) = $sth->fetchrow_array()) {

# Let's print the columns with pipe seperation
print "$S_YSVACCEPT | $S_YMJACCEPT \n";
my $commas = $S_YSVACCEPT =~ tr/,/,/;
$S_YSVACCEPT = '",,"' if ($commas != 2);
}
push
@results,("$S_YSVACCEPT,$S_YMJACCEPT");

-I cut the query just for 2 cols for testing..

results:
20,YSV20,7832 | AFV,AFVForFree,,
79.01,YSV80,7838 | 0:0
0 | AFV,AFVForFree,7822,y
19.01,YSV20, | 0:0
0 | 0:0
0 | 0:0
0 | 0:0
99.01,YSV100,7834 | 0:0
20,YSV20, | 0:0
0 | 0:0
19.01,YSV20,7832 | 0:0
0 | 0:0
0 | 0:0
Took 0.1212 seconds to generate 0 rows

As you can see, i'm not able to handle 0's. What do you think? 0 should be:
,, | 0:0
 
Upvote 0 Downvote
I think the problem is you have the "push" line outside the 'while' loop:

Code: 
while ((
         $S_YSVACCEPT,
         $S_YMJACCEPT
         ) = $sth->fetchrow_array()) {

   # Let's print the columns with pipe seperation
   print "$S_YSVACCEPT | $S_YMJACCEPT \n";
   my $commas = $S_YSVACCEPT =~ tr/,/,/;
   $S_YSVACCEPT = '",,"' if ($commas != 2);
   [b]push @results,("$S_YSVACCEPT,$S_YMJACCEPT");[/b]
}[b]#<--- before end of 'while' loop[/b]


- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
kevin, same problems. is it perhaps because 0 is actually a "0" string? if so, how do we fix it?
 
Upvote 0 Downvote
this is troublesome, I'm trying to troubleshoot the case for having "0" as a value. When I try to evaluate it, I still can't manage to capture it.
=============
code so far:
=============
while ((
$S_YSVACCEPT,
$S_YMJACCEPT
) = $sth->fetchrow_array()) {

my $commas = $S_YSVACCEPT =~ tr/,/,/;
if ($commas !=2) {
$S_YSVACCEPT = '",,"';
} elsif ("$S_YSVACCEPT" eq "0") {
$S_YSVACCEPT = '",,"'
};
push @results,("$S_YSVACCEPT,$S_YMJACCEPT");
}
$sth->finish;
$dbh->disconnect();

=========
results:
=========
0 | 0:0
0 | AFV,AFVForFree,7822,y
0 | 0:0
0 | 0:0
0 | 0:0
0 | 0:0
0 | 0:0
0 | 0:0
 
Upvote 0 Downvote
the code works. Using the sample data you posted:

Code: 
while (<DATA>) {
   chomp;
   ($S_YSVACCEPT,$S_YMJACCEPT) =split(/\|/);
   # Let's print the columns with pipe seperation
   #print "$S_YSVACCEPT | $S_YMJACCEPT\n";
   my $commas = ($S_YSVACCEPT =~ tr/,/,/);
   $S_YSVACCEPT = '",,"' if ($commas != 2);
   push @results,("$S_YSVACCEPT,$S_YMJACCEPT");
}
print "$_\n" for @results;[b]#<-- here is where the new data is[/b]


__DATA__
20,YSV20,7832 | AFV,AFVForFree,,
79.01,YSV80,7838 | 0:0
0 | AFV,AFVForFree,7822,y
19.01,YSV20, | 0:0
0 | 0:0
0 | 0:0
0 | 0:0
99.01,YSV100,7834 | 0:0
20,YSV20, | 0:0
0 | 0:0
19.01,YSV20,7832 | 0:0
0 | 0:0
0 | 0:0

the output is:

Code: 
20,YSV20,7832 , AFV,AFVForFree,,
79.01,YSV80,7838 , 0:0
",,", AFV,AFVForFree,7822,y
19.01,YSV20, , 0:0
",,", 0:0
",,", 0:0
",,", 0:0
99.01,YSV100,7834 , 0:0
20,YSV20, , 0:0
",,", 0:0
19.01,YSV20,7832 , 0:0
",,", 0:0
",,", 0:0


are you checking the @results array?




- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
Great! Thanks Kevin. I just did the

print "$_\n" for @results;

=========
Results:
=========
",,",0:0
",,",AFV,AFVForFree,7822,y
",,",0:0
",,",0:0
",,",0:0
",,",0:0
",,",0:0
",,",0:0

Is there a way to strip off the double-quotes?
 
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
110
mikrom
mikrom
rob54321
  • Question
Perl how to read line from file with interpolation 1
Replies
6
Views
173
rob54321
rob54321
Bob6739
  • Locked
  • Question
Perl Beginner - Need Help
Replies
6
Views
100
feherke
feherke
Ahven
  • Locked
  • Question
Compiling Perl 5.36.0 fails on OS X 10.5.8 PowerPC
Replies
1
Views
62
mikrom
mikrom
Anurag Sinha
  • Locked
  • Question
calling perl script on click of the button
Replies
0
Views
67
Anurag Sinha
Anurag Sinha

Part and Inventory Search

Sponsor

Back
Top