2 # DESCRIPTION: import from [Oxygène](http://www.memsoft.fr) to [hledger](http://hledger.org)
3 # AUTHOR: Julien Moutinho <julm+hledger@autogeree.net>
4 # LICENSE: [GPLv3+](https://www.gnu.org/licenses/gpl-3.0.html)
5 # NOTE: should be easily hackable to import from other .csv
7 # % hledger-print-csv -f Chart_of_accounts.hledger >Chart_of_accounts.csv
8 # % iconv -f latin1 -t utf8 <EXPORT.oxygen.csv |
9 # perl hledger-of-oxygen-csv.pl \
10 # Chart_of_accounts.csv \
11 # >EXPORT.oxygen.hledger
13 # FORMAT of EXPORT.oxygen.csv:
15 # NUMJL;LIBJL;DTOPE;NPIEC;NUMCP;LIBCP;CODCP;LIBEC;MTDEB;MTCRE;COTVA;TXTVA
16 # 60;Achats;01/01/2012;ACH01/76;401REGIEQUART;REGIE DE QUARTIER;REGIE DE QUARTI;LOYER LOCAL DEC. 2011+1T 2012 REGIE QUAR;0,00;1410,91;;0,00
17 # 60;Achats;01/01/2012;ACH01/76;6132000;LOYER LOCAL 15 rue P. BONNARD;LOYER BONNARD;LOYER LOCAL DEC. 2011+1T 2012 REGIE QUAR;1076,25;0,00;;0,00
18 # 60;Achats;01/01/2012;ACH01/76;6165000;Responsabilité civile;;ASSURANCE LOCAL 2012 VIA REGIEQUARTIER;86,47;0,00;;0,00
19 # 60;Achats;01/01/2012;ACH01/76;6140000;Charges locatives et de copropriété;;CHARGES LOCAL 1T 2012;248,19;0,00;;0,00
20 # ; ... And so on.. and so forth..
23 # FORMAT of Chart_of_accounts.hledger:
28 # 0.ZZZ:1.YYY:2.XXX 0; 012. Description
29 # 0.ZZZ:1.YYY:3.WWW 0; 013. Description
30 # 0.ZZZ:1.YYY:3.WWW.4.VVV 0; 0134. Description
31 # ; ... And so on.. and so forth..
36 # 01/01 Plan comptable des associations
37 # 1.Capital 0 ; 1. COMPTES DE CCOAITAUX
38 # 1.Capital:0.Fonds 0 ; 10. Fonds associatifs et reserves
39 # 2.Immobilisation 0 ; 2. COMPTES D'IMMOBILISATIONS
40 # 2.Immobilisation:1.Corporelle 0 ; 21. Immobilisations corporelles
41 # 4.Tiers 0 ; 4. COMPTES TIERS
42 # 4.Tiers:0.Fournisseur 0 ; 40. Fournisseurs et comptes rattachés
43 # 5.Finance 0 ; 5. COMPTES FINANCIERS
44 # 5.Finance:1.Etablissement 0 ; 51. Banques, établissements financiers et assimilés
45 # 5.Finance:1.Etablissement:1.Valeur 0 ; 511. Valeurs à l’encaissement
46 # 5.Finance:1.Etablissement:1.Valeur:2.Chèque_à_encaisser 0 ; 5112. Chèques à encaisser
47 # 5.Finance:1.Etablissement:2.Banque:001.Courant 0 ; 512001. Crédit Coopératif - Compte courant
48 # 5.Finance:1.Etablissement:2.Banque:002.Livret 0 ; 512002. Crédit coopératif - Livret
49 # 6.Charge 0 ; 6. COMPTES D'ACHATS
50 # 6.Charge:1.Service 0 ; 61. Services extérieurs
51 # 6.Charge:2.Autre_service 0 ; 62. Autres services extérieurs
52 # 6.Charge:3.Impôt 0 ; 63. Impôts, taxes et versements assimilés
53 # 6.Charge:4.Personnel 0 ; 64. Charges de personnel
54 # 6.Charge:5.Gestion 0 ; 65. Autres charges de gestion courantes
55 # 6.Charge:8.Dotation 0 ; 68. Dotations aux amortissements, dépréciations, provisions et engagements
56 # 7.Produit 0 ; 7. COMPTES DE PRODUITS
57 # 7.Produit:0.Vente 0 ; 70. ventes de produits finis, prestations de services, marchandises
58 # ; ... And so on.. and so forth..
61 our $VERSION = '2014.07.22';
63 use warnings FATAL
=> qw(all);
65 use open qw
/:std :utf8/;
70 #require Text::CSV::Encoded;
75 my ($jj,$mm,undef,$yy) = ($_ =~ m{^\s*([0-3]?[0-9])\s*/\s*([0-1]?[0-9])\s*/\s*(20)?([0-9][0-9])\s*$});
76 return "20$yy/$mm/$jj";
80 my ($code) = ($_ =~ m{^([0-9]*?)0*$});
81 return (defined $code ?
$code : $_);
83 sub parse_journal
(@
) {
85 $_ = Text
::Trim
::trim
($_);
90 sub parse_csv_line
(@
) {
91 my ($nth, $h, $c) = @_;
92 #print STDERR ("parse_csv_line: csv_line($nth)=".Data::Dumper::Dumper($c));
93 my $date = parse_date
($c->{date
});
95 unless defined $h->{$date};
97 if (exists $h->{$date}->{$c->{transaction
}}) {
98 $t = $h->{$date}->{$c->{transaction
}};
102 { journal
=> parse_journal
($c->{journal
})
103 , journal_code
=> $c->{journal_code
}
106 $h->{$date}->{$c->{transaction
}} = $t;
110 if (defined $c->{debit
} and $c->{debit
} eq '0,00') {
111 $amount = "-$c->{credit}";
113 elsif (defined $c->{credit
} and $c->{credit
} eq '0,00') {
114 $amount = "$c->{debit}";
116 else { die "ERROR: wrong credit/debit: CSV#$nth: ".Data
::Dumper
::Dumper
($c); }
119 { account
=> parse_code
($c->{account
})
121 , comment
=> $c->{account_name
}
125 sub print_hledger
(@
) {
127 foreach my $date (sort {$a cmp $b} (keys %$h)) {
128 my $transactions = $h->{$date};
129 while (my ($transaction, $t) = each %$transactions) {
130 print STDOUT
"$date $transaction ; Journal:$t->{journal}\n";
132 foreach my $a (@
{$t->{postings
}}) {
133 if (not defined $a->{account
}) {
134 print STDERR Data
::Dumper
::Dumper
($t);
135 die "ERROR: wrong account in t=$transaction";
137 if (defined $ap->{$a->{account
}}) {
138 $a->{account
} = $ap->{$a->{account
}}->{account
}
140 my $w = 0 + length ($a->{account
});
144 my $p = $t->{postings
};
145 foreach my $a (sort {$a->{account
} cmp $b->{account
}} @
$p) {
146 print STDOUT
"\t$a->{account} $a->{amount} ; $a->{comment} CSV#.$a->{csv_nth}\n";
151 sub parse_chart_of_accounts
(@
) {
154 my $csv = Text
::CSV
->new
159 print STDERR
("Chart_of_accounts: ", $coa_file, "\n");
160 open (my $COA, "<:encoding(utf8)", $coa_file)
161 or die "ERROR: opening accounting plan given as first argument";
162 #my $coa_in = IO::Wrap::wraphandle($COA);
163 my $coa_head = $csv->getline($COA);
164 print STDERR
("coa_head: ", join("|", @
$coa_head), "\n");
165 $csv->column_names(@
$coa_head);
167 while (my $csv_line = $csv->getline_hr($COA)) {
169 my $post_cmt = $csv_line->{'posting-comment'};
170 die "ERROR: no posting-comment COA#$nth: ".Data
::Dumper
::Dumper
($csv_line)
171 if not defined $post_cmt;
172 my ($code, $description) = ($post_cmt =~ m{^([0-9]+)\.\s*(.*)$});
173 die "ERROR: cannot extract code in accounting plan: posting-comment COA#$nth: $csv_line->{'posting-comment'}"
174 if not defined $code;
176 { account
=> $csv_line->{account
}
177 , description
=> $description
180 print STDERR
"Chart_of_accounts: ".Data
::Dumper
::Dumper
(\
%ap);
185 my $ap = parse_chart_of_accounts
($ARGV[0]);
186 my $csv = Text
::CSV
->new
191 my $in = IO
::Wrap
::wraphandle
(\
*STDIN
);
192 binmode STDOUT
, ':utf8';
193 my $csv_head = $csv->getline($in);
194 #print STDERR ("head: ", join("|", @$csv_head), "\n");
195 #$csv->column_names(@$csv_head);
196 $csv->column_names (qw
(
210 my $hledger_data = {};
214 while (my $csv_line = $csv->getline_hr($in)) {
215 #print STDERR ("csv_line: ", join("|", @$csv_line), "\n");
216 parse_csv_line
(2 + $nth++, $hledger_data, $csv_line);
218 #print STDERR "hledger_data=".Data::Dumper::Dumper($hledger_data);
219 print_hledger
($hledger_data, $ap);
220 #my $out = IO::Wrap::wraphandle(\*STDERR);