Repository navigation
missing guard for arbitrary "s within a csv field. #2663
Description
Activity
What kind of CSV is that ? hledger accepts RFC 4180 CSV, where " inside a quoted field must be escaped by doubling it ("").
https://hledger.org/1.52/hledger.html#valid-csvAs a practical workaround for this CSV, possibly you could escape any double quote which has a non-comma on both sides of it (and remove the excess trailing comma):
source foo*.csv | sed -E -e 's/([^,])"([^,])/\1""\2/g' -e 's/,$//'Hm, fair point. It sounds like all instances of "valid CSV" in the docs should be replaced with "supported CSV input".
Dealing with this on a case-by-case basis with sed is too high a barrier of entry (or is seen as too fragile for financial data) for the users I support, so I'm now investigating alternatives. I'll close this issue in a future documentation PR. Please feel free to ping me if I seem to be taking too long.
That doc section defines what we mean by valid CSV. I'm not really familiar with any other definition of valid CSV. If this was an app, I'd like to research it and see how widespread its flavour of CSV is, but it seems this is just a bank ? I would fix their CSV and move on, or are you seeing this in more places ?
https://openformatter.com/blog/csv-format-standards-rfc-4180
Valid CSV is a much looser definition than RFC 4180. The reason this is notable is because banks may provide files that are in Excel dialect, for consumption by Excel. Using CSV as an interchange format between databases is also notable, and common. Have you seen or heard of the creative ways that CSV is used in academia for things that arguably might be better suited for a database?
Some colleagues of mine have told me about even more arcane, yet valid, CSV variations they've encountered on proprietary UNIX and mainframes. Unfortunately banking is often run on these systems.
To be clear, I agree that it's fair to say anything that hledger requires preprocessing for anything that isn't RFC 4180, but there's a whole world of perfectly valid CSV that isn't RFC-4180-compliant out there :)
- Understood. Yes we can expect anything from banks so it may be nice to be more lenient if possible, making data cleaning less necessary. If we can identify this style of csv and its encoding method, that will help, our csv lib may even support it. Is it an Excel style ? Message ID: ***@***.***>
No, it's not Excel style; Excel escapes double quotes like RFC 4180. My research says this is just a broken CSV, with no consistent escape convention. We can decode this particular example with a heuristic, but that's fragile and can break other valid data.
hledger 1.52.1-g3834a163b-20260428, linux-x86_64 (official Docker image)
Minimal reproducer:
That's valid CSV, so hledger import should handle it, and it seems like multiple quotes per CSV field are not yet correctly guarded.
[edit: drop extra quotes from "Date" in table]