#!/usr/bin/env node
/**
* ICS PDF to CSV Converter
*
* Converts ICS creditcard PDF statements to CSV format compatible with SnelStart import.
*
* Usage:
* node scripts/ics-pdf-to-csv.mjs <input-folder> [output-csv]
* node scripts/ics-pdf-to-csv.mjs ~/Desktop/Snelstart-Administratie/prive
* node scripts/ics-pdf-to-csv.mjs ~/Desktop/Snelstart-Administratie/prive output.csv
*/
import fs from 'fs';
import path from 'path';
import { getDocument } from 'pdfjs-dist/legacy/build/pdf.mjs';
// Dutch month names to numbers
const DUTCH_MONTHS = {
'jan': '01', 'feb': '02', 'mrt': '03', 'apr': '04',
'mei': '05', 'jun': '06', 'jul': '07', 'aug': '08',
'sep': '09', 'okt': '10', 'nov': '11', 'dec': '12',
'januari': '01', 'februari': '02', 'maart': '03', 'april': '04',
'juni': '06', 'juli': '07', 'augustus': '08',
'september': '09', 'oktober': '10', 'november': '11', 'december': '12'
};
/**
* Parse a Dutch date like "22 dec." or "03 jan." to components
*/
function parseDutchDate(dateStr, year) {
const match = dateStr.trim().match(/^(\d{1,2})\s+([a-z]+)\.?$/i);
if (!match) return null;
const day = match[1].padStart(2, '0');
const monthStr = match[2].toLowerCase();
const month = DUTCH_MONTHS[monthStr];
if (!month) return null;
return { day, month, year };
}
/**
* Convert date components to DD-MM-YYYY format
*/
function formatDate(dateObj) {
return `${dateObj.day}-${dateObj.month}-${dateObj.year}`;
}
/**
* Parse amount string like "21,54" or "3.000,00" to number
*/
function parseAmount(amountStr) {
if (!amountStr) return 0;
const cleaned = amountStr.replace(/\./g, '').replace(',', '.');
return parseFloat(cleaned) || 0;
}
/**
* Extract statement year from PDF text
*/
function extractStatementYear(text) {
const match = text.match(/(\d{1,2})\s+(januari|februari|maart|april|mei|juni|juli|augustus|september|oktober|november|december)\s+(\d{4})/i);
if (match) {
return match[3];
}
return new Date().getFullYear().toString();
}
/**
* Extract ICS customer number from PDF text
*/
function extractCustomerNumber(text) {
const match = text.match(/ICS-klantnummer\s*(\d+)/);
return match ? match[1] : 'unknown';
}
/**
* Parse transactions from PDF text
*/
function parseTransactions(text, statementYear) {
const transactions = [];
// The PDF text has transactions in format:
// "24 nov. 24 nov. Patreon* Membership Internet IE 4,84 Af"
// We use a global regex to find all matches
// Pattern to match transaction lines
// DD mmm. DD mmm. Description ... Amount Af/Bij
const txRegex = /(\d{1,2})\s+([a-z]{3,4})\.?\s+(\d{1,2})\s+([a-z]{3,4})\.?\s+(.+?)\s+([\d.,]+)\s+(Af|Bij)/gi;
const statementYearInt = parseInt(statementYear);
let match;
while ((match = txRegex.exec(text)) !== null) {
let [, transDay, transMonthStr, bookDay, bookMonthStr, middle, amountStr, direction] = match;
// Skip if this is header text
if (middle.includes('Datum') || middle.includes('ICS-klantnummer') ||
middle.includes('Vorig openstaand') || middle.includes('Totaal')) {
continue;
}
// Parse months
const transMonth = DUTCH_MONTHS[transMonthStr.toLowerCase()];
const bookMonth = DUTCH_MONTHS[bookMonthStr.toLowerCase()];
if (!transMonth || !bookMonth) continue;
// Clean up description - remove foreign currency amounts and wisselkoers
let description = middle
.replace(/\s+USD\s*$/i, '')
.replace(/\s+[\d.,]+\s+USD\s*/gi, ' ')
.replace(/Wisselkoers\s+USD\s+[\d.,]+/gi, '')
.replace(/\s+/g, ' ')
.trim();
// Determine years based on statement year and month
// Statement covers previous month, so transactions from month before statement
// can be from previous year
let transYear = statementYearInt;
let bookYear = statementYearInt;
// Extract statement month from text
const stmtMatch = text.match(/(\d{1,2})\s+(januari|februari|maart|april|mei|juni|juli|augustus|september|oktober|november|december)\s+(\d{4})/i);
const stmtMonth = stmtMatch ? DUTCH_MONTHS[stmtMatch[2].toLowerCase()] : '01';
// If statement is in January and transaction is in December, transaction is from previous year
if (stmtMonth === '01' && transMonth === '12') {
transYear = statementYearInt - 1;
}
if (stmtMonth === '01' && bookMonth === '12') {
bookYear = statementYearInt - 1;
}
// If statement is in February and transaction is in December/January
if (stmtMonth === '02' && transMonth === '12') {
transYear = statementYearInt - 1;
}
if (stmtMonth === '02' && bookMonth === '12') {
bookYear = statementYearInt - 1;
}
const amount = parseAmount(amountStr);
const isDebit = direction.toLowerCase() === 'af';
transactions.push({
transactionDate: `${transDay.padStart(2, '0')}-${transMonth}-${transYear}`,
bookingDate: `${bookDay.padStart(2, '0')}-${bookMonth}-${bookYear}`,
description: description.substring(0, 100),
amount: isDebit ? -amount : amount,
direction: direction
});
}
return transactions;
}
/**
* Extract text from all pages of a PDF
*/
async function extractPdfText(filePath) {
const data = new Uint8Array(fs.readFileSync(filePath));
const doc = await getDocument({ data }).promise;
let fullText = '';
for (let i = 1; i <= doc.numPages; i++) {
const page = await doc.getPage(i);
const textContent = await page.getTextContent();
const pageText = textContent.items.map(item => item.str).join(' ');
fullText += pageText + '\n';
}
return fullText;
}
/**
* Process a single PDF file
*/
async function processPdf(filePath) {
const text = await extractPdfText(filePath);
const year = extractStatementYear(text);
const customerNumber = extractCustomerNumber(text);
const transactions = parseTransactions(text, year);
return {
file: path.basename(filePath),
customerNumber,
year,
transactions
};
}
/**
* Convert transactions to CSV format compatible with ICS CSV export
*/
function toCsv(allTransactions, customerNumber) {
// CSV header matching ICS format
const header = 'Kaartnummer;Naam kaarthouder;Transactiedatum;Boekdatum;Bedrag transactie;Valuta;Bedrag in EUR;Omschrijving;Type transactie';
const rows = allTransactions.map(tx => {
const amount = tx.amount.toFixed(2).replace('.', ',');
const type = tx.amount >= 0 ? 'Transaction' : 'Transaction';
return [
customerNumber,
'ALBERT BARTH',
tx.transactionDate,
tx.bookingDate,
amount,
'EUR',
amount,
`"${tx.description.replace(/"/g, '""')}"`,
type
].join(';');
});
return [header, ...rows].join('\n');
}
/**
* Main function
*/
async function main() {
const args = process.argv.slice(2);
if (args.length === 0) {
console.log('Usage: node scripts/ics-pdf-to-csv.mjs <input-folder> [output-csv]');
console.log('');
console.log('Examples:');
console.log(' node scripts/ics-pdf-to-csv.mjs ~/Desktop/Snelstart-Administratie/prive');
console.log(' node scripts/ics-pdf-to-csv.mjs ~/Desktop/Snelstart-Administratie/prive transactions.csv');
process.exit(1);
}
const inputPath = args[0];
let outputPath = args[1];
// Check if input is a directory or file
const stat = fs.statSync(inputPath);
let pdfFiles = [];
if (stat.isDirectory()) {
// Find all PDF files in directory
pdfFiles = fs.readdirSync(inputPath)
.filter(f => f.toLowerCase().endsWith('.pdf'))
.sort()
.map(f => path.join(inputPath, f));
if (!outputPath) {
outputPath = path.join(inputPath, 'transactions.csv');
}
} else {
pdfFiles = [inputPath];
if (!outputPath) {
outputPath = inputPath.replace('.pdf', '.csv');
}
}
if (pdfFiles.length === 0) {
console.error('No PDF files found');
process.exit(1);
}
console.log(`Found ${pdfFiles.length} PDF files`);
let allTransactions = [];
let customerNumber = 'unknown';
for (const pdfFile of pdfFiles) {
console.log(`Processing: ${path.basename(pdfFile)}`);
try {
const result = await processPdf(pdfFile);
customerNumber = result.customerNumber;
allTransactions.push(...result.transactions);
console.log(` -> ${result.transactions.length} transactions`);
} catch (err) {
console.error(` Error: ${err.message}`);
}
}
// Sort by transaction date
allTransactions.sort((a, b) => {
const [aDay, aMonth, aYear] = a.transactionDate.split('-');
const [bDay, bMonth, bYear] = b.transactionDate.split('-');
const aDate = new Date(aYear, aMonth - 1, aDay);
const bDate = new Date(bYear, bMonth - 1, bDay);
return aDate - bDate;
});
// Remove duplicates (same date, amount, description)
const seen = new Set();
const uniqueTransactions = [];
for (const tx of allTransactions) {
const key = `${tx.transactionDate}|${tx.amount}|${tx.description}`;
if (!seen.has(key)) {
seen.add(key);
uniqueTransactions.push(tx);
}
}
const duplicatesRemoved = allTransactions.length - uniqueTransactions.length;
// Generate CSV
const csv = toCsv(uniqueTransactions, customerNumber);
fs.writeFileSync(outputPath, csv);
console.log('');
console.log('Summary:');
console.log(` Total transactions: ${allTransactions.length}`);
console.log(` Duplicates removed: ${duplicatesRemoved}`);
console.log(` Unique transactions: ${uniqueTransactions.length}`);
console.log(` Output: ${outputPath}`);
// Calculate totals
const totalDebit = uniqueTransactions.filter(t => t.amount < 0).reduce((sum, t) => sum + Math.abs(t.amount), 0);
const totalCredit = uniqueTransactions.filter(t => t.amount > 0).reduce((sum, t) => sum + t.amount, 0);
console.log(` Total debit: €${totalDebit.toFixed(2)}`);
console.log(` Total credit: €${totalCredit.toFixed(2)}`);
}
main().catch(err => {
console.error('Error:', err.message);
process.exit(1);
});
