Parsing to insert single quotes

[Eric]

I have a .csv file with 1750 records to reformat from time to time which
look like:

Zenia,7.25,Trinity

So far, I'm now getting:

(1749,'Zamora,7.25,Yolo'),

The left-right parens and autoincrement working, but am
stuck trying to add a single quote to the end of the City
field and a single quote to the beginning of the County
field like:

(1749,'Zamora',7.25,'Yolo'),

It would be helpful to add a semi-colon to the END of the
very LAST line like:

(1750,'Zenia',7.25,'Trinity');

But I'm stumped on that one also.

Any help would be greatly appreciated.

Thank you.


===SCRIPT===
#!/usr/bin/perl -w
#
use strict;
my $line;
print "Enter File Name To Tweak: ";
$filename = <STDIN>;
print "Enter Output File Name: ";
$outfile = <STDIN>;
open (INFILE, "$filename");
$totalrecs = 0;
$increment = 1;
while ($line = <INFILE>) {
chomp($line);
&write2outfile;
$increment++;
}
close (INFILE); #close input file
print "Net Records: $totalrecs -1\n";

### SUBROUTINE TO PRINT TO ASCII .CSV FILE
sub write2outfile
{
open (FILE ,">>$outfile");
if ($totalrecs >0) {
print FILE "\($increment\,\'$line\'\)\,\n";
} else {
print FILE "INSERT INTO column1 (ID, taxcity, taxrate, taxcounty)
VALUES\n";
}
close (FILE);
$totalrecs++;
}
# END

[Eric]

Oh Oh...

I just discovered I am missing record #1 in the output file...my counter
approach is apparently flawed.

Sorry to have overlooked this in the initial post.

[Eric]

Here is my latest attempt, but still getting an uninitialized value in
concatenation <.> or string in the print $1, etc. line.

#!/usr/bin/perl -w
#
use strict;
my $line;
my $increment;
my $outfile;
my $filename;
my $totalrecs;
my $city;
my $tax;
my $county;
print "Enter File Name To Tweak: ";
$filename = <STDIN>;
print "Enter Output File Name: ";
$outfile = <STDIN>;
open (INFILE, "$filename");
$totalrecs = 0;
$increment = 1;
while ($line = <INFILE>) {
chomp($line);
&write2outfile;
$increment++;
}
close (INFILE); #close input file
print "Net Records: $totalrecs -1\n";

### SUBROUTINE TO PRINT TO ASCII .CSV FILE
sub write2outfile
{
open (FILE ,">>$outfile");
if ($totalrecs >0) {
$line =~ /([\s\w]+)\,([\d]+)\,([\s\w]+)/;
$city = $1;
$tax = $2;
$county = $3;
print FILE "\($increment\,\'$city\',\'$tax\',\'$county\')\,\n ";
} else {
print FILE "INSERT INTO column1 (ID, taxcity, taxrate,taxcounty)
VALUES\n";
}
close (FILE);
$totalrecs++;
}
# END

[Eric]

Eric wrote:

>
> Oh Oh...
>
> I just discovered I am missing record #1 in the output file...my counter
> approach is apparently flawed.
>
> Sorry to have overlooked this in the initial post.

Here is my latest attempt to parse out the fields, but still getting an
uninitialized value in concatenation <.> or string in the print $1, etc.
line.

#!/usr/bin/perl -w
#
use strict;
my $line;
my $increment;
my $outfile;
my $filename;
my $totalrecs;
my $city;
my $tax;
my $county;
print "Enter File Name To Tweak: ";
$filename = <STDIN>;
print "Enter Output File Name: ";
$outfile = <STDIN>;
open (INFILE, "$filename");
$totalrecs = 0;
$increment = 1;
while ($line = <INFILE>) {
chomp($line);
&write2outfile;
$increment++;
}
close (INFILE); #close input file
print "Net Records: $totalrecs -1\n";

### SUBROUTINE TO PRINT TO ASCII .CSV FILE
sub write2outfile
{
open (FILE ,">>$outfile");
if ($totalrecs >0) {
$line =~ /([\s\w]+)\,([\d]+)\,([\s\w]+)/;
$city = $1;
$tax = $2;
$county = $3;
print FILE "\($increment\,\'$city\',\'$tax\',\'$county\')\,\n ";
} else {
print FILE "INSERT INTO column1 (ID, taxcity, taxrate,taxcounty)
VALUES\n";
}
close (FILE);
$totalrecs++;
}
# END

[Jay Tilton]

Eric <nospam@xx.com> wrote:

: I have a .csv file with 1750 records to reformat from time to time which
: look like:
:
: Zenia,7.25,Trinity
:
: So far, I'm now getting:
:
: (1749,'Zamora,7.25,Yolo'),
:
: The left-right parens and autoincrement working, but am
: stuck trying to add a single quote to the end of the City
: field and a single quote to the beginning of the County
: field like:
:
: (1749,'Zamora',7.25,'Yolo'),
:
: It would be helpful to add a semi-colon to the END of the
: very LAST line like:
:
: (1750,'Zenia',7.25,'Trinity');
:
: But I'm stumped on that one also.
:
: ===SCRIPT===
: #!/usr/bin/perl -w
: #
: use strict;

Nice touch, but your program doesn't even get through compilation
because of all the undeclared variables.

: my $line;
: print "Enter File Name To Tweak: ";
: $filename = <STDIN>;
: print "Enter Output File Name: ";
: $outfile = <STDIN>;

You should probably want to chomp() those values before trying to use
them as filenames.

: open (INFILE, "$filename");
^ ^
Useless use of quotes.

Always check the return from open() for success.

: $totalrecs = 0;
: $increment = 1;
: while ($line = <INFILE>) {
: chomp($line);
: &write2outfile;
^
Prefer to use the write2outfile() form of subroutine call.

: $increment++;
: }
: close (INFILE); #close input file
: print "Net Records: $totalrecs -1\n";
:
: ### SUBROUTINE TO PRINT TO ASCII .CSV FILE
: sub write2outfile
: {
: open (FILE ,">>$outfile");

How about opening the file once before writing then closing it when all
writing is complete, instead of re-opening and re-closing every time
another line is to be added.

: if ($totalrecs >0) {
: print FILE "\($increment\,\'$line\'\)\,\n";
^ ^ ^ ^ ^ ^
Why did you backslash-escape all those characters that have no special
meaning in a double-quoted string?

: } else {
: print FILE "INSERT INTO column1 (ID, taxcity, taxrate, taxcounty)
: VALUES\n";
: }

That if/else construct will effectively discard the first line of input.

: close (FILE);
: $totalrecs++;
: }
: # END

The split() and printf() functions can be used to wrap certain fields in
single quotes with mimimum fuss.

To get a semicolon on the final line, check whether INFILE has reached
EOF and alter the line's last character accordingly.

#!/usr/bin/perl
use warnings;
use strict;
print "Enter File Name To Tweak: ";
chomp( my $filename = <STDIN> );
print "Enter Output File Name: ";
chomp( my $outfile = <STDIN> );

open (INFILE, '<', $filename)
or die "Cannot open '$filename' for read: $!";
open (FILE , '>>', $outfile)
or die "Cannot open '$outfile' for append: $!";
print FILE
"INSERT INTO column1 (ID, taxcity, taxrate, taxcounty) VALUES\n";
while (<INFILE>) {
chomp;
printf FILE "(%d,'%s',%f,'%s')%s\n",
$. ,
split(/,/) ,
eof(INFILE) ? ';' : ',';
}
close(INFILE);
close(FILE);

It seems a roundabout way of transferring records from one database to
another when the DBI module is available, though.

[Eric]

Jay Tilton wrote:

Thank you very much, Jay, for your insightful comments and help.

> :
> : ===SCRIPT===
> : #!/usr/bin/perl -w
> : #
> : use strict;
>
> Nice touch, but your program doesn't even get through compilation
> because of all the undeclared variables.

Umh, my bad I added a bunch in the revision but it still didn't cut the
mustard.

> : open (INFILE, "$filename");
> ^ ^
> Useless use of quotes.

Yup. I need to watch that.

>
> Always check the return from open() for success.

Yup. Ditto.

> : open (FILE ,">>$outfile");
>
> How about opening the file once before writing then closing it when all
> writing is complete, instead of re-opening and re-closing every time
> another line is to be added.

Old bad habit in the script I was trying to hack up.

> : if ($totalrecs >0) {
> : print FILE "\($increment\,\'$line\'\)\,\n";
> ^ ^ ^ ^ ^ ^
> Why did you backslash-escape all those characters that have no special
> meaning in a double-quoted string?

Same thing ;-(

>
> : } else {
> : print FILE "INSERT INTO column1 (ID, taxcity, taxrate, taxcounty)
> : VALUES\n";
> : }
>
> That if/else construct will effectively discard the first line of input.

Yup...so I now realize.

> The split() and printf() functions can be used to wrap certain fields in
> single quotes with mimimum fuss.

I just learned something important. Thanks!

>
> To get a semicolon on the final line, check whether INFILE has reached
> EOF and alter the line's last character accordingly.

Didn't know how to do this so thanks for the instructional help.

> It seems a roundabout way of transferring records from one database to
> another when the DBI module is available, though.

Yup, however the output is for quick copy & paste via MYSQLCC on the PC
into a MySQL table...and may be combined with some table creation code
to do several things all at once.

Sincere thanks again for taking the time to help and especially the
instructional info. BTW, I took a quick test drive and your code worked
flawlessly. The learing continues here, as sporadic as it might be.

Regards,

Eric

[Eric]

Ooops...just noticed that FOUR zeros get added onto each of the numbers
in the numeric/decimail field from:

> printf FILE "(%d,'%s',%f,'%s')%s\n",

How can I correct this?

Thanks.

[Jay Tilton]

Eric <nospam@xx.com> wrote:

: Ooops...just noticed that FOUR zeros get added onto each of the numbers
: in the numeric/decimail field from:
:
: > printf FILE "(%d,'%s',%f,'%s')%s\n",
:
: How can I correct this?

See perldoc -f sprintf for a list of the different formatting
conversions available.

If you want, say, two decimals in the third field, change "%f" to
"%.2f".

Or if you would just like that field to pass through unchanged, change
"%f" to "%s" .

[Eric]

Jay Tilton wrote:

>
> Eric <nospam@xx.com> wrote:
>
> : Ooops...just noticed that FOUR zeros get added onto each of the numbers
> : in the numeric/decimail field from:
> :
> : > printf FILE "(%d,'%s',%f,'%s')%s\n",
> :
> : How can I correct this?
>
> See perldoc -f sprintf for a list of the different formatting
> conversions available.
>
> If you want, say, two decimals in the third field, change "%f" to
> "%.2f".
>
> Or if you would just like that field to pass through unchanged, change
> "%f" to "%s" .

Thanks. I found an example using %g that seems to work.

BTW, I've been studying your code and am simply baffled on how you get
auto-incrementing of the 1st field. I'm trying to learn from this, and
also stumped as to exactly HOW the parsing of the 3 fields occurs to
include leaving the white spaces between multi-word city names. Is that
the split function?

This is another (humbling) educational experience for me!

[Jay Tilton]

Eric <nospam@xx.com> wrote:

: BTW, I've been studying your code

Briefly recapping that,

printf FILE "(%d,'%s',%f,'%s')%s\n",
$. ,
split(/,/) ,
eof(INFILE) ? ';' : ',';

: and am simply baffled on how you get
: auto-incrementing of the 1st field.

That's the $. variable, which keeps a count of the number of records
(lines) read from a filehandle. As long as one line read corresponds to
one line written, $. can be used to enumerate the lines being output.
See "perldoc perlvar" for gory details.

: also stumped as to exactly HOW the parsing of the 3 fields occurs to
: include leaving the white spaces between multi-word city names. Is that
: the split function?

Yup. "split(/,/)" bursts the value in $_ (the implied second argument
to the split function) into a list of "whatever is not a comma" values.
Presence of whitespace in a field is irrelevant, since whitespace is not
a comma. See "perldoc -f split" for more info on split() and its
capabilities.