重定向的URL列表比较

Question

我得到了2个CSV文件，每个文件中都包含了3000+ URL。

我的任务是创建一个从“旧站点”到“新站点”的.htaccess“重定向”块，与其进行手工比较，不如尝试使用bash/python脚本，或者将它们导入MySQL进行比较。

因此，在Bash中，我尝试了以下代码：

#!/bin/bash

awk 'BEGIN{FS=OFS="/"} {gsub(/\/$/, ""); $NF=tolower($NF)} NR==FNR{a[$NF]=$0; next} $NF in a {print a[$NF] " " $0 > "combined.csv"}' oldsite.csv newsite.csv

然而，它返回给我一个空的"combined.csv"，所以我想也许是“Python”.但是唉，我对Python知之甚少，所以我想MySQL.如果我只是将每个CSV导入到一个新表中，我可以运行一个比较SQL语句并将结果转储到一个2列的新表中.遗憾的是，我不知道从哪里开始比较，用LIKE比较语句来计算，但我想知道的是什么是“最好的”(意思是最精确的比较)方法。如果是Python，怎么做？

CSV样品

新网址

"new-url"
"/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

旧网址

"old-url"
"/product/0903-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Heart-Dangle-in-14K-Gold/"
"/product/0954-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Butterfly-Dangle-in-14K-Gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/"

期望组合

"old-url", "new-url"
"/product/0903-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Heart-Dangle-in-14K-Gold/", "/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/0954-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Butterfly-Dangle-in-14K-Gold/", "/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/", "/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

Answer 1

正如我们在注释线程中所发现的，您需要将数据转换成可以在awk/unix中处理的数据，方法是删除MS-DOS行尾的\r部分。

dos2unix file

它将file行结束从\r\n转换为\n。请注意，您可以使用多个文件名调用dos2unix，并且将处理每个文件，即

dos2unix old.csv new.csv many_more ...

下面是您修改后的代码，它将为“新”文件中的不匹配记录创建一个单独的文件。我发现唯一需要的修正是将最终输出更改为包含, char，所以print a[$NF] "," $0。

#!/bin/bash 
awk 'BEGIN{FS=OFS="/"}
  { gsub(/\/$/, "")
    # print "#dbg: FILENAME="FILENAME "\tNR="NR "\tFNR="FNR
    $NF=tolower($NF)
  }
  NR==FNR{
    a[$NF]=$0; next
  }
  {
    if ($NF in a) {
      print  a[$NF] "," $0  > "combined.csv"
    }
    else {
      print  a[$NF] "," $0  > "unmatched.csv"
    }
  }
  ' oldsite.csv newsite.csv

输出

cat combined.csv

"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

cat unmatched.csv
,"new-url"

IHTH