比较2个csv文件并替换更新的值 [英] Compare 2 csv files and replace updated values

问题描述

我有2个csv文件，我正在寻找一种方法来使用特定列进行比较，一旦找到匹配，我需要从匹配的行中获取另一列的值，并将其放在相应的列

一个csv有

code> product_id，product_name，brand_name，price
其他具有 product_id，product_category，product_name，brand_name，price p>

我需要比较2个文件，找到具有匹配的 product_id 值的行，一旦发现我需要取文件1中的价格值，并将其放入文件2中匹配的记录的价格。

经过广泛的研究，我得出这样的结论，这可能是用powershell可以实现的。

有没有人对我有什么想法？谢谢您的时间。

解决方案

由于只是一次性动作。您可以在电子表格（google docs，excel，...）中打开csv文件，并执行VLOOKUP。很容易：

为了演示这个想象下面的电子表格，其中两个csv文件并排。首先从列A到B，第二列从列D到F

  | A | B | C | D | E | F 
  -  + ------------ + ------- + --- + ------------ + ---- -------------- + ------- 
 1 | product_id |价格| | product_id |产品类别|价格
 2 | 1 | 29.9 | | 2 |一些CAT 1 | = IFERROR（VLOOKUP（D2; A：B; 2; FALSE）;NULL）
 3 | 2 | 35.5 | | 3 |一些CAT 2 | = IFERROR（VLOOKUP（D3; A：B; 2; FALSE）;NULL）
  

VLOOKUP 函数将搜索区域A：B的第一列上的D2单元格的完全匹配，并返回值该区域的第二列。如果 VLOOKUP 失败， iferror 将返回NULL。

因此，在这种情况下，在单元格F2上，将在列A上查找产品ID2（单元格d2）。它在第3行中找到产品ID2，并返回价格35.5 A：B的行）。在计算所有行后，结果将是：

  | A | B | C | D | E | F 
  -  + ------------ + ------- + --- + ------------ + ---- -------------- + ------- 
 1 | product_id |价格| | product_id |产品类别|价格
 2 | 1 | 29.9 | | 2 |一些CAT 1 | 35.5 
 3 | 2 | 35.5 | | 3 |一些CAT 2 | NULL 
  

I have 2 csv files and I'm looking for a way to compare them using a specific column, and once a match is found I need to take the value of another column from the matched row and put it in the corresponding column of the other record.

I'll try to explain a little bit more.

One csv has product_id,product_name,brand_name,price the other has product_id,product_category,product_name,brand_name,price

I need to compare the 2 files by finding the rows that have a matching product_id value, and once found I need to take the price value from file 1 and put it to the matched record's price in file 2.

After extensive research I've come to the conclusion that this maybe achievable with powershell.

Does anyone have any ideas about how I could do that? Thank you for your time.

解决方案

Since is just a one time action. you could open the csv files in a spreadsheet (google docs, excel, ...) and do a VLOOKUP. Is easy:

To demonstrate this imagine the following spreadsheet where both csv files are side by side. First from column A to B and the second on column D to F

  |    A       |   B   | C |      D     |         E        |   F  
--+------------+-------+---+------------+------------------+-------
1 | product_id | price |   | product_id | product_category | price
2 |          1 |  29.9 |   |          2 |       SOME CAT 1 | =IFERROR(VLOOKUP(D2;A:B;2;FALSE); "NULL")
3 |          2 |  35.5 |   |          3 |       SOME CAT 2 | =IFERROR(VLOOKUP(D3;A:B;2;FALSE); "NULL")

The VLOOKUP function will search for an exact match of the value of D2 cell on the first column of the region A:B, and return the value from the second column of that region. The iferrorwill return NULL if the VLOOKUP fails.

So in this case on cell F2, will look for the product id "2" (Cell d2) on the column A. It founds the product id "2" in row 3, and return the price "35.5" (being the second row of the range A:B). After all rows have been calculated the result will be:

  |    A       |   B   | C |      D     |         E        |   F  
--+------------+-------+---+------------+------------------+-------
1 | product_id | price |   | product_id | product_category | price
2 |          1 |  29.9 |   |          2 |       SOME CAT 1 | 35.5
3 |          2 |  35.5 |   |          3 |       SOME CAT 2 | NULL

这篇关于比较2个csv文件并替换更新的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！