Text::CSV_XS Trying to find empty field

[Pam]

Hello:

I was wondering if anyone could answer this question for me. I am
currently trying to pasre a comma sepearted file to search for empty
fields in a particluar column. I decided to parse using the
Text::CSV:XS
to check for this information since I am already parsing the text file.
The
text file loks like the one below.

"LIBkk03527","","FMOM198:JAVA MIDP
2.0_Quality_OPERATOR_5","N","2","1","", and etc.

I want to check for a ("",) in a particular column and then write
certain data to that column,
lets say column 11. The "", tell me that that field is empty, I am
already parsing the file but just to write and format it in an xls
format.

I have tried to check for empty cell such as with undef with the
paser module
I was able to find undef data but not able to do the manipulation I
needed to do, so I thought
parsing it this way would be my last hope. Can anyone help me!

This is what I am currently doing:
my $filename ='CCB.txt';

open(FILE,">$filename") || die("Cannot Open File $filename : $!" );
print FILE $query_result->content;
print "File open ";

close (FILE);




# Open the Comma Separated Variable file
open (CSVFILE, $filename) or die "$filename: $!";


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




my $format3 = $workbook->add_format();
$format3->set_text_wrap();
$format3->set_border();
$format3->set_bottom();
$format3->set_top();
$format3->set_left();
$format3->set_right();




# Create a new CSV parsing object
my $csv = Text::CSV_XS->new;

# Row and column are zero indexed
my $row = 0;
my $total;
my $count;

while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;

my $col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token, $format3);
$col++;
}
$row++;
if ($row > 1){
$count = $count + 1;

$total = $count;

}

}
else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}

}
print "Adding sheet1\n";


Thanks,
Pam

 

[J. Gleixner]

Hello:

I was wondering if anyone could answer this question for me. I am
currently trying to pasre a comma sepearted file to search for empty
fields in a particluar column. I decided to parse using the
Text::CSV:XS
Text::CSV_XS

to check for this information since I am already parsing the text file.
The text file loks like the one below.

"LIBkk03527","","FMOM198:JAVA MIDP
2.0_Quality_OPERATOR_5","N","2","1","", and etc.

I want to check for a ("",) in a particular column and then write
certain data to that column,
lets say column 11. The "", tell me that that field is empty, I am
already parsing the file but just to write and format it in an xls
format.

I have tried to check for empty cell such as with undef with the
paser module

It's not undefined, it's an empty string.

perldoc -f defined

I was able to find undef data but not able to do the manipulation I
needed to do, so I thought
parsing it this way would be my last hope. Can anyone help me!

Please, simply post a short script that everyone can run.

The problem/question you have is with Text::CSV_XS, it doesn't seem to
be with Spreadsheet::WriteExcel, so we don't need/want to see anything
that uses it.

Based on your subject, it seems to be about trying to test for that
empty column/token. Providing an example would be quite short. e.g.:

use Text::CSV_XS;
my $data = '"LIBkk03527","","FMOM198:JAVA
MIDP2.0_Quality_OPERATOR_5","N","2","1",""';
my $csv = Text::CSV_XS->new();
if ($csv->parse($data)) {
my @Fld = $csv->fields;
foreach my $token (@Fld) {
if( $token eq '' ) { print "no value for token\n"; }
else { print "token = $token\n"; }
}
}

perl ./tsttok.pl

token = LIBkk03527
no value for token
token = FMOM198:JAVA MIDP2.0_Quality_OPERATOR_5
token = N
token = 2
token = 1
no value for token

 

[Pam]

It's not undefined, it's an empty string.

perldoc -f defined


Please, simply post a short script that everyone can run.

The problem/question you have is with Text::CSV_XS, it doesn't seem to
be with Spreadsheet::WriteExcel, so we don't need/want to see anything
that uses it.

Based on your subject, it seems to be about trying to test for that
empty column/token. Providing an example would be quite short. e.g.:

use Text::CSV_XS;
my $data = '"LIBkk03527","","FMOM198:JAVA
MIDP2.0_Quality_OPERATOR_5","N","2","1",""';
my $csv = Text::CSV_XS->new();
if ($csv->parse($data)) {
my @Fld = $csv->fields;
foreach my $token (@Fld) {
if( $token eq '' ) { print "no value for token\n"; }
else { print "token = $token\n"; }
}
}

token = LIBkk03527
no value for token
token = FMOM198:JAVA MIDP2.0_Quality_OPERATOR_5
token = N
token = 2
token = 1
no value for token

Hello:

Thank you that did work!!! The reason I included the Spreadsheet
snipet is
because I write to the spreadsheet. The last problem I am having is
once I determine
that there is no value I write to spreadsheet and format it with a
highlighted color.
Right now it ignores my writing to the spread sheet it finds the column
12 and lets me
know there is no value but it won't allow me to do any writing. Can't
figure out why.
Can you see anything I am doing wrong. I can't find the problem.

# Create a new CSV parsing object
my $csv = Text::CSV_XS->new();


# Row and column are zero indexed
my $row = 0;
my $total;
my $count = 0;


while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;

my $col = 0;


foreach my $token (@Fld) {
$worksheet->write($row, $col, $token, $format3);

$col++;
print "This is the column", $col, "\n";

if (($col == 12) && ($token eq '' )) {
print "No value for token\n";

$worksheet->write($row, $col, "3G_Platform",
$format2);

print "Is it SETTING WRITE\n";
}

else{
print "token = $token\n";

}

}
$row++;
print "This is row", $row, "\n";

if ($row > 1){
$count = $count + 1;

$total = $count;
print"Getting the total\n";
}

}
else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}

}
print "Adding sheet1\n";


Thank You So Much
Pam

 

[J. Gleixner]

Thank you that did work!!!

That's good to hear. You're welcome.

The reason I included the Spreadsheet
snipet is because I write to the spreadsheet.

I understand, however the issue was how to test the value, which
has nothing to do with the spreadsheet, and by including all of
that extra code it takes longer for anyone to debug.

The last problem I am having is
once I determine
that there is no value I write to spreadsheet and format it with a
highlighted color.
Right now it ignores my writing to the spread sheet it finds the column
12 and lets me
know there is no value but it won't allow me to do any writing. Can't
figure out why.
Can you see anything I am doing wrong. I can't find the problem.

It looks like your column is off by one.

# Create a new CSV parsing object
my $csv = Text::CSV_XS->new();


# Row and column are zero indexed
my $row = 0;

Again.. Please narrow your code down to something that can be a simple
so anyone can run it to see the issue.

e.g. $total isn't part of the problem, CSVFILE isn't defined, etc.

Sure, anyone could go back and cut and paste your previous posts,
but that's a big waste of everyone's time.

my $total;
my $count = 0;


while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;

my $col = 0;


foreach my $token (@Fld) {
$worksheet->write($row, $col, $token, $format3);

Here, if $col was 11 and token was '', it'd write the empty string to
column 11.

$col++;

Now $col would be 12.

print "This is the column", $col, "\n";

if (($col == 12) && ($token eq '' )) {
print "No value for token\n";

$worksheet->write($row, $col, "3G_Platform",
$format2);

Then it'd write the '3G_Platform' to column 12, if $token was ''.

print "Is it SETTING WRITE\n";
}

Now, the next iteration, $col would still be 12, it'd over-write the
previous value, '3G_Platform', with whatever $token contains,
and continue with the rest of the columns.

I'd suggest moving $col++ to the end of the for loop and adding in a
print, instead of a write, so you can see the values on STDOUT, as it's
running. Also, to make it easier to debug, change your input to be just
one line, containing the data you're testing for, that way you'll have
much less data to view on the screen.

# Assuming format2 is correct.. if not, print it out also.
#use Data:umper;
#
#print "format2=", Dumper( $format2 ), "\n";
for my $token( @Fld )
{
$token = '3G_Platform' if $col == 12 and $token eq '';
#$worksheet->write($row, $col, $token, $format2);
print "write( $row, $col, $token )\n";
$col++;
}

Once the print shows the correct data for the particular row and column,
you should be able to use the write().

When you're debugging and/or asking for help, try to simplify the
problem and narrow down the issue. Are the values I'm expecting being
written to the correct row and column? Am I testing for the values
correctly? etc... Once the variables are correctly being set, then you
add in writing it as an XLS.

perldoc -q debug