insert filename into multiple tab delimited files

eagleroy has asked for the wisdom of the Perl Monks concerning the following question:

I need to read a directory for all tab-delimited files (they will have a .csv extension) and insert a new column that contains the file name(without the path) and write the modified file back to the original file name. I've tried using ParseExcel/WriteExel , CTable, AnyData, and Text::xSV and usually get stuck on creating and merging the new column or rewrtiing a corrupted file (for excel). Here's one non-working version.....

#!/usr/bin/perl -w

my $dir = 'C:/Programs' ;
use strict;
use Data::CTable;

#Read Directory for all '.csv' file

opendir(TD, $dir) || die "Can not open directory: $!:";
my @FILES=grep(/\.csv/i, readdir TD);
print @FILES;
closedir(TD);

my $file = $FILES[0];
my $Full = $file;
my $People = Data::CTable->new("roy.csv" ) || die "Can not open file: 
+$!:";
$People->clean_ws();
$People->col(PCSFileName => "hello");



$People->write(_FileName => "Roy_results.csv", _LineEnding => "dos");
foreach my $file (@FILES) {
     my $t = Data::Table::fromCSV($file) || die "Can not open file: $!
+:";
     $t->addCol($file, "PCS Filename") || die "Can not add Column ";
     print $t->csv || die "Can not write output";
}
[download]

Here's another.......

#!/usr/bin/perl -w

my $dir = 'C:/Programs' ;
use strict;
use Data::Table;

#Read Directory for all '.csv' file

opendir(TD, $dir) || die "Can not open directory: $!:";
my @FILES=grep(/\.csv/i, readdir TD);
print @FILES;
closedir(TD);

my $file = $FILES[0];
my $t = Data::Table::fromTSV($file,1) || die "Can not open file: $!:";
print $t->tsv
foreach my $file (@FILES) {
     my $t = Data::Table::fromCSV($file) || die "Can not open file: $!
+:";
     $t->addCol($file, "PCS Filename") || die "Can not add Column ";
     print $t->csv || die "Can not write output";
}
[download]

Comment on insert filename into multiple tab delimited files Select or Download Code

Replies are listed 'Best First'.
Re: insert filename into multiple tab delimited files by Ieronim (Friar) on Jul 05, 2006 at 18:08 UTC
If your file is simply tab-delimited, you can add the file column without using any external modules, simply adding tab and filename at the end of each line :) Like this: `{ local @ARGV = @FILES; local $^I = '.orig'; my %seen; while (<>) { (s/$/\tFilename/, (print), next) unless $seen{$ARGV}++; s/$/\t$ARGV/; print; } }` [download] This fragment of code also saves all original files with `'.orig'` extension.	[reply] [d/l] [select]
Re^2: insert filename into multiple tab delimited files by eagleroy (Initiate) on Jul 05, 2006 at 22:47 UTC
Thanks, I will give it a try	[reply]
Re^2: insert filename into multiple tab delimited files by eagleroy (Initiate) on Jul 06, 2006 at 13:29 UTC
I tried this and I got rows(no new column) interspersed within the data. And the rows had unreadable characters.	[reply]
Re: insert filename into multiple tab delimited files by jZed (Prior) on Jul 05, 2006 at 18:06 UTC
I'm a little confused as to your goal. "Insert a new column that contains the filename". Does that mean that you want to create a new column (field) and for each row (record) insert the filename as the value for that column? That doesn't make a lot of sense to me, but assuming that's what you want to do, just open the file, append a tab and the column name onto the first line and append a tab and the file name onto each succeeding line.	[reply]
Re^2: insert filename into multiple tab delimited files by eagleroy (Initiate) on Jul 05, 2006 at 22:46 UTC
Yes, I need to put the filename in the file because it will eventually land on a mainframe system without the original file name, but I need to be able to reference back to the original file name it had in the windows directory Thanks	[reply]
Re: insert filename into multiple tab delimited files by Anonymous Monk on Jul 05, 2006 at 23:08 UTC
Hi Just a thought, rather than adding the filename to each line, why not just open the file to append and add something like '<FileName> Name_Of_File' to the end. It should be easy to extract, and you are not messing about with the original data. As I say just a thought. J.C.	[reply]
Re^2: insert filename into multiple tab delimited files by eagleroy (Initiate) on Jul 06, 2006 at 03:39 UTC
Brilliant simplicity! - wish I had thought of it.	[reply]
Re^2: insert filename into multiple tab delimited files by eagleroy (Initiate) on Jul 06, 2006 at 13:26 UTC
Well, I tried it and I got unreadable characters appended to the end of the file. I then tried to use binmode, but that didn't resolve it.	[reply]
Re^2: insert filename into multiple tab delimited files by Anonymous Monk on Jul 08, 2006 at 00:25 UTC
Hi, I don't have Excel so I boshed together a Works Spreadsheet and saved it as a Tab delimited file `a b c d e f 2a 2b 2c 2d 2e 2f 3a 3b 3c 3d 3e 3f` [download] Then I ran the following : `use strict; use warnings; open JIM, ">>c:/perl/testa.txt" or die "Rats : $!"; print JIM "<FileName>\ttesta.txt\t4c\t4d\t4e\t4f\n"; close JIM;` [download] When I opened the file in Works again I got : `a b c d e f 2a 2b 2c 2d 2e 2f 3a 3b 3c 3d 3e 3f <FileName> testa.txt 4c 4d 4e 4f` [download] Does this help at all? J.C.	[reply] [d/l] [select]