Trying to write a CSV file with commas 1

[daveinOK2004]

Greetings and thanks in advance for any responses.

I'm new to Perl in particular and scripting in programming in general, and so far find it fascinating even if a bit frustrating at times.

I'm trying to write a PL file that will take one CSV file, parse it in (which I have done using TEXT::CSV. I then perform some manipulation with some of the fields and add a few of my own. I've gotten all of this working fine.

Now my final task is to write the new data to an output.csv file. I've got it working, with the exception that some of my fields may contain a comma, which breaks the output file.

#!/usr/bin/perl -w
use strict;
use warnings;
use Text::CSV;

my $infile = 'c:\work\input.csv';
my $outfile = 'c:\work\output.csv';

my $csv = Text::CSV->new();

open (CSV, "<", $infile) or die $!;
open (FH, ">", $outfile) or die "$!";

while (<CSV>) {
if ($csv->parse($_)) {
my @columns = $csv->fields();

**** bunch of manipulation code ****

print FH "$col1,$col2,$col3,$col4,$col5,$col6...$col36\n";

else {
my $err = $csv->error_input;
print "Failed to parse line: $err";
}
}

close FH;
close CSV;

Columns 3 and 5 are likely to contain one or more commas and break the whole output file. I know the fix is to put quotes around those columns, but don't know how to do that in Perl.

Can anyone help me out? Sorry for asking what is such a "dumb question".

Dave

 

[daveinOK2004]

No need to respond. I figured it out, using TEXT::CSV for output as well as input. Much cleaner.

 

[KevinADC]

Use the module to write the data to the file too. I am sure it can handle embedded commas. Read the Text::CSV documentation. If you get stuck ask more questions. You might want to try Text::CSV_XS also, which should be faster tha Text::CSV.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[ishnid]

It's incredible how many people come to forums and say that they're new to Perl and want to find out how to do things without using modules. It's also incredible how many people will solve their own question and not post their solution for people coming after them to see. Threads like this make my day.

 

[lchenard]

For those of you who do not know about or do not want to use additional modules you could also go thru the horrible steps of switching out the embedded comma for another symbol in each of your fields for each record

I've chosen to deliminate my records with the "|" symbol because of the frequency that I find users adding a "," to their field entries. However if i want to provide a downloadable csv file for functions such as a mail merge or import into outlook etc i have to deal with those commas.

example
1|firstname|lastname|address1|city|state|zip|\n
.
.
$record = "1|John|Doe|12134 - 103 Street, Apt 101|City|State|10101|\n";

$record =~ s/,/*/g;
$record =~ s/|/,/g;
.
.

1,John,Doe,12134 - 103 Street* Apt 101,City,State,10101,\n

 

[KevinADC]

or just make a valid csv entry:

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$line[/blue] = [red]'[/red][purple]1|firstname|last, name|address1|city|state|zip[/purple][red]'[/red][red];[/red]

[black][b]my[/b][/black] [blue]@stuff[/blue] = [url=http://perldoc.perl.org/functions/split.html][black][b]split[/b][/black][/url][red]([/red][red]/[/red][purple][purple][b]\|[/b][/purple][/purple][red]/[/red],[blue]$line[/blue][red])[/red][red];[/red]
[olive][b]for[/b][/olive] [red]([/red][blue]@stuff[/blue][red])[/red] [red]{[/red]
   [olive][b]if[/b][/olive] [red]([/red][red]/[/red][purple],[/purple][red]/[/red][red])[/red] [red]{[/red]
      [blue]$_[/blue] = [red]qq~[/red][purple]"$_"[/purple][red]~[/red][red];[/red]
   [red]}[/red]
[red]}[/red]
[black][b]my[/b][/black] [blue]$csv[/blue] = [url=http://perldoc.perl.org/functions/join.html][black][b]join[/b][/black][/url] [red]'[/red][purple],[/purple][red]'[/red] ,[blue]@stuff[/blue][red];[/red]
[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [blue]$csv[/blue][red];[/red]

------------------------------------------
- Kevin, perl coder unexceptional!

 

[daveinOK2004]

Ishnid,

FYI, the reason I didn't post my code once I figured it out was that it wasn't complete yet. I'd rather post a commented, useful example than just a clip of code that doesn't do anything by itself. Thanks for making such a self serving unconstructive comment. Hope you feel better.

For everyone else, here's what I've put together. I hope the example proves useful. Thanks to everyone who made constructive comments. I know there are some inconsistencies still in how I coded things from one spot to another, but I worked from examples. I'm sure it could be cleaned up and made a bit more efficient, but it accomplishes the goal at hand.

#!/usr/bin/perl -w
use strict;
use warnings;
use Text::CSV;

##############################################
# CSV prep utility    v1.0                   #
#                                            #
# This utility will take a CSV file export   #
# and prepare it for input into our Exchange #
# 2003 Server as a mail enabled contact.     #
##############################################

# Source CSV file will look like this:
# HEADER: Obj-Dist-Name (DN),Display Name (cn),E-Mail Addresses (rfc822Mailbox),Company (Company),Department (department),Office (physicalDeliveryOfficeName),Phone Number (telephoneNumber),First Name (givenName),Initials (initials),Last Name (sn),Title (title)
# RECORD: /o=DOMAIN/ou=LOCATION/cn=Recipients/cn=BobMarley,"Marley, Bob",bobm@rasta.org,Rasta Inc,Voice Services,"Kingston, Jamaica",777-555-1234,Bob,B,Marley,rastafarian

# Define the files that will be used. Future revision may prompt for these with the values here as a default.
my $infile = 'c:\work\input.csv';
my $outfile = 'c:\work\output.csv';

# Create CSV instances for input and output using the TEXT::CSV module
my $csv = Text::CSV->new();
my $csvout = Text::CSV->new();

# Open the infile and create the outfile.
open (CSV, "<", $infile) or die $!;
open my $csv_fh, ">", $outfile or die $!;

# Create an array with the fieldnames. Write this as the header for the output CSV file. 
my @fields = ('DN','objectClass','cn','distinguishedName','instanceType','displayName','targetAddress','mailNickname','name','objectCategory','mail','sn','givenName','showInAddressBook',
'Company','Department','physicalDeliveryOfficeName','telephoneNumber','title');
if ($csv->combine (@fields)) {
      my $string = $csv->string;
      print $csv_fh "$string\n";
      }
  else {
      my $err = $csv->error_input;
      print "combine () failed on argument: ", $err, "\n";
      }

# Begin parsing through the source file. Store the fields in variables denoted by the inFieldName.
while (<CSV>) {
    if ($csv->parse($_)) {
        my @columns = $csv->fields();
        my $inDN = $columns[0];
        my $inCN = $columns[1];
        my $inrfc822Mailbox = $columns[2];
        my $inCompany = $columns[3];
        my $inDepartment = $columns[4];
        my $inphysicalDeliveryOfficeName = $columns[5];
        my $intelephoneNumber = $columns[6];
        my $ingivenName = $columns[7];
        my $inInitials = $columns[8];
        my $insn = $columns[9];
        my $intitle = $columns[10];        
        
        #Now that we have the input fields, prepare the data for our output file.
        # break the incoming DN down to get just the CN from it.
        my @ary=split('/',$inDN);
        my $cntmp=$ary[-1];
        @ary=split('=',$cntmp);
        
        #now create the fields for output.
        my $DN="CN=$ary[-1],OU=test,DC=domain,DC=local";
        print $DN;
        print "\n";
        my $objectClass = "contact";
        my $cn = $ary[-1];
        my $distinguishedName = "OU=test,DC=domain,DC=local";
        my $instanceType = "4";
        my $displayName = "$ingivenName $inInitials $insn";
        my $targetAddress = "SMTP:$inrfc822Mailbox";
        my $mailNickname = "$ingivenName$insn$inInitials";
        my $name = $displayName;
        my $objectCategory = "CN=Person,CN=Schema,CN=Configuration,DC=domain,DC=local";
        my $mail = $inrfc822Mailbox;
        my $sn = $insn;
        my $givenName = $ingivenName;
        my $showInAddressBook = "CN=Default Global Address List,CN=All Global Address Lists,CN=Address Lists Container,CN=Org Name,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=domain,DC=local;CN=All Contacts,CN=All Address Lists,CN=Address Lists Container,CN=Organization Name,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=domain,DC=local";
        my $Company = $inCompany;
        my $Department = $inDepartment;
        my $physicalDeliveryOfficeName = $inphysicalDeliveryOfficeName;
        my $telephoneNumber = $intelephoneNumber;
        my $title = $intitle;
        
        # Now create an array containing the output data.
        my @fields = ($DN,$objectClass,$cn,$distinguishedName,$instanceType,$displayName,$targetAddress,$mailNickname,$name,$objectCategory,$mail,$sn,$givenName,$showInAddressBook,$Company,$Department,$physicalDeliveryOfficeName,$telephoneNumber,$title);
        
        #write it to the output file
        if ($csv->combine (@fields)) {
          my $string = $csv->string;
          print $csv_fh "$string\n";
          }
        else {
            my $err = $csv->error_input;
            print "combine () failed on argument: ", $err, "\n";
        }

    } else {
        my $err = $csv->error_input;
        print "Failed to parse line: $err";
    }
}
# close open handles and you are done.
close $csv_fh;
close CSV;

### The resultant file can now be imported to Active Directory using
### csvde -i -f c:\work\output.csv -v -j c:\log
### from the exchange system console.

 

[ishnid]

Ishnid,

FYI, the reason I didn't post my code once I figured it out was that it wasn't complete yet. I'd rather post a commented, useful example than just a clip of code that doesn't do anything by itself. Thanks for making such a self serving unconstructive comment. Hope you feel better.

You misunderstand. There was no sarcasm in my comment whatsoever. My point was that it was great to see someone new to Perl whose first instinct is already to look to CPAN to make things easier for himself. Many people take quite a while before they're convinced to abandon their fixation on "core Perl functions". It was also great to see that when you did figure out a solution, you posted back to say so, and mentioned how you did it. I wasn't prompting you to post the code: the code for creating a line of a CSV file using Text::CVS is in the documentation for that module.

FWIW because the post really did "make my day", I was the one that starred you.

 

[daveinOK2004]

OK, if that was really your intent with your post, then I appologize for my response. So many times, written posts like this are misinterpreted. I took your post to be a stab against me, and responded inappropriately.