summaryrefslogtreecommitdiff
path: root/hledger_csv.txt
blob: 766402d06b6a85d2dcb805fd1db04bd3f7a56027 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962

HLEDGER_CSV(5)               hledger User Manuals               HLEDGER_CSV(5)



NAME
       How hledger reads CSV data, and the CSV rules file format.

DESCRIPTION
       hledger  can read CSV files (Character Separated Value - usually comma,
       semicolon, or tab) containing dated records as  if  they  were  journal
       files, automatically converting each CSV record into a transaction.

       (To learn about writing CSV, see CSV output.)

       We describe each CSV file's format with a corresponding rules file.  By
       default this is named like the CSV file with a .rules extension  added.
       Eg  when reading FILE.csv, hledger also looks for FILE.csv.rules in the
       same directory as FILE.csv.  You can specify  a  different  rules  file
       with  the  --rules-file  option.  If a rules file is not found, hledger
       will create a sample rules file, which you'll need to adjust.

       This file contains rules describing the CSV data (header  line,  fields
       layout, date format etc.), and how to construct hledger journal entries
       (transactions) from it.  Often there will also be a list of conditional
       rules  for  categorising  transactions  based  on  their  descriptions.
       Here's an overview of the CSV rules; these  are  described  more  fully
       below, after the examples:


       skip                             skip one or more header lines or matched
                                        CSV records
       fields                           name CSV fields, assign them to  hledger
                                        fields
       field assignment                 assign  a  value  to  one hledger field,
                                        with interpolation
       separator                        a custom field separator
       if block                         apply some rules to CSV records  matched
                                        by patterns
       if table                         apply  some rules to CSV records matched
                                        by patterns, alternate syntax
       end                              skip the remaining CSV records
       date-format                      how to parse dates in CSV records
       decimal-mark                     the decimal mark used in CSV amounts, if
                                        ambiguous
       newest-first                     disambiguate  record  order when there's
                                        only one date
       include                          inline another CSV rules file
       balance-type                     choose which type of balance assignments
                                        to use

       Note,  for best error messages when reading CSV files, use a .csv, .tsv
       or .ssv file extension or file prefix - see File Extension below.

       There's an introductory Convert CSV files tutorial on hledger.org.

EXAMPLES
       Here are some sample hledger CSV rules files.  See also the  full  col-
       lection at:
       https://github.com/simonmichael/hledger/tree/master/examples/csv

   Basic
       At  minimum,  the  rules file must identify the date and amount fields,
       and often it also specifies the date format and how many  header  lines
       there are.  Here's a simple CSV file and a rules file for it:

              Date, Description, Id, Amount
              12/11/2019, Foo, 123, 10.23

              # basic.csv.rules
              skip         1
              fields       date, description, _, amount
              date-format  %d/%m/%Y

              $ hledger print -f basic.csv
              2019-11-12 Foo
                  expenses:unknown           10.23
                  income:unknown            -10.23

       Default account names are chosen, since we didn't set them.

   Bank of Ireland
       Here's  a  CSV with two amount fields (Debit and Credit), and a balance
       field, which we can use to add balance assertions, which is not  neces-
       sary but provides extra error checking:

              Date,Details,Debit,Credit,Balance
              07/12/2012,LODGMENT       529898,,10.0,131.21
              07/12/2012,PAYMENT,5,,126

              # bankofireland-checking.csv.rules

              # skip the header line
              skip

              # name the csv fields, and assign some of them as journal entry fields
              fields  date, description, amount-out, amount-in, balance

              # We generate balance assertions by assigning to "balance"
              # above, but you may sometimes need to remove these because:
              #
              # - the CSV balance differs from the true balance,
              #   by up to 0.0000000000005 in my experience
              #
              # - it is sometimes calculated based on non-chronological ordering,
              #   eg when multiple transactions clear on the same day

              # date is in UK/Ireland format
              date-format  %d/%m/%Y

              # set the currency
              currency  EUR

              # set the base account for all txns
              account1  assets:bank:boi:checking

              $ hledger -f bankofireland-checking.csv print
              2012-12-07 LODGMENT       529898
                  assets:bank:boi:checking         EUR10.0 = EUR131.2
                  income:unknown                  EUR-10.0

              2012-12-07 PAYMENT
                  assets:bank:boi:checking         EUR-5.0 = EUR126.0
                  expenses:unknown                  EUR5.0

       The  balance assertions don't raise an error above, because we're read-
       ing directly from CSV, but they will be checked if  these  entries  are
       imported into a journal file.

   Amazon
       Here we convert amazon.com order history, and use an if block to gener-
       ate a third posting if there's a fee.  (In practice you'd probably  get
       this data from your bank instead, but it's an example.)

              "Date","Type","To/From","Name","Status","Amount","Fees","Transaction ID"
              "Jul 29, 2012","Payment","To","Foo.","Completed","$20.00","$0.00","16000000000000DGLNJPI1P9B8DKPVHL"
              "Jul 30, 2012","Payment","To","Adapteva, Inc.","Completed","$25.00","$1.00","17LA58JSKRD4HDGLNJPI1P9B8DKPVHL"

              # amazon-orders.csv.rules

              # skip one header line
              skip 1

              # name the csv fields, and assign the transaction's date, amount and code.
              # Avoided the "status" and "amount" hledger field names to prevent confusion.
              fields date, _, toorfrom, name, amzstatus, amzamount, fees, code

              # how to parse the date
              date-format %b %-d, %Y

              # combine two fields to make the description
              description %toorfrom %name

              # save the status as a tag
              comment     status:%amzstatus

              # set the base account for all transactions
              account1    assets:amazon
              # leave amount1 blank so it can balance the other(s).
              # I'm assuming amzamount excludes the fees, don't remember

              # set a generic account2
              account2    expenses:misc
              amount2     %amzamount
              # and maybe refine it further:
              #include categorisation.rules

              # add a third posting for fees, but only if they are non-zero.
              if %fees [1-9]
               account3    expenses:fees
               amount3     %fees

              $ hledger -f amazon-orders.csv print
              2012-07-29 (16000000000000DGLNJPI1P9B8DKPVHL) To Foo.  ; status:Completed
                  assets:amazon
                  expenses:misc          $20.00

              2012-07-30 (17LA58JSKRD4HDGLNJPI1P9B8DKPVHL) To Adapteva, Inc.  ; status:Completed
                  assets:amazon
                  expenses:misc          $25.00
                  expenses:fees           $1.00

   Paypal
       Here's  a  real-world rules file for (customised) Paypal CSV, with some
       Paypal-specific rules, and a second rules file included:

              "Date","Time","TimeZone","Name","Type","Status","Currency","Gross","Fee","Net","From Email Address","To Email Address","Transaction ID","Item Title","Item ID","Reference Txn ID","Receipt ID","Balance","Note"
              "10/01/2019","03:46:20","PDT","Calm Radio","Subscription Payment","Completed","USD","-6.99","0.00","-6.99","simon@joyful.com","memberships@calmradio.com","60P57143A8206782E","MONTHLY - $1 for the first 2 Months: Me - Order 99309. Item total: $1.00 USD first 2 months, then $6.99 / Month","","I-R8YLY094FJYR","","-6.99",""
              "10/01/2019","03:46:20","PDT","","Bank Deposit to PP Account ","Pending","USD","6.99","0.00","6.99","","simon@joyful.com","0TU1544T080463733","","","60P57143A8206782E","","0.00",""
              "10/01/2019","08:57:01","PDT","Patreon","PreApproved Payment Bill User Payment","Completed","USD","-7.00","0.00","-7.00","simon@joyful.com","support@patreon.com","2722394R5F586712G","Patreon* Membership","","B-0PG93074E7M86381M","","-7.00",""
              "10/01/2019","08:57:01","PDT","","Bank Deposit to PP Account ","Pending","USD","7.00","0.00","7.00","","simon@joyful.com","71854087RG994194F","Patreon* Membership","","2722394R5F586712G","","0.00",""
              "10/19/2019","03:02:12","PDT","Wikimedia Foundation, Inc.","Subscription Payment","Completed","USD","-2.00","0.00","-2.00","simon@joyful.com","tle@wikimedia.org","K9U43044RY432050M","Monthly donation to the Wikimedia Foundation","","I-R5C3YUS3285L","","-2.00",""
              "10/19/2019","03:02:12","PDT","","Bank Deposit to PP Account ","Pending","USD","2.00","0.00","2.00","","simon@joyful.com","3XJ107139A851061F","","","K9U43044RY432050M","","0.00",""
              "10/22/2019","05:07:06","PDT","Noble Benefactor","Subscription Payment","Completed","USD","10.00","-0.59","9.41","noble@bene.fac.tor","simon@joyful.com","6L8L1662YP1334033","Joyful Systems","","I-KC9VBGY2GWDB","","9.41",""

              # paypal-custom.csv.rules

              # Tips:
              # Export from Activity -> Statements -> Custom -> Activity download
              # Suggested transaction type: "Balance affecting"
              # Paypal's default fields in 2018 were:
              # "Date","Time","TimeZone","Name","Type","Status","Currency","Gross","Fee","Net","From Email Address","To Email Address","Transaction ID","Shipping Address","Address Status","Item Title","Item ID","Shipping and Handling Amount","Insurance Amount","Sales Tax","Option 1 Name","Option 1 Value","Option 2 Name","Option 2 Value","Reference Txn ID","Invoice Number","Custom Number","Quantity","Receipt ID","Balance","Address Line 1","Address Line 2/District/Neighborhood","Town/City","State/Province/Region/County/Territory/Prefecture/Republic","Zip/Postal Code","Country","Contact Phone Number","Subject","Note","Country Code","Balance Impact"
              # This rules file assumes the following more detailed fields, configured in "Customize report fields":
              # "Date","Time","TimeZone","Name","Type","Status","Currency","Gross","Fee","Net","From Email Address","To Email Address","Transaction ID","Item Title","Item ID","Reference Txn ID","Receipt ID","Balance","Note"

              fields date, time, timezone, description_, type, status_, currency, grossamount, feeamount, netamount, fromemail, toemail, code, itemtitle, itemid, referencetxnid, receiptid, balance, note

              skip  1

              date-format  %-m/%-d/%Y

              # ignore some paypal events
              if
              In Progress
              Temporary Hold
              Update to
               skip

              # add more fields to the description
              description %description_ %itemtitle

              # save some other fields as tags
              comment  itemid:%itemid, fromemail:%fromemail, toemail:%toemail, time:%time, type:%type, status:%status_

              # convert to short currency symbols
              if %currency USD
               currency $
              if %currency EUR
               currency E
              if %currency GBP
               currency P

              # generate postings

              # the first posting will be the money leaving/entering my paypal account
              # (negative means leaving my account, in all amount fields)
              account1 assets:online:paypal
              amount1  %netamount

              # the second posting will be money sent to/received from other party
              # (account2 is set below)
              amount2  -%grossamount

              # if there's a fee, add a third posting for the money taken by paypal.
              if %feeamount [1-9]
               account3 expenses:banking:paypal
               amount3  -%feeamount
               comment3 business:

              # choose an account for the second posting

              # override the default account names:
              # if the amount is positive, it's income (a debit)
              if %grossamount ^[^-]
               account2 income:unknown
              # if negative, it's an expense (a credit)
              if %grossamount ^-
               account2 expenses:unknown

              # apply common rules for setting account2 & other tweaks
              include common.rules

              # apply some overrides specific to this csv

              # Transfers from/to bank. These are usually marked Pending,
              # which can be disregarded in this case.
              if
              Bank Account
              Bank Deposit to PP Account
               description %type for %referencetxnid %itemtitle
               account2 assets:bank:wf:pchecking
               account1 assets:online:paypal

              # Currency conversions
              if Currency Conversion
               account2 equity:currency conversion

              # common.rules

              if
              darcs
              noble benefactor
               account2 revenues:foss donations:darcshub
               comment2 business:

              if
              Calm Radio
               account2 expenses:online:apps

              if
              electronic frontier foundation
              Patreon
              wikimedia
              Advent of Code
               account2 expenses:dues

              if Google
               account2 expenses:online:apps
               description google | music

              $ hledger -f paypal-custom.csv  print
              2019-10-01 (60P57143A8206782E) Calm Radio MONTHLY - $1 for the first 2 Months: Me - Order 99309. Item total: $1.00 USD first 2 months, then $6.99 / Month  ; itemid:, fromemail:simon@joyful.com, toemail:memberships@calmradio.com, time:03:46:20, type:Subscription Payment, status:Completed
                  assets:online:paypal          $-6.99 = $-6.99
                  expenses:online:apps           $6.99

              2019-10-01 (0TU1544T080463733) Bank Deposit to PP Account for 60P57143A8206782E  ; itemid:, fromemail:, toemail:simon@joyful.com, time:03:46:20, type:Bank Deposit to PP Account, status:Pending
                  assets:online:paypal               $6.99 = $0.00
                  assets:bank:wf:pchecking          $-6.99

              2019-10-01 (2722394R5F586712G) Patreon Patreon* Membership  ; itemid:, fromemail:simon@joyful.com, toemail:support@patreon.com, time:08:57:01, type:PreApproved Payment Bill User Payment, status:Completed
                  assets:online:paypal          $-7.00 = $-7.00
                  expenses:dues                  $7.00

              2019-10-01 (71854087RG994194F) Bank Deposit to PP Account for 2722394R5F586712G Patreon* Membership  ; itemid:, fromemail:, toemail:simon@joyful.com, time:08:57:01, type:Bank Deposit to PP Account, status:Pending
                  assets:online:paypal               $7.00 = $0.00
                  assets:bank:wf:pchecking          $-7.00

              2019-10-19 (K9U43044RY432050M) Wikimedia Foundation, Inc. Monthly donation to the Wikimedia Foundation  ; itemid:, fromemail:simon@joyful.com, toemail:tle@wikimedia.org, time:03:02:12, type:Subscription Payment, status:Completed
                  assets:online:paypal             $-2.00 = $-2.00
                  expenses:dues                     $2.00
                  expenses:banking:paypal      ; business:

              2019-10-19 (3XJ107139A851061F) Bank Deposit to PP Account for K9U43044RY432050M  ; itemid:, fromemail:, toemail:simon@joyful.com, time:03:02:12, type:Bank Deposit to PP Account, status:Pending
                  assets:online:paypal               $2.00 = $0.00
                  assets:bank:wf:pchecking          $-2.00

              2019-10-22 (6L8L1662YP1334033) Noble Benefactor Joyful Systems  ; itemid:, fromemail:noble@bene.fac.tor, toemail:simon@joyful.com, time:05:07:06, type:Subscription Payment, status:Completed
                  assets:online:paypal                       $9.41 = $9.41
                  revenues:foss donations:darcshub         $-10.00  ; business:
                  expenses:banking:paypal                    $0.59  ; business:

CSV RULES
       The following kinds of rule can appear in the rules file, in any order.
       Blank lines and lines beginning with # or ; are ignored.

   skip
              skip N

       The  word  "skip"  followed by a number (or no number, meaning 1) tells
       hledger to ignore this many non-empty lines  preceding  the  CSV  data.
       (Empty/blank  lines  are skipped automatically.) You'll need this when-
       ever your CSV data contains header lines.

       It also has a second purpose: it can be used inside if blocks to ignore
       certain CSV records (described below).

   fields
              fields FIELDNAME1, FIELDNAME2, ...

       A  fields  list  (the  word  "fields" followed by comma-separated field
       names) is the quick way to assign CSV field values to  hledger  fields.
       It does two things:

       1. it  names  the  CSV fields.  This is optional, but can be convenient
          later for interpolating them.

       2. when you use a standard hledger field name, it assigns the CSV value
          to that part of the hledger transaction.

       Here's  an  example  that  says "use the 1st, 2nd and 4th fields as the
       transaction's date, description and amount; name the  last  two  fields
       for later reference; and ignore the others":

              fields date, description, , amount, , , somefield, anotherfield

       Field  names  may  not contain whitespace.  Fields you don't care about
       can be left unnamed.  Currently there must be least  two  items  (there
       must be at least one comma).

       Note,  always  use  comma  in  the  fields  list, even if your CSV uses
       another separator character.

       Here are the standard hledger field/pseudo-field names.  For more about
       the transaction parts they refer to, see the manual for hledger's jour-
       nal format.

   Transaction field names
       date, date2, status, code, description, comment can be used to form the
       transaction's first line.

   Posting field names
   account
       accountN,  where  N  is 1 to 99, causes a posting to be generated, with
       that account name.

       Most often there are two postings, so you'll want to set  account1  and
       account2.   Typically  account1 is associated with the CSV file, and is
       set once with a top-level assignment, while account2 is  set  based  on
       each transaction's description, and in conditional blocks.

       If  a  posting's  account name is left unset but its amount is set (see
       below), a default account name will be chosen (like  "expenses:unknown"
       or "income:unknown").

   amount
       amountN  sets  posting N's amount.  If the CSV uses separate fields for
       inflows and outflows, you can use amountN-in and  amountN-out  instead.
       By  assigning to amount1, amount2, ...  etc.  you can generate anywhere
       from 0 to 99 postings.

       There is also an older, unnumbered form of these  names,  suitable  for
       2-posting transactions, which sets both posting 1's and (negated) post-
       ing 2's amount: amount, or amount-in and  amount-out.   This  is  still
       supported  because  it  keeps pre-hledger-1.17 csv rules files working,
       and because it can be more succinct, and because  it  converts  posting
       2's amount to cost if there's a transaction price, which can be useful.

       If you have an existing rules file using the unnumbered form, you might
       want  to  use  the numbered form in certain conditional blocks, without
       having to update and retest all the old  rules.   To  facilitate  this,
       posting    1    ignores    amount/amount-in/amount-out    if   any   of
       amount1/amount1-in/amount1-out are assigned, and posting 2 ignores them
       if  any  of  amount2/amount2-in/amount2-out are assigned, avoiding con-
       flicts.

   currency
       If the CSV has the currency symbol in a separate field (ie, not part of
       the  amount  field), you can use currencyN to prepend it to posting N's
       amount.  Or, currency with no number affects all postings.

   balance
       balanceN sets a balance assertion amount (or if the posting  amount  is
       left empty, a balance assignment) on posting N.

       Also,  for  compatibility with hledger <1.17: balance with no number is
       equivalent to balance1.

       You can adjust the type of assertion/assignment with  the  balance-type
       rule (see below).

   comment
       Finally, commentN sets a comment on the Nth posting.  Comments can also
       contain tags, as usual.

       See TIPS below for more about setting amounts and currency.

   field assignment
              HLEDGERFIELDNAME FIELDVALUE

       Instead of or in addition to a  fields  list,  you  can  use  a  "field
       assignment" rule to set the value of a single hledger field, by writing
       its name (any of the standard hledger field names above) followed by  a
       text  value.  The value may contain interpolated CSV fields, referenced
       by their 1-based position in the CSV record (%N), or by the  name  they
       were given in the fields list (%CSVFIELDNAME).  Some examples:

              # set the amount to the 4th CSV field, with " USD" appended
              amount %4 USD

              # combine three fields to make a comment, containing note: and date: tags
              comment note: %somefield - %anotherfield, date: %1

       Interpolation  strips  outer  whitespace  (so  a  CSV  value like " 1 "
       becomes 1 when interpolated) (#1051).  See TIPS below  for  more  about
       referencing other fields.

   separator
       You  can  use the separator rule to read other kinds of character-sepa-
       rated data.  The argument is any single  separator  character,  or  the
       words  tab or space (case insensitive).  Eg, for comma-separated values
       (CSV):

              separator ,

       or for semicolon-separated values (SSV):

              separator ;

       or for tab-separated values (TSV):

              separator TAB

       If the input file has a .csv, .ssv or .tsv file extension (or  a  csv:,
       ssv:, tsv: prefix), the appropriate separator will be inferred automat-
       ically, and you won't need this rule.

   if block
              if MATCHER
               RULE

              if
              MATCHER
              MATCHER
              MATCHER
               RULE
               RULE

       Conditional blocks ("if blocks") are a block of rules that are  applied
       only  to CSV records which match certain patterns.  They are often used
       for customising account names based on transaction descriptions.

   Matching the whole record
       Each MATCHER can be a record matcher, which looks like this:

              REGEX

       REGEX is a case-insensitive regular expression  which  tries  to  match
       anywhere  within  the  CSV record.  It is a POSIX ERE (extended regular
       expression) that also supports GNU word boundaries (\b,  \B,  \<,  \>),
       and  nothing  else.   If  you  have  trouble,  be  sure  to  check  our
       https://hledger.org/hledger.html#regular-expressions doc.

       Important note: the record that is matched is not the original  record,
       but  a synthetic one, with any enclosing double quotes (but not enclos-
       ing whitespace) removed, and always comma-separated (which means that a
       field  containing  a  comma  will  appear like two fields).  Eg, if the
       original record is 2020-01-01; "Acme, Inc.";   1,000,  the  REGEX  will
       actually see 2020-01-01,Acme, Inc.,  1,000).

   Matching individual fields
       Or, MATCHER can be a field matcher, like this:

              %CSVFIELD REGEX

       which  matches just the content of a particular CSV field.  CSVFIELD is
       a percent sign followed by the field's  name  or  column  number,  like
       %date or %1.

   Combining matchers
       A single matcher can be written on the same line as the "if"; or multi-
       ple matchers can be written on the following lines, non-indented.  Mul-
       tiple  matchers are OR'd (any one of them can match), unless one begins
       with an & symbol, in which case it is AND'ed with the previous matcher.

              if
              MATCHER
              & MATCHER
               RULE

   Rules applied on successful match
       After  the  patterns  there  should  be one or more rules to apply, all
       indented by at least one space.  Three kinds of  rule  are  allowed  in
       conditional blocks:

       o field assignments (to set a hledger field)

       o skip (to skip the matched CSV record)

       o end (to skip all remaining CSV records).

       Examples:

              # if the CSV record contains "groceries", set account2 to "expenses:groceries"
              if groceries
               account2 expenses:groceries

              # if the CSV record contains any of these patterns, set account2 and comment as shown
              if
              monthly service fee
              atm transaction fee
              banking thru software
               account2 expenses:business:banking
               comment  XXX deductible ? check it

   if table
              if,CSVFIELDNAME1,CSVFIELDNAME2,...,CSVFIELDNAMEn
              MATCHER1,VALUE11,VALUE12,...,VALUE1n
              MATCHER2,VALUE21,VALUE22,...,VALUE2n
              MATCHER3,VALUE31,VALUE32,...,VALUE3n
              <empty line>

       Conditional  tables  ("if  tables")  are  a different syntax to specify
       field assignments that will be applied only to CSV records which  match
       certain patterns.

       MATCHER  could  be  either field or record matcher, as described above.
       When MATCHER matches, values from that row would be assigned to the CSV
       fields named on the if line, in the same order.

       Therefore if table is exactly equivalent to a sequence of of if blocks:

              if MATCHER1
                CSVFIELDNAME1 VALUE11
                CSVFIELDNAME2 VALUE12
                ...
                CSVFIELDNAMEn VALUE1n

              if MATCHER2
                CSVFIELDNAME1 VALUE21
                CSVFIELDNAME2 VALUE22
                ...
                CSVFIELDNAMEn VALUE2n

              if MATCHER3
                CSVFIELDNAME1 VALUE31
                CSVFIELDNAME2 VALUE32
                ...
                CSVFIELDNAMEn VALUE3n

       Each line starting with MATCHER should contain enough (possibly  empty)
       values for all the listed fields.

       Rules  would be checked and applied in the order they are listed in the
       table and, like with if blocks, later rules (in the same or another ta-
       ble) or if blocks could override the effect of any rule.

       Instead  of ',' you can use a variety of other non-alphanumeric charac-
       ters as a separator.  First character after if is taken to be the sepa-
       rator  for the rest of the table.  It is the responsibility of the user
       to ensure that separator does not occur inside MATCHERs  and  values  -
       there is no way to escape separator.

       Example:

              if,account2,comment
              atm transaction fee,expenses:business:banking,deductible? check it
              %description groceries,expenses:groceries,
              2020/01/12.*Plumbing LLC,expenses:house:upkeep,emergency plumbing call-out

   end
       This  rule  can  be  used inside if blocks (only), to make hledger stop
       reading this CSV file and move on to the next input file, or to command
       execution.  Eg:

              # ignore everything following the first empty record
              if ,,,,
               end

   date-format
              date-format DATEFMT

       This  is  a  helper for the date (and date2) fields.  If your CSV dates
       are not formatted like YYYY-MM-DD,  YYYY/MM/DD  or  YYYY.MM.DD,  you'll
       need  to  add  a  date-format rule describing them with a strptime date
       parsing pattern, which must parse the CSV date value completely.   Some
       examples:

              # MM/DD/YY
              date-format %m/%d/%y

              # D/M/YYYY
              # The - makes leading zeros optional.
              date-format %-d/%-m/%Y

              # YYYY-Mmm-DD
              date-format %Y-%h-%d

              # M/D/YYYY HH:MM AM some other junk
              # Note the time and junk must be fully parsed, though only the date is used.
              date-format %-m/%-d/%Y %l:%M %p some other junk

       For the supported strptime syntax, see:
       https://hackage.haskell.org/package/time/docs/Data-Time-For-
       mat.html#v:formatTime

   decimal-mark
              decimal-mark .

       or:

              decimal-mark ,

       hledger automatically accepts either period or comma as a decimal  mark
       when  parsing  numbers (cf Amounts).  However if any numbers in the CSV
       contain digit group marks,  such  as  thousand-separating  commas,  you
       should  declare  the  decimal  mark explicitly with this rule, to avoid
       misparsed numbers.

   newest-first
       hledger always sorts the generated transactions by date.   Transactions
       on  the same date should appear in the same order as their CSV records,
       as hledger can usually auto-detect whether the CSV's  normal  order  is
       oldest first or newest first.  But if all of the following are true:

       o the  CSV  might  sometimes  contain just one day of data (all records
         having the same date)

       o the CSV records are normally in reverse chronological  order  (newest
         at the top)

       o and you care about preserving the order of same-day transactions

       then, you should add the newest-first rule as a hint.  Eg:

              # tell hledger explicitly that the CSV is normally newest first
              newest-first

   include
              include RULESFILE

       This  includes  the  contents  of another CSV rules file at this point.
       RULESFILE is an absolute file path or a path relative  to  the  current
       file's  directory.  This can be useful for sharing common rules between
       several rules files, eg:

              # someaccount.csv.rules

              ## someaccount-specific rules
              fields   date,description,amount
              account1 assets:someaccount
              account2 expenses:misc

              ## common rules
              include categorisation.rules

   balance-type
       Balance assertions generated by assigning to balanceN are of the simple
       =  type  by  default, which is a single-commodity, subaccount-excluding
       assertion.  You may find the subaccount-including variants more useful,
       eg  if  you  have  created some virtual subaccounts of checking to help
       with budgeting.  You can select a different type of assertion with  the
       balance-type rule:

              # balance assertions will consider all commodities and all subaccounts
              balance-type ==*

       Here are the balance assertion types for quick reference:

              =    single commodity, exclude subaccounts
              =*   single commodity, include subaccounts
              ==   multi commodity,  exclude subaccounts
              ==*  multi commodity,  include subaccounts

TIPS
   Rapid feedback
       It's  a  good idea to get rapid feedback while creating/troubleshooting
       CSV rules.  Here's a good way, using entr from http://eradman.com/entr-
       project :

              $ ls foo.csv* | entr bash -c 'echo ----; hledger -f foo.csv print desc:SOMEDESC'

       A  desc:  query (eg) is used to select just one, or a few, transactions
       of interest.  "bash -c" is used to run multiple  commands,  so  we  can
       echo  a  separator  each  time the command re-runs, making it easier to
       read the output.

   Valid CSV
       hledger accepts CSV conforming  to  RFC  4180.   When  CSV  values  are
       enclosed in quotes, note:

       o they must be double quotes (not single quotes)

       o spaces outside the quotes are not allowed

   File Extension
       To  help hledger identify the format and show the right error messages,
       CSV/SSV/TSV files should normally be named with a .csv,  .ssv  or  .tsv
       filename  extension.   Or,  the file path should be prefixed with csv:,
       ssv: or tsv:.  Eg:

              $ hledger -f foo.ssv print

       or:

              $ cat foo | hledger -f ssv:- foo

       You can override the file extension with a separator  rule  if  needed.
       See also: Input files in the hledger manual.

   Reading multiple CSV files
       If  you  use  multiple  -f  options to read multiple CSV files at once,
       hledger will look for a correspondingly-named rules file for  each  CSV
       file.   But if you use the --rules-file option, that rules file will be
       used for all the CSV files.

   Valid transactions
       After reading a CSV file, hledger post-processes and validates the gen-
       erated journal entries as it would for a journal file - balancing them,
       applying balance assignments, and canonicalising  amount  styles.   Any
       errors  at this stage will be reported in the usual way, displaying the
       problem entry.

       There is one exception: balance assertions, if you have generated them,
       will  not  be checked, since normally these will work only when the CSV
       data is part of the main journal.  If you  do  need  to  check  balance
       assertions generated from CSV right away, pipe into another hledger:

              $ hledger -f file.csv print | hledger -f- print

   Deduplicating, importing
       When  you  download a CSV file periodically, eg to get your latest bank
       transactions, the new file may overlap with  the  old  one,  containing
       some of the same records.

       The import command will (a) detect the new transactions, and (b) append
       just those transactions to your main journal.  It is idempotent, so you
       don't  have to remember how many times you ran it or with which version
       of the CSV.  (It keeps state in a hidden .latest.FILE.csv  file.)  This
       is the easiest way to import CSV data.  Eg:

              # download the latest CSV files, then run this command.
              # Note, no -f flags needed here.
              $ hledger import *.csv [--dry]

       This  method  works  for  most CSV files.  (Where records have a stable
       chronological order, and new records appear only at the new end.)

       A number of other tools and workflows, hledger-specific and  otherwise,
       exist for converting, deduplicating, classifying and managing CSV data.
       See:

       o https://hledger.org -> sidebar -> real world setups

       o https://plaintextaccounting.org -> data import/conversion

   Setting amounts
       A posting amount can be set in one of these ways:

       o by assigning (with a fields list  or  field  assignment)  to  amountN
         (posting N's amount) or amount (posting 1's amount)

       o by  assigning to amountN-in and amountN-out (or amount-in and amount-
         out).  For each CSV record, whichever of these has a  non-zero  value
         will  be  used,  with  appropriate  sign.  If both contain a non-zero
         value, this may not work.

       o by assigning to balanceN (or balance) instead of the  above,  setting
         the  amount  indirectly via a balance assignment.  If you do this the
         default account name may be wrong, so you should set that explicitly.

       There is some special handling for an amount's sign:

       o If  an amount value is parenthesised, it will be de-parenthesised and
         sign-flipped.

       o If an amount value begins with a double minus sign, those cancel  out
         and are removed.

       o If an amount value begins with a plus sign, that will be removed

   Setting currency/commodity
       If  the  currency/commodity  symbol  is  included  in  the CSV's amount
       field(s):

              2020-01-01,foo,$123.00

       you don't have to do anything special for the commodity symbol, it will
       be assigned as part of the amount.  Eg:

              fields date,description,amount

              2020-01-01 foo
                  expenses:unknown         $123.00
                  income:unknown          $-123.00

       If the currency is provided as a separate CSV field:

              2020-01-01,foo,USD,123.00

       You can assign that to the currency pseudo-field, which has the special
       effect of prepending itself to every amount in the transaction (on  the
       left, with no separating space):

              fields date,description,currency,amount

              2020-01-01 foo
                  expenses:unknown       USD123.00
                  income:unknown        USD-123.00

       Or,  you  can  use a field assignment to construct the amount yourself,
       with more control.  Eg to put the symbol on the right, and separated by
       a space:

              fields date,description,cur,amt
              amount %amt %cur

              2020-01-01 foo
                  expenses:unknown        123.00 USD
                  income:unknown         -123.00 USD

       Note  we  used a temporary field name (cur) that is not currency - that
       would trigger the prepending effect, which we don't want here.

   Referencing other fields
       In field assignments, you can interpolate only CSV fields, not  hledger
       fields.   In  the example below, there's both a CSV field and a hledger
       field named amount1, but %amount1 always means the CSV field,  not  the
       hledger field:

              # Name the third CSV field "amount1"
              fields date,description,amount1

              # Set hledger's amount1 to the CSV amount1 field followed by USD
              amount1 %amount1 USD

              # Set comment to the CSV amount1 (not the amount1 assigned above)
              comment %amount1

       Here,  since there's no CSV amount1 field, %amount1 will produce a lit-
       eral "amount1":

              fields date,description,csvamount
              amount1 %csvamount USD
              # Can't interpolate amount1 here
              comment %amount1

       When there are multiple field assignments to the  same  hledger  field,
       only the last one takes effect.  Here, comment's value will be be B, or
       C if "something" is matched, but never A:

              comment A
              comment B
              if something
               comment C

   How CSV rules are evaluated
       Here's how to think of CSV rules being evaluated (if  you  really  need
       to).  First,

       o include  - all includes are inlined, from top to bottom, depth first.
         (At each include point the file is inlined and  scanned  for  further
         includes, recursively, before proceeding.)

       Then  "global"  rules  are  evaluated,  top  to  bottom.   If a rule is
       repeated, the last one wins:

       o skip (at top level)

       o date-format

       o newest-first

       o fields - names the CSV fields, optionally sets up initial assignments
         to hledger fields

       Then for each CSV record in turn:

       o test  all  if  blocks.   If  any of them contain a end rule, skip all
         remaining CSV records.  Otherwise if any of them contain a skip rule,
         skip  that  many  CSV  records.   If  there are multiple matched skip
         rules, the first one wins.

       o collect all field assignments at top level and in matched if  blocks.
         When  there  are multiple assignments for a field, keep only the last
         one.

       o compute a value for each hledger field -  either  the  one  that  was
         assigned  to  it (and interpolate the %CSVFIELDNAME references), or a
         default

       o generate a synthetic hledger transaction from these values.

       This is all part of the CSV reader, one of several readers hledger  can
       use  to parse input files.  When all files have been read successfully,
       the transactions are passed as input to whichever hledger  command  the
       user specified.



REPORTING BUGS
       Report  bugs at http://bugs.hledger.org (or on the #hledger IRC channel
       or hledger mail list)


AUTHORS
       Simon Michael <simon@joyful.com> and contributors


COPYRIGHT
       Copyright (C) 2007-2020 Simon Michael.
       Released under GNU GPL v3 or later.


SEE ALSO
       hledger(1), hledger-ui(1), hledger-web(1), ledger(1)

       hledger_journal(5), hledger_csv(5), hledger_timeclock(5), hledger_time-
       dot(5)



hledger-lib-1.20.4               December 2020                  HLEDGER_CSV(5)