-5

I have two csv files:

success.csv

"ID","Legacy ID","Field1", "Field2"

1,1111,google,news
2,2222,yahoo,news

error.csv

"Legacy ID","Field1","Field2","Message"

3333,aol,news,"Failed to upload data"    
4444,cbs,news,"Alredy exists"

How can I merge these two files and create a new file as shown below? I cannot use indexes as the size and order of fields will keep changing.

results.csv

"ID","Legacy ID","Message"

1,1111,""
2,2222,""
"",3333,"Failed to upload data"
"",4444,"Alredy exists"

The requirement is to create the results.csv file with only three columns from both success.csv and error.csv.

  1. If the row is successfully loaded then we get "ID" in the success file as the first column with no "Message" Column

  2. If it's failed we get an error in the "Message" field which is always the last field in the file. In this case the "ID" will be empty.

Read the values from success.csv as follows:

awk '{print $1, $2;}' success.csv

Read the values from error.csv as follows:

awk '{print $1, NF;}' error.csv

I am not able to figure out a way to combine both the statements and write the result to a file.

  • Please edit your question and explain how you get to results.csv from the first two files. You seem to be removing some information, why and how can we know? Also, please show us what you have tried and how it has failed. We're happy to help you but we won't do all the work for you. – terdon Jan 17 '15 at 18:30
  • 1
    CSV files can have (quoted) fields with newlines, you should use a real library as available for Python, Perl, etc. to parse these files and extract the fields you need. – Anthon Jan 17 '15 at 20:58
  • I guess there is a typo in awk '{print $1, NF;}' error.csv. That shall probably be awk '{print $1, $NF;}' error.csv. And in both cases you need awk -F, ... – Hauke Laging Jan 18 '15 at 17:16
1

The question is not very clear, but I believe you are looking for the following construct:

awk 'BEGIN{FS=OFS=","}NR==FNR{print $1,$2,"\"\"";next}{print "\"\"",$1,$NF}' success.csv error.csv

Explanation:

  • first of all at the very beginning we set field separator (FS) and output field separator (OFS) to ,
  • we process both files in one go, but we check which one is processed. The first one is processed when current row number is equal to current row number in current file (NR==FNR).
  • so for first file (success.csv) we print $1,$2,"\"\"" while for second (error.csv) we print "\"\"",$1,$NF.

The result is:

1,1111,""
2,2222,""
"",3333,"Failed to upload data"    
"",4444,"Alredy exists"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.