We help IT Professionals succeed at work.

Perl script to generate a CSV file

itsme_asif
itsme_asif asked
on
1,038 Views
Last Modified: 2013-12-26
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

Comment
Watch Question

Commented:
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

Commented:
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

Author

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2009

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

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

Author

Commented:
Perfect, works great. Thanks

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2015

Commented:
Do the lines in the input file end with "\r\n"?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.