asked on

Perl script to generate a CSV file

This is the script i have
Instead of getting the output as

20,,,,,abc,0,,,reportingHirarchy,,,
21,,,,,cfg,0,,,21,,,
22,,,,,ijk,0,,,22,,,
23,,,,,asd,0,,,23,,,
24,,,,,asj,0,,,24,,,
25,,,,,qwe,0,,,25,,,

I am getting as

30,,,,,abc,0,,,reportingHirarchy,,,
30,,,,,cfg,0,,,21,,,
30,,,,,ijk,0,,,22,,,
30,,,,,asd,0,,,23,,,
30,,,,,asj,0,,,24,,,
30,,,,,qwe,0,,,25,,,

can you please help!

#!/usr/bin/perl
 
#use strict;
#use warnings;
 
open my $CSV, '<', 'sample1.csv' or die "failed to open 'sample1.csv' $!";
open my $TXT, '<', 'sample.txt' or die "failed to open 'sample.txt' $!";
open my $NEW, '>', 'new1.csv' or die "failed to open 'new1.csv' $!";
 
 
 
while(<$CSV>) {
   chomp;
   my @csv = split /,/;
  for ($i=20;$i<=30;$i++) {
 
   $csv[0]=$i;
}
   my $phone = <$TXT>;
   chomp $phone;
   $csv[5] = $phone;
   no warnings;         
  $i=$i+1; 
  print $NEW join(',', @csv[0..12]) . "\n";
   
   use warnings;
}

Open in new window

marchent

This loop ends $i with 30.
for ($i=20;$i<=30;$i++) {

$csv[0]=$i;
}

just remove, and use $i out side of while starting with 20.


$i = 20;
while(<$CSV>) {
   chomp;
   my @csv = split /,/; 
 
   $csv[0] = $i++;
 
   my $phone = <$TXT>;
   chomp $phone;
   $csv[5] = $phone;
   no warnings;         
  $i=$i+1; 
  print $NEW join(',', @csv[0..12]) . "\n";
   
   use warnings;
}

Open in new window

marchent

forgot to mention, remove below line from while loop.
$i=$i+1;

$i = 20;
while(<$CSV>) {
   chomp;
   my @csv = split /,/; 
 
   $csv[0] = $i++;
 
   my $phone = <$TXT>;
   chomp $phone;
   $csv[5] = $phone;
   no warnings;         
  print $NEW join(',', @csv[0..12]) . "\n";
   
   use warnings;
}

Open in new window

itsme_asif

ASKER

Sweet, works like a charm.

i have one more favor to ask, how can i populate a csv file with the following values
org21,,,,,,0,,,20,,,
org22,,,,,,0,,,21,,,
org23,,,,,,0,,,22,,,
org24,,,,,,0,,,23,,,
org25,,,,,,0,,,24,,,
org26,,,,,,0,,,25,,,
..
..
..
org,,,,,,,,,0,,,k,,,

Basically column 0 or 1 should be org where i should be 21 or any number and the column 1's in the following column should be org similarly coloumn 7 should be always 0 and column 10 should be and the subsequent column 10's should be i+1.

Appreciate your help

ASKER CERTIFIED SOLUTION

marchent

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adam314

for(20..25) {
printf "org%d,,,,,,0,,,%d,,,\n", $i+1, $i;
}

Adam314

That should have been:
for(20..25) {
printf "org%d,,,,,,0,,,%d,,,\n", $_+1, $_;
}

itsme_asif

ASKER

Perfect, works great. Thanks

itsme_asif

ASKER

Hello,
The script works as desired, however my input is like the following

Accountant, Financial.Intermodal Finance.1
Accounts Payable.Logistics Finance.1
Accounts Receivable.Logistics Finance.1
Adjuster, Associate Claims.Risk Mgmt.1
Adjuster, Cargo Claims.Risk Mgmt.1
Adjuster, Claims.Risk Mgmt.1

instead of getting the o/p as

org20,,,Accountant, Financial.Intermodal Finance.1,0,,org19,,,,,,,,,,,,,,,,,,,,,,,
org21,,,Accounts Payable.Logistics Finance.1,0,,org20,,,,,,,,,,,,,,,,,,,,,,,
org22,,,Accounts Receivable.Logistics Finance.1,0,,org21,,,,,,,,,,,,,,,,,,,,,,,
org23,,,Adjuster, Associate Claims.Risk Mgmt.1,0,,org22,,,,,,,,,,,,,,,,,,,,,,,
org24,,,Adjuster, Cargo Claims.Risk Mgmt.1,0,,org23,,,,,,,,,,,,,,,,,,,,,,,
org25,,,Adjuster, Claims.Risk Mgmt.1,0,,org24,,,,,,,,,,,,,,,,,,,,,,,

The o/p is getting messed up as the following ,which is potentially due to the commas in the input file
,0,,org20,,,,,,,,,,,,,,,,,,,,,,,cs Finance.1
,0,,org20,,,,,,,,,,,,,,,,,,,,,,,ms.Risk Mgmt.1
,0,,org20,,,,,,,,,,,,,,,,,,,,,,,mt.1
org23,,,Adjuster, Sr Cargo Claims.Risk Mgmt.1,0,,org20,,,,,,,,,,,,,,,,,,,,,,,

Really would appreciate your help!

#!/usr/bin/perl
 
 
open my $TXT, '<', 'pos.txt' or die "failed to open 'pos.txt' $!";
open my $NEW, '>', 'neworg.csv' or die "failed to open 'neworg.csv' $!";
 
 
 
$i = 20;
#$j=19;
 
  while(<$TXT>) {
   chomp;
   #my @TXT = split /,/; 
   #index number for 'orgHeadUniqueName'
   $TXT[4] = 0;
   #index number for 'reportingHierarchyRootUniqueName'
   $TXT[6] = "org20";
   #index number for 'uniqueName'	
   $TXT[0] = "org".$i;
 
   $i++;
#   $j++;	
 
   my $pos = <$TXT>;
   chomp $pos;
   #index number for 'displayName' for ex position code	
   $TXT[3] = $pos;
   no warnings;         
   print $NEW join(',', @TXT[0..29]) . "\n";
   
   use warnings;
}

Open in new window

ozo

Do the lines in the input file end with "\r\n"?