使用AWK要在2 CSV文件查找数据 [英] Using AWK To Lookup Data in 2 CSV Files

问题描述

我一直在尝试了3天，试图了解如何使用AWK给我，我要找的结果。让我先说我最好不是一个程序员，pretty新手开始。我有看约30不同的方式做我想做什么，但他们要么没有精确匹配，我有改变的事情，他们不工作，或者他们不从一开始工作。

I've been trying for 3 days to try and understand how to use AWK to give me the results I'm looking for. Let me start by saying I'm not a programmer, pretty novice at best. I've look at about 30 different ways to do what I want but they're either not exact matches and I have to change things and they don't work or they don't work from the beginning.

目标：以2 CSV文件导出的数据进行比较，以另一个CVS文件。我要看看是否在任一列2或第3列在文件1（标题的sampleData）的MAC地址显示栏2文件2（标题为sampleLeases），然后从文件2，相匹配的MAC地址打印第1列，并从文件2列1

Objective: To compare 2 CSV files and export data to another CVS file. I need to see if any Mac addresses in either Column 2 or Column 3 in file 1 (titled sampleData) show up Column 2 in file 2 (titled sampleLeases),and then print Column 1 from file 2, the mac address that matches, and Column 1 from file 2.

我的问题可以从我在AWK关联数组的误解干，但我想了解在旅途中，我需要帮助。

My issue could stem from my misunderstanding of associative arrays in AWK, but I'm trying to learn on the go and I need help.

我已经试过这其中包括许多other.Sorry为长张贴，我只是想尽可能彻底。

I've tried this amongst many other.Sorry for the long posting, I just wanted to be as thorough as possible.

awk 'BEGIN{FS=OFS=","} FNR==NR {a[$1]=$2; next } {if ($2 in a) print $2,a[$1]}' sampleLeases.csv sampleData.csv 

下面是数据样本集合

Below is a sample set of data.

sampleLeases.csv

10.1.2.3,00:11:22:33:44:55
10.1.2.4,00:11:22:33:44:56
10.1.2.5,00:11:22:33:44:57
10.1.2.6,00:11:22:33:44:58
10.1.2.7,00:11:22:33:44:59
10.1.2.8,00:11:22:33:44:60
10.1.2.9,00:11:22:33:44:61
10.1.2.10,00:11:22:33:44:62
10.1.2.11,00:11:22:33:44:63
10.1.2.12,00:11:22:33:44:64
10.1.2.13,00:11:22:33:44:65
10.1.2.14,00:11:22:33:44:66
10.1.2.15,00:11:22:33:44:67
10.1.2.16,00:11:22:33:44:68
10.1.2.17,00:11:22:33:44:69
10.1.2.18,00:11:22:33:44:70
10.1.2.19,00:11:22:33:44:71
10.1.2.20,00:11:22:33:44:72
10.1.2.21,00:11:22:33:44:73

sampleData.csv

 comp. name,mac address,mac address 2,os version,S/N,dept.,building,asset number
 bldg1-rm200-01,00:11:22:33:44:55,22:33:44:55:66:77,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-02,11:22:33:44:56:44,00:11:22:33:44:56,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-03,00:11:22:33:44:57,22:33:44:55:66:79,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-04,00:11:22:33:44:58,22:33:44:55:66:80,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-05,11:22:33:44:59:45,00:11:22:33:44:60,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-06,00:11:22:33:44:61,22:33:44:55:66:82,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-07,11:22:33:44:61:45,00:11:22:33:44:62,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-08,00:11:22:33:44:63,22:33:44:55:66:84,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-09,00:11:22:33:44:64,22:33:44:55:66:85,Osversion,98745562,cart,main,6587521336
 bldg1-rm200-10,00:11:22:33:44:65,22:33:44:55:66:86,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-11,11:22:33:44:65:56,00:11:22:33:44:66,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-12,11:22:33:44:66:56,00:11:22:33:44:67,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-13,11:22:33:44:67:20,00:11:22:33:44:68,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-14,11:22:33:44:68:34,00:11:22:33:44:69,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-15,00:11:22:33:44:70,22:33:44:55:66:91,Osversion,98745562,lab,main,6587521336
 bldg1-rm200-16,11:22:33:44:70:59,00:11:22:33:44:71,Osversion,98745562,lab,main,6587521336

所需的输出格式

 bldg1-rm200-01,00:11:22:33:44:55,10.1.2.3

感谢。

编辑：

我试过用karkafa和Xorg提出的这两种方法，我没有收到任何输出。我一直在使用Linux Mint的17.2拉斐拉来测试我的awk脚本连接显示的情况下我的awk的版本（GNU awk中实际上）和Linux内核版本的截图是relevant.I'm。 截图显示没有AWK输出。

I've tried the both methods suggested by karkafa and Xorg and I'm not receiving any output. I've attached a screenshot showing my Awk version(GNU Awk actually) and Linux kernel version in case it's relevant.I'm using Linux Mint 17.2 Rafaela to test out my AWK script. Screenshot showing no AWK output.

推荐答案

awk来救援

 $awk -F, -v OFS=, 'NR==FNR{a[$3]=a[$2]=$1;next} $2 in a{print a[$2],$2,$1}' file2 file1

将打印

 bldg1-rm200-01,00:11:22:33:44:55,10.1.2.3
 bldg1-rm200-02,00:11:22:33:44:56,10.1.2.4
 bldg1-rm200-03,00:11:22:33:44:57,10.1.2.5
 bldg1-rm200-04,00:11:22:33:44:58,10.1.2.6
 bldg1-rm200-05,00:11:22:33:44:60,10.1.2.8
 bldg1-rm200-06,00:11:22:33:44:61,10.1.2.9
 bldg1-rm200-07,00:11:22:33:44:62,10.1.2.10
 bldg1-rm200-08,00:11:22:33:44:63,10.1.2.11
 bldg1-rm200-09,00:11:22:33:44:64,10.1.2.12
 bldg1-rm200-10,00:11:22:33:44:65,10.1.2.13
 bldg1-rm200-11,00:11:22:33:44:66,10.1.2.14
 bldg1-rm200-12,00:11:22:33:44:67,10.1.2.15
 bldg1-rm200-13,00:11:22:33:44:68,10.1.2.16
 bldg1-rm200-14,00:11:22:33:44:69,10.1.2.17
 bldg1-rm200-15,00:11:22:33:44:70,10.1.2.18
 bldg1-rm200-16,00:11:22:33:44:71,10.1.2.19

这篇关于使用AWK要在2 CSV文件查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！