Perl script to handle CSV files

Hi.

I'm currently trying to write a perl script.
I have a folder containing CSV files contained in the following directory:
"C:\Users\Stephen\Desktop\Database_Design"

The files are currently named in the format: "20130730_p12_A1.csv" (csvfiles.jpg attached).

Each CSV file contains two columns: "mass" and "intensity".
Firstly, what I'd like to do, is to take the full name of each file (201307030_p12_A1) and add it to the next adjacent column for each of these files, under the heading "filename". This filename will then be "copied down" to the last entry within the table (filename-column.jpg attached).




Once this column has been added to each CSV file, the next step will be to modify the filename for each CSV file.

First of all, the date and "p12" should be removed from each filename, so:

"20130730_p12_A1.csv"

would simply read:

A1.csv
....

A1.csv
A2.csv
A3.csv
etc..

Secondly, a text file (mapcodelist.txt) will be used to add information into the filename.

For naming convention, I'd like to change any full stops (".")s within mapcodelist to underscores ("_")s.
I'd then like to tie up this mapcodelist information with the CSV files.

For example:

Using mapcodelist.txt (attached), A1.csv would become:

"A1_GO1_3.csv"

A2.csv becoming A2_G0_808.csv
A3.csv becoming A3_OUM19363.csv

and so on...

If anybody could possibly help me out with this, I'd very much appreciate it.

Thanks

Stephen.
csvfiles.jpg
filename-column.jpg
mapcodelist.txt
StephenMcGowanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
#!/usr/bin/perl
open M,"<mapcodelist.txt" or die "mapcodelist.txt $!";
while( <M> ){
  my($k,$v)=split;
  $v=~s/\./_/g;
  $m{$k}=$v;
}
close M;
@ARGV=<C:/Users/Stephen/Desktop/Database_Design/20130730_p12_*.csv>;
$^I=".bak";
while( <> ){
    s/$/,$ARGV/;
   print;
}
for( <C:/Users/Stephen/Desktop/Database_Design/20130730_p12_*.csv> ){
  ($r=$_) =~ s/\w+_(\w+)(?=\.csv)/$1_$m{$1}/;
  rename $_,$r or warn " rename $_,$r  $!";
}
0
StephenMcGowanAuthor Commented:
Thanks ozo,

The script worked great. The only thing I've noticed is that the table header "filename" isn't created and that the full directory pathway is transferred to the spreadsheet, where I would only like the date, the 'p-number' (p12) and the 'A-number' (A5).
(see the two jpgs attached)

Also, I've noticed that in your script, you've defined the date within the directory pathway:

@ARGV=<C:/Users/Stephen/Desktop/Database_Design/20130730_p12_*.csv>;

This filename date will vary and so should have the ability to change. Would it be possible to simply look for all csv files within a folder regardless of the "20130730_p12_" name?

Thanks again,

Stephen.
table1.jpg
table2.jpg
0
ozoCommented:
#!/usr/bin/perl
use warnings;
use strict;
open M,"<mapcodelist.txt" or die "mapcodelist.txt $!";
my %m;
while( <M> ){
    my($k,$v)=split;
    $v=~s/\./_/g;
    $m{$k}=$v;
}
close M;
chdir "C:/Users/Stephen/Desktop/Database_Design/" or die $!;
@ARGV=<*.csv>;
$^I=".bak";
while( <> ){
    chomp;
    $\=/^mass/?",filename$/": ",$ARGV$/";
    print;
}
for( <*.csv> ){
  my $r;
  ($r=$_) =~ s/\w+_(\w+)(?=\.csv)/$1_$m{$1}/;
  rename $_,$r or warn " rename $_,$r  $!";
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StephenMcGowanAuthor Commented:
Hi ozo,

Thanks a lot for getting back to me. I've submitted the question and awarded the points to you. :)

Just one last request with this script:

How do I go about removing the ".csv"s from this newly created column? I also want to give this column a header: "filename" (table1.jpg)

I also want to create a new column with the heading: "samplename".. this takes the second part of the name which was used in the spreadsheet renaming, and copies this down throughout the column. So for an example A1_GO1_3.csv would take the GO1_3 add this to the samplename column and copy it down (table2.jpg)

Thanks,

Stephen.
table1.jpg
table2.jpg
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.