CSVUtils/csv_lookup_fields.pm

filter: Statementstmt 95.9% 95 / 99 Branchbran 58.8% 20 / 34 Conditioncond 33.3% 1 / 3 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 100% 8 / 8 Podpod n/a totaltotal 84.9% 124 / 146 CC 24 SCAR 23.8
CC
24
Cov
85%
__ANON__
17.0
__ANON__
8.4
__ANON__
2.0
CRAP 10.8
« CSVUtils/csv_pick_rows.pm ↑ Summary CSVUtils/csv_add_fields.pm »
1 package App::CSVUtils::csv_lookup_fields;
2  
3 2 ▶use 5.010001;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 2 our $AUTHORITY = 'cpan:PERLANCAR'; # AUTHORITY
8 2 our $DATE = '2026-07-09'; # DATE
9 2 our $DIST = 'App-CSVUtils'; # DIST
10 2 our $VERSION = '1.038'; # VERSION
11  
12 2 ▶use App::CSVUtils qw(
Subroutine BEGIN: called CC=1 SCAR=0.0
13 gen_csv_util
14 2 );
15  
16 gen_csv_util(
17 name => 'csv_lookup_fields',
18 summary => 'Fill fields of a CSV file from another',
19 description => <<'MARKDOWN',
20  
21 Example input:
22  
23 # report.csv
24 client_id,followup_staff,followup_note,client_email,client_phone
25 101,Jerry,not renewing,
26 299,Jerry,still thinking over,
27 734,Elaine,renewing,
28  
29 # clients.csv
30 id,name,email,phone
31 101,Andy,andy@example.com,555-2983
32 102,Bob,bob@acme.example.com,555-2523
33 299,Cindy,cindy@example.com,555-7892
34 400,Derek,derek@example.com,555-9018
35 701,Edward,edward@example.com,555-5833
36 734,Felipe,felipe@example.com,555-9067
37  
38 To fill up the `client_email` and `client_phone` fields of `report.csv` from
39 `clients.csv`, we can use:
40  
41 % csv-lookup-fields report.csv clients.csv --lookup-fields client_id:id --fill-fields client_email:email,client_phone:phone
42  
43 The result will be:
44  
45 client_id,followup_staff,followup_note,client_email,client_phone
46 101,Jerry,not renewing,andy@example.com,555-2983
47 299,Jerry,still thinking over,cindy@example.com,555-7892
48 734,Elaine,renewing,felipe@example.com,555-9067
49  
50 MARKDOWN
51 add_args => {
52 ignore_case => {
53 schema => 'bool*',
54 cmdline_aliases => {ci=>{}, i=>{}},
55 },
56 fill_fields => {
57 schema => ['str*'],
58 req => 1,
59 },
60 lookup_fields => {
61 schema => ['str*'],
62 req => 1,
63 },
64 count => {
65 summary => 'Do not output rows, just report the number of rows filled',
66 schema => 'bool*',
67 cmdline_aliases => {c=>{}},
68 },
69 },
70  
71 reads_multiple_csv => 1,
72  
73 tags => ['category:templating'],
74  
75 1 ▶ on_begin => sub {
Subroutine __ANON__: called CC=8 SCAR=21.2
76 1 my $r = shift;
77  
78 # check arguments
79 1 ▶ @{ $r->{util_args}{input_filenames} } == 2
Branch: 1 branch
BranchTrueFalseTotal
@{$r->{"util_args"}{"input_filenames"};} == 2 or die [400, "Please specify exactly 2 files: target and source"] 1 0 1
80 or die [400, "Please specify exactly 2 files: target and source"];
81  
82 1 my @lookup_fields; # elem = [fieldname-in-target, fieldname-in-source]
83 {
84 1 my @ff = ref($r->{util_args}{lookup_fields}) eq 'ARRAY' ?
85 0 ▶ @{$r->{util_args}{lookup_fields}} : split(/,/, $r->{util_args}{lookup_fields});
Branch: 1 branch
BranchTrueFalseTotal
ref $r->{'util_args'}{'lookup_fields'} eq 'ARRAY' ? : 0 1 1
86 1 for my $field_idx (0..$#ff) {
87 1 my @ff2 = split /:/, $ff[$field_idx], 2;
88 1 ▶ if (@ff2 < 2) {
Branch: 1 branch
BranchTrueFalseTotal
if (@ff2 < 2) 0 1 1
89 0 $ff2[1] = $ff2[0];
90 }
91 1 $lookup_fields[$field_idx] = \@ff2;
92 }
93 }
94  
95 1 my %fill_fields; # key=fieldname-in-target, val=fieldname-in-source
96 {
97 1 my @ff = ref($r->{util_args}{fill_fields}) eq 'ARRAY' ?
98 0 ▶ @{$r->{util_args}{fill_fields}} : split(/,/, $r->{util_args}{fill_fields});
Branch: 1 branch
BranchTrueFalseTotal
ref $r->{'util_args'}{'fill_fields'} eq 'ARRAY' ? : 0 1 1
99 1 for my $field_idx (0..$#ff) {
100 2 my @ff2 = split /:/, $ff[$field_idx], 2;
101 2 ▶ if (@ff2 < 2) {
Branch: 1 branch
BranchTrueFalseTotal
if (@ff2 < 2) 1 1 2
102 1 $ff2[1] = $ff2[0];
103 }
104 2 $fill_fields{ $ff2[0] } = $ff2[1];
105 }
106 }
107  
108 # these are the keys that we add to the stash
109 1 $r->{lookup_fields} = \@lookup_fields;
110 1 $r->{fill_fields} = \%fill_fields;
111 1 $r->{source_fields_idx} = [];
112 1 $r->{source_fields} = [];
113 1 $r->{source_data_rows} = [];
114 1 $r->{target_fields_idx} = [];
115 1 $r->{target_fields} = [];
116 1 $r->{target_data_rows} = [];
117 },
118  
119 2 ▶ on_input_header_row => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
120 2 my $r = shift;
121  
122 2 ▶ if ($r->{input_filenum} == 1) {
Branch: 1 branch
BranchTrueFalseTotal
if ($r->{'input_filenum'} == 1) { } 1 1 2
123 1 $r->{target_fields} = $r->{input_fields};
124 1 $r->{target_fields_idx} = $r->{input_fields_idx};
125 1 $r->{output_fields} = $r->{input_fields};
126 } else {
127 1 $r->{source_fields} = $r->{input_fields};
128 1 $r->{source_fields_idx} = $r->{input_fields_idx};
129 }
130 },
131  
132 9 ▶ on_input_data_row => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
133 9 my $r = shift;
134  
135 9 ▶ if ($r->{input_filenum} == 1) {
Branch: 1 branch
BranchTrueFalseTotal
if ($r->{'input_filenum'} == 1) { } 3 6 9
136 3 push @{ $r->{target_data_rows} }, $r->{input_row};
137 } else {
138 6 push @{ $r->{source_data_rows} }, $r->{input_row};
139 }
140 },
141  
142 1 ▶ after_close_input_files => sub {
Subroutine __ANON__: called CC=15 SCAR=28.3
143 1 my $r = shift;
144  
145 1 my $ci = $r->{util_args}{ignore_case};
146  
147 # build lookup table
148 1 my %lookup_table; # key = joined lookup fields, val = source row idx
149 1 for my $row_idx (0..$#{$r->{source_data_rows}}) {
150 6 my $row = $r->{source_data_rows}[$row_idx];
151 my $key = join "|", map {
152 6 my $field = $r->{lookup_fields}[$_][1];
153 6 my $field_idx = $r->{source_fields_idx}{$field};
154 6 ▶ my $val = defined $field_idx ? $row->[$field_idx] : "";
Branch: 1 branch
BranchTrueFalseTotal
defined $field_idx ? : 6 0 6
155 6 ▶ $val = lc $val if $ci;
Branch: 1 branch
BranchTrueFalseTotal
if $ci 0 6 6
156 6 $val;
157 6 } 0..$#{ $r->{lookup_fields} };
158 6 ▶ $lookup_table{$key} //= $row_idx;
Condition33%1/3
$lookup_table{$key} //= $row_idx
l!l&&r!l&&!r
060
MC/DC0%0/2
$lookup_table{$key} //= $row_idx
$lookup_table{$key} $row_idx
Truth table33%1/3
A //= B
A= $lookup_table{$key}B= $row_idx
ABresult
1X1
011
000
159 }
160 #use DD; dd { lookup_fields=>$r->{lookup_fields}, fill_fields=>$r->{fill_fields}, lookup_table=>\%lookup_table };
161  
162 # fill target csv
163 1 my $num_filled = 0;
164  
165 1 for my $row (@{ $r->{target_data_rows} }) {
166 my $key = join "|", map {
167 3 my $field = $r->{lookup_fields}[$_][0];
168 3 my $field_idx = $r->{target_fields_idx}{$field};
169 3 ▶ my $val = defined $field_idx ? $row->[$field_idx] : "";
Branch: 1 branch
BranchTrueFalseTotal
defined $field_idx ? : 3 0 3
170 3 ▶ $val = lc $val if $ci;
Branch: 1 branch
BranchTrueFalseTotal
if $ci 0 3 3
171 3 $val;
172 3 } 0..$#{ $r->{lookup_fields} };
173  
174 #say "D:looking up '$key' ...";
175 3 ▶ if (defined(my $row_idx = $lookup_table{$key})) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined(my $row_idx = $lookup_table{$key})) 3 0 3
176 #say " D:found";
177 3 my $row_filled;
178 3 my $source_row = $r->{source_data_rows}[$row_idx];
179 3 for my $field (keys %{$r->{fill_fields}}) {
180 6 my $target_field_idx = $r->{target_fields_idx}{$field};
181 6 ▶ next unless defined $target_field_idx;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $target_field_idx 0 6 6
182 6 my $source_field_idx = $r->{source_fields_idx}{ $r->{fill_fields}{$field} };
183 6 ▶ next unless defined $source_field_idx;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $source_field_idx 0 6 6
184 6 $row->[$target_field_idx] =
185 $source_row->[$source_field_idx];
186 6 $row_filled++;
187 }
188 3 ▶ $num_filled++ if $row_filled;
Branch: 1 branch
BranchTrueFalseTotal
if $row_filled 3 0 3
189 }
190 3 ▶ unless ($r->{util_args}{count}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($r->{"util_args"}{"count"}) 3 0 3
191 3 $r->{code_print_row}->($row);
192 }
193 } # for target data row
194  
195 1 ▶ if ($r->{util_args}{count}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($r->{"util_args"}{"count"}) 0 1 1
196 0 $r->{result} = [200, "OK", $num_filled];
197 }
198 },
199 2 );
200  
201 2 1;
202 # ABSTRACT: Fill fields of a CSV file from another
203  
204 __END__
« CSVUtils/csv_pick_rows.pm ↑ Summary CSVUtils/csv_add_fields.pm »