合并 CSV 文件，字段分隔符也出现在引号内

Question 1

显然，使用 csv 解析器会更好，但如果我们可以安全地假设

第一个字段永远不会包含逗号；
您只需要第一个文件中存在的 id（如果 id 位于 file2 或 file3 中而不是 file1 中，则忽略它）；
这些文件足够小，可以放入您的 RAM 中。

那么这个 Perl 方法应该可以工作：

#!/usr/bin/env perl 
use strict;

my %f;
## Read the files
while (<>) {
    ## remove trailing newlines
    chomp;
    ## Replace any commas within quotes with '|'.
    ## I am using a while loop to deal with multiple commas.
    while (s/\"([^"]*?),([^"]*?)\"/"$1|$2"/){}
    ## match the id and the rest.
    /^(.+?)(,.+)/; 
    ## The keys of the %f hash are the ids
    ## each line with the same id is appended to
    ## the current value of the key in the hash.
    $f{$1}.=$2; 
}
## Print the lines
foreach my $id (keys(%f)) {
    print "$id$f{$id}\n";
}

将上面的脚本保存为并按foo.pl如下方式运行：

perl foo.pl file1.csv file2.csv file3.csv

上面的脚本也可以写成一行：

perl -lne 'while(s/\"([^"]*?),([^"]*)\"/"$1|$2"/){} /^(.+?)(,.+)/; $k{$1}.=$2; 
           END{print "$_$k{$_}" for keys(%k)}' file1 file2 file3

Answer

显然，使用 csv 解析器会更好，但如果我们可以安全地假设

第一个字段永远不会包含逗号；
您只需要第一个文件中存在的 id（如果 id 位于 file2 或 file3 中而不是 file1 中，则忽略它）；
这些文件足够小，可以放入您的 RAM 中。

那么这个 Perl 方法应该可以工作：

#!/usr/bin/env perl 
use strict;

my %f;
## Read the files
while (<>) {
    ## remove trailing newlines
    chomp;
    ## Replace any commas within quotes with '|'.
    ## I am using a while loop to deal with multiple commas.
    while (s/\"([^"]*?),([^"]*?)\"/"$1|$2"/){}
    ## match the id and the rest.
    /^(.+?)(,.+)/; 
    ## The keys of the %f hash are the ids
    ## each line with the same id is appended to
    ## the current value of the key in the hash.
    $f{$1}.=$2; 
}
## Print the lines
foreach my $id (keys(%f)) {
    print "$id$f{$id}\n";
}

将上面的脚本保存为并按foo.pl如下方式运行：

perl foo.pl file1.csv file2.csv file3.csv

上面的脚本也可以写成一行：

perl -lne 'while(s/\"([^"]*?),([^"]*)\"/"$1|$2"/){} /^(.+?)(,.+)/; $k{$1}.=$2; 
           END{print "$_$k{$_}" for keys(%k)}' file1 file2 file3

Question 2

TXR语言：

@(do
   (defun csv-parse (str)
     (let ((toks (tok-str str #/[^\s,][^,]+[^\s,]|"[^"]*"|[^\s,]/)))
       [mapcar (do let ((l (match-regex @1 #/".*"/)))
                     (if (eql l (length @1))
                       [@1 1..-1] @1)) toks]))

   (defun csv-format (list)
     (cat-str (mapcar (do if (find #\, @1) `"@1"` @1) list) ", "))

   (defun join-recs (recs-left recs-right)
     (append-each ((l recs-left))
       (collect-each ((r recs-right))
         (append l r))))

   (let ((hashes (collect-each ((arg *args*))
                   (let ((stream (open-file arg)))
                     [group-by first [mapcar csv-parse (gun (get-line stream))]
                               :equal-based]))))
     (when hashes
       (let ((joined (reduce-left (op hash-isec @1 @2 join-recs) hashes)))
         (dohash (key recs joined)
           (each ((rec recs))
             (put-line (csv-format rec))))))))

样本数据。

注意：键 3792318 在第三个文件中出现两次，因此我们预计该键的连接输出中有两行。

注：数据不需要排序；哈希用于连接。

$ for x in csv* ; do echo "File $x:" ; cat $x ; done
File csv1:
3792318, 2014-07-15 00:00:00, "A, B"
3792319, 2014-07-16 00:00:01, "B, C"
3792320, 2014-07-17 00:00:02, "D, E"
File csv2:
3792319, 2014-07-15 00:02:00, "X, Y"
3792320, 2014-07-11 00:03:00, "S, T"
3792318, 2014-07-16 00:02:01, "W, Z"
File csv3:
3792319, 2014-07-10 00:04:00, "M"
3792320, 2014-07-09 00:06:00, "N"
3792318, 2014-07-05 00:07:01, "P"
3792318, 2014-07-16 00:08:01, "Q"

跑步：

$ txr join.txr csv1 csv2 csv3
3792319, 2014-07-16 00:00:01, "B, C", 3792319, 2014-07-15 00:02:00, "X, Y", 3792319, 2014-07-10 00:04:00, M
3792318, 2014-07-15 00:00:00, "A, B", 3792318, 2014-07-16 00:02:01, "W, Z", 3792318, 2014-07-05 00:07:01, P
3792318, 2014-07-15 00:00:00, "A, B", 3792318, 2014-07-16 00:02:01, "W, Z", 3792318, 2014-07-16 00:08:01, Q
3792320, 2014-07-17 00:00:02, "D, E", 3792320, 2014-07-11 00:03:00, "S, T", 3792320, 2014-07-09 00:06:00, N

一个更“正确”的csv-parse函数是：

   ;; Include the comma separators as tokens; then parse the token
   ;; list, recognizing consecutive comma tokens as an empty field,
   ;; and stripping leading/trailing whitespace and quotes.
   (defun csv-parse (str)
     (labels ((clean (str)
                (set str (trim-str str))
                (if (and (= [str 0] #\")
                         (= [str -1] #\"))
                  [str 1..-1]
                  str))
              (post-process (tokens)
                (tree-case tokens
                  ((tok sep . rest)
                   (if (equal tok ",")
                     ^("" ,*(post-process (cons sep rest)))
                     ^(,(clean tok) ,*(post-process rest))))
                  ((tok . rest)
                   (if (equal tok ",")
                     '("")
                     ^(,(clean tok)))))))
       (post-process (tok-str str #/[^,]+|"[^"]*"|,/))))

Answer