#8/21/2009 - Charles Oppenheimer Recursive Techonlogy LLC
#Perl Artistic license - open source

#config parameters###############################################

####file name parameters
$file = "pba_apac.csv";
$newfile = "pba_apac_update.csv";
$NOUPDATESupfile = "NOUPDATES$newfile";

### values to add to multi value picklist - update according do your data loader export file
$addvalue = "English";			#this is the value you want to add to a multi value list
$multivaluepicklistindex = 3;	#this is the column number from your DataLoader export file that has a multi value picklist.  0 indexed. 



##################################################################


#### main stuff
open(SEARCHFILE, "<$file") || print "Could not open file $file : $!";				#dataloader file exported from SFDC to be changed
open(NEWFILE, ">$newfile") || die "Could not open file $newfile : $!";				#updated data will go into this file
open(NOUPDATES, ">$NOUPDATESupfile") || die "Could not open file $NOUPDATESupfile : $!";	#file for lines that were not updated, for troubleshooting


#updating the file
while (<SEARCHFILE>) { #operate on each line in the file: 

	$beforestring = $_;		#current line
	$totalstrings++;		#counter
	

	if ($totalstrings == 1) {			# first line with SFDC dataloader export file is column headers (usually)
		print NEWFILE $_;				#add it back in pronto
		next;							#skip the rest of this stuff - so we don't add new values to column header
	}; 

	#turn current line into an array
	my @linearray=split(/,/,$beforestring);

	#get specific column that is multi value list
	my $multi = $linearray[$multivaluepicklistindex];
	$multi =~ s/\"//g;  #remove quotes

	#turn muli string into array
	my @multivaluelist = split(/;/,$multi);
		
	
	unless (grep(/$addvalue/, @multivaluelist)) {  #check for existing $addvalue string we intend to add
		push(@multivaluelist, $addvalue);
	}

	#turn multilist back into string
	my $newval = join(';',@multivaluelist);
	
	# add back quotes  
	$newval = "\"" . $newval . "\"";
	
	#update current line with changed string
	$linearray[$multivaluepicklistindex] = $newval;

	#turnarray back to string
	$newstring = join(',',@linearray); 


	#reporting and file creation
	if ($beforestring eq $newstring) {
		$nochange++; 
		print NOUPDATES $beforestring;
	} else {
		$changed++;
		print "BEFORE: $beforestring AFTER: $newstring\n";
		print NEWFILE $newstring;
	}
	#print "string after = $newstring\n";
	
}
print "out of $totalstrings changed $changed, did not change $nochange, skipped 1.  ";