How can I combine files into one CSV file?

Question

If I have one file FOO_1.txt that contains:

FOOA

FOOB

FOOC

FOOD

...

and a lots of other files FOO_files.txt. Each of them contains:

1110000000...

one line that contain 0 or 1 as the number of FOO1 values (fooa,foob, ...)

Now I want to combine them to one file FOO_RES.csv that will have the following format:

FOOA,1,0,0,0,0,0,0...

FOOB,1,0,0,0,0,0,0...

FOOC,1,0,0,0,1,0,0...

FOOD,0,0,0,0,0,0,0...

...

What is the simple & elegant way to conduct that (with hash & arrays -> $hash{$key} = \@data ) ?

Thanks a lot for any help !

Yohad

Answer 1

If I understand correctly your first file is your key order file, and the remaining files each contain a byte per key in the same order. You want a composite file of those keys with each of their data bytes listed together.

In this case you should open all the files simultaneously. Read one key from the key order file, read one byte from each of the data files. Output everything as you read it to you final file. Repeat for each key.

Answer 2

You don't really need to use a hash. My Perl is a little rusty, so syntax may be off a bit, but basically do this:

open KEYFILE , "foo_1.txt" or die "cannot open foo_1 for writing";
open VALFILE , "foo_files.txt" or die "cannot open foo_files for writing";
open OUTFILE , ">foo_out.txt"or die "cannot open foo_out for writing";

my %output;
while (<KEYFILE>) {
    my $key = $_;
    my $val = <VALFILE>;
    my $arrVal = split(//,$val);

    $output{$key} = $arrVal;
    print OUTFILE $key."," . join(",", $arrVal)
}

Edit: Syntax check OK

Comment by Sinan: @Byron, it really bothers me that your first sentence says the OP does not need a hash yet your code has %output which seems to serve no purpose. For reference, the following is a less verbose way of doing the same thing.

#!/usr/bin/perl

use strict;
use warnings;

use autodie qw(:file :io);

open my $KEYFILE, '<', "foo_1.txt";
open my $VALFILE, '<', "foo_files.txt";
open my $OUTFILE, '>', "foo_out.txt";

while (my $key = <$KEYFILE>) {
    chomp $key;
    print $OUTFILE join(q{,}, $key, split //, <$VALFILE> ), "\n";
}
__END__

Answer 3

Your specifications aren't clear. You couldn't have a "lots of other files" named FOO_files.txt, because it's only one name. So I'm going to take this as the files-with-data + filelist pattern. In this case, there are files named FOO*.txt, each containing "[01]+\n".

Thus the idea is to process all the files in the filelist file and to insert them all into a result file FOO_RES.csv, comma-delimited.

use strict;
use warnings;
use English qw<$OS_ERROR>;
use IO::Handle;

open my $foos, '<', 'FOO_1.txt'
    or die "I'm dead: $OS_ERROR";
@ARGV = sort map { chomp; "$_.txt" } <$foos>;
$foos->close;

open my $foo_csv, '>', 'FOO_RES.csv'
    or die "I'm dead: $OS_ERROR";

while ( my $line = <> ) { 
    my ( $foo_name ) = ( $ARGV =~ /(.*)\.txt$/ );
    $foo_csv->print( join( ',', $foo_name, split //, $line ), "\n" );
}

$foo_csv->close;

Answer 4

It looks like you have many foo_files that have 1 line in them, something like:

1110000000

Which stands for

fooa=1
foob=1
fooc=1
food=0
fooe=0
foof=0
foog=0
fooh=0
fooi=0
fooj=0

And it looks like your foo_res is just a summation of those values? In that case, you don't need a hash of arrays, but just a hash.

my @foo_files = (); #NOT SURE HOW YOU POPULATE THIS ONE
my @foo_keys = qw(a b c d e f g h i j);
my %foo_hash = map{ ( $_, 0 ) } @foo_keys; # initialize hash
foreach my $foo_file ( @foo_files ) {
  open( my $FOO, "<", $foo_file) || die "Cannot open $foo_file\n";
  my $line = <$FOO>;
  close( $FOO );
  chomp($line);
  my @foo_values = split(//, $line);
  foreach my $indx ( 0 .. $#foo_keys ) {
    last if ( ! $foo_values[ $indx ] ); # or some kind of error checking if the input file doesn't have all the values
    $foo_hash{ $foo_keys[$indx] } += $foo_values[ $indx ];
  }
}

It's pretty hard to understand what you are asking for, but maybe this helps?

Answer 5

If you can't describe a your data and your desired result clearly, there is no way that you will be able to code it--taking on a simple project is a good way to get started using a new language.

Allow me to present a simple method you can use to churn out code in any language, whether you know it or not. This method only works for smallish projects. You'll need to actually plan ahead for larger projects.

How to write a program:

1. Open up your text editor and write down what data you have. Make each line a comment
2. Describe your desired results.
3. Start describing the steps needed to change your data into the desired form.

Numbers 1 & 2 completed:

#!/usr/bin perl
use strict;
use warnings;

# Read data from multiple files and combine it into one file.
# Source files:
#    Field definitions: has a list of field names, one per line.
#    Data files:  
#      * Each data file has a string of digits.
#      * There is a one-to-one relationship between the digits in the data file and the fields in the field defs file.
# 
# Results File:
# * The results file is a CSV file.
# * Each field will have one row in the CSV file.
# * The first column will contain the name of the field represented by the row.
# * Subsequent values in the row will be derived from the data files.
# * The order of subsequent fields will be based on the order files are read.
# * However, each column (2-X) must represent the data from one data file.

Now that you know what you have, and where you need to go, you can flesh out what the program needs to do to get you there - this is step 3:

You know you need to have the list of fields, so get that first:

# Get a list of fields.
#   Read the field definitions file into an array.

Since it is easiest to write CSV in a row oriented fashion, you will need to process all your files before generating each row. So you'll need someplace to store the data.

# Create a variable to store the data structure.

Now we read the data files:

# Get a list of data files to parse
# Iterate over list

# For each data file:
#    Read the string of digits.
#    Assign each digit to its field.
#    Store data for later use.

We've got all the data in memory, now write the output:

# Write the CSV file.
# Open a file handle.

# Iterate over list of fields
# For each field
#   Get field name and list of values.
#   Create a string - comma separated string with field name and values  
#   Write string to file handle

# close file handle.

Now you can start converting comments into code. You could have anywhere from 1 to 100 lines of code for each comment. You may find that something you need to do is very complex and you don't want to take it on at the moment. Make a dummy subroutine to handle the complex task, and ignore it until you have everything else done. Now you can solve that complex, thorny sub-problem on it's own.

Since you are just learning Perl, you'll need to hit the docs to find out how to do each of the subtasks represented by the comments you've written. The best resource for this kind of work is the list of functions by category in perlfunc. The Perl syntax guide will come in handy too. Since you'll need to work with a complex data structure, you'll also want to read from the Data Structures Cookbook.

You may be wondering how the heck you should know which perldoc pages you should be reading for a given problem. An article on Perlmonks titled How to RTFM provides a nice introduction to the documentation and how to use it.

The great thing, is if you get stuck, you have some code to share when you ask for help.