Re: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file

in reply to Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file

Hi ei6eqb and 73 form ka3uca!

I think I have found a program that will do what you need. The program is: Update: Input file format changed per his post

#!/usr/bin/perl
use strict;
use warnings;

3 == @ARGV or die "USAGE: perl $0 filein fileout shopID\n";

my ($file_in, $file_out, $shopID) = @ARGV; # file_in == file3, file_ou
+t == fout.csv shopID == 2345

open my $in, '<', $file_in or die "Unable to open $file_in: $!";
open my $out, '>', $file_out or die "Unable to open $file_out: $!";

chomp(my @hdrs = split /,/, <$in>);

my @cols = split /\n/, <<EndCols;
Cust Code
Inv Num
MobileNumber
Inv Date
PosId
PosUserID
ShopID
Prodcode
ProdDiscription
ProdDept
ProdGroup1
ProdGroup2
Qty
Net Amt
EndCols

print $out "DeviceID,PosTxnID,MobileNumber,Timestamp,PosID,PosUserID,S
+hopID,Prodcode,ProdDescription,ProdDept,ProdGroup1,ProdGroup2,Qty,Val
+ue\n";

my %tmp;

while (<$in>) {
    next unless /^\S+\s-\s#\s/ .. /^,/;
    chomp;
    if (/^,/) {
        %tmp = ();    
    }
    elsif (/^(ZB\d+)\s-\s#\s([^,]+)/) {
        $tmp{Prodcode} = $1;
        $tmp{ProdDiscription} = $2;
        $tmp{ShopID} = $shopID;    
    }
    else {
        @tmp{@hdrs} = split /,/;
        $tmp{'Inv Date'} = join("/", reverse split '/', $tmp{'Inv Date
+'});
        $tmp{'Inv Date'} .= ' 00:00';
        print $out join(",", map $_ // '', @tmp{@cols}), "\n";    
    }
}
close $in or die $!;
close $out or die $!
[download]

Run from the command line like perl test2.pl file3 fout.csv 2345 where test2.pl was my program, file3 was my input file, fout.csv is the file to be written and 2345 the shopID.

I used your input file and got the results:

DeviceID,PosTxnID,MobileNumber,Timestamp,PosID,PosUserID,ShopID,Prodco
+de,ProdDescription,ProdDept,ProdGroup1,ProdGroup2,Qty,Value
VINT01,112681,,2018/01/23 00:00,,,2345,ZB101,Acme Widget Large 20 inch
+,,,,1,23.5
RR01,112683,,2018/01/23 00:00,,,2345,ZB101,Acme Widget Large 20 inch,,
+,,2,47
VINT01,112681,,2018/01/23 00:00,,,2345,ZB1201,Acme Widget Small 5 inch
+,,,,2,100
PIP01,112670,,2018/01/23 00:00,,,2345,ZB2101,Acme Widget Large 15 inch
+,,,,1,12.5
VINT01,112681,,2018/01/23 00:00,,,2345,ZB2101,Acme Widget Large 15 inc
+h,,,,1,12.5
VCR01,112674,,2018/01/23 00:00,,,2345,ZB2401,Acme Widget Medium 10 inc
+h,,,,2,65
VINT01,112681,,2018/01/23 00:00,,,2345,ZB2501,Acme Widget Small 6 inch
+,,,,1,37.5
CAPB01,112672,,2018/01/23 00:00,,,2345,ZB3501,Acme Widget GOLD,,,,1,29
SANML,112673,,2018/01/23 00:00,,,2345,ZB3501,Acme Widget GOLD,,,,1,29
RS01,112657,,2018/01/23 00:00,,,2345,ZB3701,Acme Widget Large 24 inch,
+,,,1,21
CAPB01,112672,,2018/01/23 00:00,,,2345,ZB3701,Acme Widget Large 24 inc
+h,,,,1,21
BPS01,112679,,2018/01/23 00:00,,,2345,ZB3701,Acme Widget Large 24 inch
+,,,,2,42
VINT01,112681,,2018/01/23 00:00,,,2345,ZB3701,Acme Widget Large 24 inc
+h,,,,1,21
MC01,112682,,2018/01/23 00:00,,,2345,ZB3701,Acme Widget Large 24 inch,
+,,,1,21
PIP01,112670,,2018/01/23 00:00,,,2345,ZB3801,Regular Acme Widget Large
+ 20 inch,,,,1,21
RE01,112663,,2018/01/23 00:00,,,2345,ZB4001,High Type Acme Widget,,,,1
+,30
CAPB01,112672,,2018/01/23 00:00,,,2345,ZB4001,High Type Acme Widget,,,
+,1,30
APP01,112685,,2018/01/23 00:00,,,2345,ZB4001,High Type Acme Widget,,,,
+1,30
RR01,112683,,2018/01/23 00:00,,,2345,ZB401,Nugget Type Acme Widget,,,,
+2,48
TB01,112665,,2018/01/23 00:00,,,2345,ZB4901,Acme Widget Large 17 inch,
+,,,1,38.5
DD01,112659,,2018/01/23 00:00,,,2345,ZB501,Acme Widget hexangle,,,,3,9
+9
VINT01,112681,,2018/01/23 00:00,,,2345,ZB501,Acme Widget hexangle,,,,1
+,33
RS01,112657,,2018/01/23 00:00,,,2345,ZB701,Acme Widget Circular 20 inc
+h,,,,1,33
PIP01,112670,,2018/01/23 00:00,,,2345,ZB701,Acme Widget Circular 20 in
+ch,,,,1,33
BPS01,112679,,2018/01/23 00:00,,,2345,ZB701,Acme Widget Circular 20 in
+ch,,,,1,33
RE01,112663,,2018/01/23 00:00,,,2345,ZB901,Acme Widget Square 3 inch,,
+,,1,32
TB01,112665,,2018/01/23 00:00,,,2345,ZB901,Acme Widget Square 3 inch,,
+,,1,32
[download]

The program first checks for 3 parameters on the input line (in @ARGV). It then gets the headers from the input file (to be used in a hash slice further down in the program @tmp{@hdrs} = split /,/;).

Then, it gets the columns needed to retrieve the data (also in another hash slice further down @tmp{@cols}).

Comment on Re: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file Select or Download Code

Replies are listed 'Best First'.
Re^2: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file by GrandFather (Saint) on Jan 30, 2018 at 20:24 UTC
Removing the use of Text::CSV is a major backward step! Consider what happens if a family of products have titles of the form: `Nugget Type Acme Widget, blue` [download] Your code will now generate a badly formed csv file because the comma in the product title is seen as a field separator. With Text::CSV generating the output at least the file is generated correctly and the comma containing title is correctly quoted. Premature optimization is the root of all job security	[reply] [d/l]
Re^3: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file by Cristoforo (Curate) on Jan 31, 2018 at 01:08 UTC
I agree much with you Grandfather, however he stated in his post- When I run the script on my input.csv file the output.csv file which is created by the script has double quotes in two of the data columns, i.e. the Timestamp field "2018-01-23 00:00" and the ProdDescription field "Acme Widget Large 20 inch" These double quotes need to be removed to comply with the required data format of the final csv file. So I don't know if he sends the result somewhere, they will be able to parse a proper CSV file. Needed additions/changes to the script to make it proper would be: Add `use Text::CSV;` in the header with the other use statements. Create a new csv object, `my $csv = Text::CSV_XS->new({binary => 1, eol => $/});` before the while loop. Instead of the print statement, use `$csv->print($out, [ @tmp{@cols} ]);`.	[reply] [d/l] [select]
Re^2: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file by ei6eqb (Initiate) on Jan 31, 2018 at 02:12 UTC
Hi Cristoforo ka3uca , Marshall, Tux, Dallaylaen, GrandFather, and ALL other Monks , I must beg your forgiveness as in my quest to sanitize the input.csv file for publication at this site in my original question I made a Cardinel mistake and I inadvertently pasted an incorrect copy of this file. I have now edited my origional question to include the correct version of the INPUT.CSV file , which now means your very king efforts in scripting your code to answer my question does not now work as expected. My humble apologies to you and all others for your efforts, for which my mistake has wasted your time. Sincere Thanks to you all Tony	[reply]
Re^3: Modifying an existing Perl script to ask for input and output filenames and also remove double quotes for outfile file by poj (Abbot) on Feb 01, 2018 at 18:56 UTC
Try #!/usr/bin/perl use strict; use Text::CSV; my @hdr = qw( DeviceID PosTxnID MobileNumber Timestamp PosID PosUserID ShopID Prodcode ProdDescription ProdDept ProdGroup1 ProdGroup2 Qty Value); my @e14 = ('','','','','','','','','','','','','',''); my $spacer = 0; # grab user input.. print "Enter the name of the file to read: "; my $filetoread = <STDIN>; chomp ($filetoread); print "Enter the name of the file to write: "; my $filetowrite = <STDIN>; chomp ($filetowrite); open my $fh_read, '<', $filetoread or die "Unable to read [$filetoread] : $!"; my $csvi = Text::CSV->new( { binary=>1 } ); open my $fh_write, '>', $filetowrite or die "Unable to write [$filetowrite] : $!"; my $csvo = Text::CSV->new( { binary=>1, eol=>$/, quote_space => 0} ); my $ar = $csvi->getline($fh_read); s/ //g for @$ar; $csvi->column_names($ar); $csvo->column_names(@hdr); $csvo->print($fh_write,\@hdr); my ($code,$desc); print "Reading file $filetoread\n"; while (my $hri = $csvi->getline_hr($fh_read)) { if ($hri->{InvDate}) { my ($dd,$mm,$yyyy) = split "/",$hri->{InvDate}; my $hro = { DeviceID => $hri->{CustCode}, PosTxnID => $hri->{InvNum}, MobileNumber => '', Timestamp => $yyyy.'-'.$mm.'-'.$dd.' 00:00', PosID => '', PosUserID => '', ShopID => '2345', Prodcode => $code, ProdDescription => $desc, ProdDept => '', ProdGroup1=> '', ProdGroup2=> '', Qty => $hri->{Qty}, Value => $hri->{NetAmt} }; if ($spacer) { $csvo->print($fh_write,\@e14); $spacer = 0; } $csvo->print_hr($fh_write,$hro); } elsif ($hri->{CustCode}) { ($code,$desc) = split ' - # ',$hri->{CustCode}; } elsif ($hri->{LineDisc}) { $spacer = 0; } } close $fh_read; close $fh_write; print "Created file $filetowrite\n"; [download] poj	[reply] [d/l]

In Section Seekers of Perl Wisdom