writing a perl script 1

[zincyo]

I'm having some trouble using perl to write to excel csv files. Does anyone have any idea how to write a perl script which will remove columns in excel and/or text?


please help!

 

[travs69]

Are you trying to read/write excel or csv? Perl will write new excel files and it can read excel files but I don't think it will edit existing files (but I may be wrong). If it is just csv then it can do anything you want to it. Do you have any example code you can post?

 

[zincyo]

i'm working with an existing csv file. I would post my code but I just discovered its wrong!

 

[travs69]

Well you need to post what you have so we can point you in the right direction.

 

[zincyo]

#!/usr/local/bin/perl
my $delim=',';
my @data=($colA,$colB,$colc$cold,$cole,$colf$colg,$colh,$coli$colj,$colk,$coll$colm);
print join( $delim, @data )."\n";


this is supposed to delete the columns to the right of column N.

 

[travs69]

I don't see where you read a file in anywhere or where your values come from. In essense it looks like it would work (but I see a few missing comma's and some inconsistances in your variables).

 

[stevexff]

Text::CSV makes it much easier to parse CSV files, as it caters for all the things you haven't thought of.

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

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

while (<DATA>) {
   chomp;
   $csv->parse($_);
   my @stuff = $csv->fields();
   print "$stuff[3]\t$stuff[7]\n";
}

__DATA__
1,2,3,"4,5",6 ,,,Does yours handle embedded commas?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

perhaps you could explain to me what this code is doing, or more importantly how I would go about removing a column and/or text.

Thanks so much, I really appreciate it!

 

[stevexff]

Yes, but not until later today...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

thanks steve-- just to clarify, I have a CSV file which is update daily. What I need to do is remove the text in the first line, and also the last column. To do this, I think i need to use the shift or pop array functions, i'm just not totally sure.

I think I need some combination of the above code, and this code:



my @array ;



@array = <stdin> ;



chomp(@array);

foreach my $var (@array)

{

print "$var\n" ;

}

 

[travs69]

if you want to use shift and pop you can just take the example above, shift it, pop it, then join it back with comma's and print it back out.

 

[stevexff]

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

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

while (<>) {
   next if ($. == 1);
   $csv->parse($_);
   my @stuff = ($csv->fields())[0 .. 6];
   print join(',', @stuff), "\n";
}

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

Steve, I don't undestand how to execute this code / get it to apply to a specific spreadsheet. any further help would be greatly appreciated.

 

[stevexff]

Assuming you are on Windows, as it stands, it will print the first 7 fields in each line of the CSV file (except the first line). So if we create this script and call it csvparse.pl, then

perl csvparse.pl input.csv > output.csv

should do what you want. Obviously, by changing the number 6 you can vary how many columns get printed.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

steve, thank you for your help, however I have not been able to get your code to work. I just wrote something of my own...

Again, what I am trying to do is run this perl script so that it removes the first row and the last column of my csv file. THis is what I have as of now -- it is close but not correct:

#!/usr/bin/perl -w

use strict;

use warnings;

use Text::csv;

my @array;
@array = <stdin> ;

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




foreach my $var (@array)
{
$csv->parse($_);
chomp(@array);
my @array = $csv->fields();

shift(@array);

# delete $array[0]; (this is where I was attempting to remove the first row, however it doesn't work)

print "$var\n" ;


}

I believe that what this would eventually allow for me to do is have the CMD type the csv file into the code and run it thru line by line... any ideas where i'm going wrong?

 

[stevexff]

any ideas where I'm going wrong?

Yes, you aren't listening (reading?). I've already given you some working code, and shown you how to execute it, which you seem to have ignored. But since you asked for it, here is an itemised breakdown of what's wrong with your code.

#!/usr/bin/perl -w [red]# 1[/red]

use strict;

use warnings;

use Text::csv; [red]# 2[/red]

my @array;
@array = <stdin> ; [red]# 3[/red]

my $csv = Text::CSV->new(); [red]# 4[/red]




foreach my $var (@array)
 {        
    $csv->parse($_); [red]# 5[/red]
    chomp(@array); [red]# 6[/red]
    my @array = $csv->fields(); [red]# 7[/red]
    
    shift(@array); [red]# 8[/red]
    
#    delete $array[0]; (this is where I was attempting to remove the first row, however it doesn't work)

        print "$var\n" ; [red]# 9[/red]

        
}

[ol][li]shebang line gets ignored on Windows. I'm assuming you are on Windows, 'cos it's Excel...[/li][li]Should be Text::CSV (yes, it is case sensitive).[/li][li]Unless you are piping the input into your script, you will be doing a lot of typing.[/li][li]Case sensitivity (q.v.).[/li][li]$_ hasn't been set, because you used $var instead on the previous line.[/li][li]Let's do this pointlessly every time through the loop, shall we?[/li][li]Good call using the same variable name for @array - luckily perl's lexical scoping saved you from self-harming, but it doesn't make it any easier to read...[/li][li]Remove the first column from each row? Or at least it would if $_ had been set earlier (see 5 above). Or maybe you are just getting confused about which @array you are referencing (point 7 above).[/li][li]Finally, we do something with $var, even if it is only to print it out unchanged. Did I miss the part where you wrote it out to another file?[/li][/ol]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

As far as piping the input into my script goes, I was assuming that I was going to have the input automatically typed into the script by using type [file_being_edited.csv] | perl file.pl

essentially, i'm not trying to write a new file, but rather edit the one I currently have.

Now i'm sorry for writing code which doesn't make much sense but I have been given what to me seems to be a rather complicated task, and I have been trying to do it while teaching perl to myself at the same time.

Considering my screwed up code, and the code which you have given me, what would you recommend that I do?

thanks

 

[stevexff]

OK, we aren't going to edit the file in place, we are going to make a new one. That way if your program doesn't work right first time, we still have the original file.

[ol][li]Open a command prompt. cd to the directory containing file_being_edited.csv[/li][li]Using notepad, make a new file in this directory, called csvparse.pl. Paste this code into it:

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

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

while (<>) {
   next if ($. == 1);
   $csv->parse($_);
   my @stuff = ($csv->fields())[0 .. [red]6[/red]];
   print join(',', @stuff), "\n";
}

[/li][li]Save it.[/li][li]Enter the command perl csvparse.pl file_being_edited.csv > file_after_editing.csv[/li][li]If you need to change the number of columns in the output file, change the value of the red number [red]6[/red] in the example code above.[/li][li]Er, that's it...[/li][/ol]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

Steve, thank you so much for your help!

Just a quick question, rather than having the file re-write all of the columns, is there a way in which I can have it ONLY remove the last column, rather than re-writing all of them?

 

[stevexff]

There is a Tie::File module, which allows you to treat a file as if it was an array, so you can update it in place. But I'm always a bit wary about input and output to the same file, unless it's a proper database. Call me old-fashioned, but I worry about things like "What happens if there is a power cut while I'm running it?", "What if the input data aren't correctly formatted?", and "What if there's a bug in my program, and the file gets hosed?".


Disk space is cheap, why take the risk?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]