如何合并2 CSV文件+所有内容+ cygwin的/庆典/ AWK / SED /粘贴 [英] How do I combine 2 csv files + all content + cygwin/bash/awk/sed/paste

问题描述

如何合并2 CSV文件（finle1.csv和file2.csv）？我已经探索的awk / sed的/粘贴，但它是超越我。

How do I combine 2 CSV files(finle1.csv and file2.csv)? I have explored awk/sed/paste but it is beyond me.

file1.csv

file1.csv

Time,Object,Integrity,KPI 1-A Name A unit(unit/s),KPI 2-A Name B unit(unit/s)
2014-06-04 11:00,ObjectA,100%,0.0316,0.0012
2014-06-04 21:00,ObjectB,100%,40.0332,7.2601

file2.csv

file2.csv

Time,Object,Integrity,KPI 1-C Name A unit(unit),KPI 1-D Name A unit(unit)
2014-06-04,ObjectA,100%,0.024,0.0014
2014-06-04,ObjectB,100%,60.6176,29.0913

whatIwant.csv

whatIwant.csv

Time,Object,Integrity,KPI 1-A Name A unit(unit/s),KPI 2-A Name B unit(unit/s),Time,Object,Integrity,KPI 1-C Name A unit(unit),KPI 1-D Name A unit(unit)
2014-06-04 11:00,ObjectA,100%,0.0316,0.0012,2014-06-04,ObjectA,100%,0.024,0.0014
2014-06-04 21:00,ObjectB,100%,40.0332,7.2601,2014-06-04,ObjectB,100%,60.6176,29.0913

请注意：我假设时间，对象将相应地排队

Note: I am assuming Time, Object will line up accordingly.

这将是N行。

另外列的数量很可能会增加在每个文件

Also the number of columns will possibly increase in each file.

我可能不得不删除第二个时间，对象，从Integrity列 whatIwant.csv 但以后可以做到这一点。

I may have to remove the 2nd Time,Object,Integrity columns from the whatIwant.csv but can do this later.

推荐答案

使用 AWK ：

awk -F, 'NR==FNR{a[$2]=$0;next}$2 in a{ print a[$2],$4, $5 }' OFS=, file1.csv file2.csv
Time,Object,Integrity,KPI 1-A Name A unit(unit/s),KPI 2-A Name B unit(unit/s),KPI 1-C Name A unit(unit),KPI 1-D Name A unit(unit)
2014-06-04 11:00,ObjectA,100%,0.0316,0.0012,0.024,0.0014
2014-06-04 21:00,ObjectB,100%,40.0332,7.2601,60.6176,29.0913

使用加入：

join -t, -j 2 -o 1.1 1.2 1.3 1.4 1.5 2.4 2.5 file1.csv file2.csv
Time,Object,Integrity,KPI 1-A Name A unit(unit/s),KPI 2-A Name B unit(unit/s),KPI 1-C Name A unit(unit),KPI 1-D Name A unit(unit)
2014-06-04 11:00,ObjectA,100%,0.0316,0.0012,0.024,0.0014
2014-06-04 21:00,ObjectB,100%,40.0332,7.2601,60.6176,29.0913

更新：

要加入日期和对象，你可以使用 AWK 为加入只加入1列。

To join on date and object, you can use awk as join only joins on 1 column.

awk -F, 'NR==FNR{sub(/ .*/,"",$1);map[$1,$2]=$0;next}(($1,$2) in map){print map[$1,$2],$4,$5}' OFS=, f1 f2
Time,Object,Integrity,KPI 1-A Name A unit(unit/s),KPI 2-A Name B unit(unit/s),KPI 1-C Name A unit(unit),KPI 1-D Name A unit(unit)
2014-06-04,ObjectA,100%,0.0316,0.0012,0.024,0.0014
2014-06-04,ObjectB,100%,40.0332,7.2601,60.6176,29.0913

这篇关于如何合并2 CSV文件+所有内容+ cygwin的/庆典/ AWK / SED /粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！