![合并 CSV 文件,字段分隔符也出现在引号内](https://linux22.com/image/39971/%E5%90%88%E5%B9%B6%20CSV%20%E6%96%87%E4%BB%B6%EF%BC%8C%E5%AD%97%E6%AE%B5%E5%88%86%E9%9A%94%E7%AC%A6%E4%B9%9F%E5%87%BA%E7%8E%B0%E5%9C%A8%E5%BC%95%E5%8F%B7%E5%86%85.png)
我有 3 个 csv 文件,我想按第一列(id 列)加入
每个文件都有相同的 3 列。
行示例:
id | timestamp | Name
3792318, 2014-07-15 00:00:00, "A, B"
当我加入 3 个 csv 文件时
join -t, <(join -t, csv1 csv2) csv3 > out.csv
该out.csv
文件每行的列数不同,可能是因为分隔符是逗号,而某些行(如上面的示例)在单元格内容中包含逗号。
答案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
答案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 #/[^,]+|"[^"]*"|,/))))