Merge CSV files and create a column with the filename from the original file

Hello everyone!!

I am not completely new to shell script but I havent been able to find the answer to my problem and I'm sure there are some smart brains here up for the challenge :D.

I have several CSV files that I need to combine into one, but I also need to know where each row came from. Let me give you an example.

Say I have file1.csv with the content:

1,a
2,b

And file2.csv:

3,c
4,d

I would like to have a file named result.csv, with the content:

1,a,file1.csv
2,b,file1.csv
3,c,file2.csv
4,d,file2.csv

The files I want to merge have different number of rows, but they all have the same number of columns, so I just need to create one new column in the result file. Any ideas??

Thank you beforehand.

/Fran

Something like this:

# ls file*csv
file1.csv  file2.csv

# awk '{print $0","FILENAME}' file*csv >  result.csv

Thank you Klashxx!!

That was a step in the right direction. Actually if I run the command with the example files as I posted in the example it works without any problems. I must have oversimplified my data because when I run it on the real thing the result file is huge, 2,3 GB although the source file are less than 100 Kb altogether. I tried to open with openoffice and in the preview window before importing I can see that there is a column with the file name which is great, then some stuff gets scrambled and repeated.

Here is a sample code of one file:

"Innography URL",Assignee,"Publication Number","Publication Country","Publication Date",Source,Title,Abstract,"Application Number",Citations,"Est. Expiration Date","Family ID","File Date","First Claim","All Claims",Inventors,"First IP Classification","All IPC Classifications","Kind Code","Priority Date","Normalized Assignee","Number of Claims","Number of Backward References","Number of Forward References","Original Assignee",Strength,"Ultimate Parent","US Classification"
"=Hyperlink(""https://app.innography.com/patents/14530190"",""Innography Link"")","Biora Ab",CA2226570,CA,1997-01-30,"CA Patents","Enamel matrix related polypeptide","The invention relates to novel nucleic acid fragments encoding polypeptides which are capable of mediating contact between enamel and cell surface. The invention also relates to expression vectors containing the nucleic acid fragments according to the invention for production of the protein organisms containing said expression vector methods for producing the polypeptide compositions comprising the polypeptides antibodies or antibody fragments recognizing the polypeptides and methods for treating various hard tissue diseases or disorders.",CA22265," ",2006-12-18,27221209,1996-06-26,,,"Wurtz, Tilmann  | Hammarstr, M Lars | Slaby, Ivan  | Cerny, Radim  | Fong, Cheng Dan",C12N01500900,"C12N 15/09|A61K 31/00|A61K 38/00|A61P 1/00|A61P 1/02|C07K 14/435|C07K 14/47|C07K 14/78|C12N 1/15|C12N 1/19|C12N 1/21|C12N 5/10|C12P 21/02",A1,1995-07-13,"Biora Ab",0,0,0,"BIORA AB","0th-10th Percentile",,435317100

As you can see one column contains an "abstract", the file may have all sort of special characters, could that have been the cause why it didnt work?

Thank you again for your help, really appreciated!

Ok I figured out it is because the file contains commas inside quoted fields. And looking out there seems like a pain to use awk then. What about Perl, anyone know how to use it to solve the problem? Thanks!

A Perl:

#!/usr/bin/perl
my $result_file=shift;
my $pattern=shift;
my (@file_list) = glob $pattern;

open( OUT, ">$result_file") or die "Can't open '$result_file':$!";
foreach my $in_file (@file_list) { 
   open(IN, "$in_file"); 
   while (<IN>) {
      chomp;
      print OUT $_ . ',' . $in_file . "\n" ;
      }
   close (IN);
   }

close (OUT);

Usage:

ex.pl result.txt 'file*csv'

Thank you again Klashxx!

That did not work either unfortunately, it also produces a huge text file. I noticed that there are also \n inside the fields and they were not rendered in the sample code I posted above, can this cause the problem?

You can download a sample of the file to see it yourself at www dot fransanchezoria.com/sample.csv

By the way hablas Espa�ol?

Thanks a million!

---------- Post updated at 09:32 AM ---------- Previous update was at 09:07 AM ----------

Just got the solution thanks to a program:

csvfix file_info *.csv > Result.csv

Glad you solved it Fran, anyway Using the data you provided works fine for me.
Must be an env issue , what OS are you running ?

PD: i'm spanish but the use of english is mandatory.

Nos vemos ..

Hi Klashxx,

CSVfix was working great, but unfortunately the data contained many special characters and it ended up with a few messed rows. I gave your script a go and it works fine, I only have a couple of issues:

1. For some reason it only opens and output one file (100_LARSSON_KRISTIAN.csv) although there are over 300 in the directory, all with the format number_surname_firstname.csv. I used
   '/home/fran/Desktop/ex.pl' '/home/fran/Desktop/chalmers test.csv' /home/fran/Desktop/chalmers/*.csv

2. Path name is displayed after /n, which is great, but many fields contains embedded new lines, which complicate things. Do you know how to output the name only in the last /n? The difference between the last /n and the rest is that is not enclosed by "" (perhaps use CSV_XS? I'm trying to figure out how to use it)

I'm running ubuntu by the way.

Many thanks!