Here are eight transactions. ';' is the column separator. The last column is the transaction number. It is virtually impossible to line up the transaction amounts in the same column.
When your bank can only generate a semicolon separated CSV, and not a comma separated CSV, you are prone to run into problems when a value/field itself contains a semicolon. I don't know if that will (ever) be an issue in this particular case.
awk(1) (in base) can only tackle CSV input accurately when the input uses a comma as separator. If you have a semicolon as separator, then you'll likely have to look elsewhere like
sysutils/goawk (perhaps
lang/gawk but on the face of it that seems difficult).
AFAIK, there were/are two main problems processing CSV files with respect to various awk versions (but not limited to awk):
- values (fields) containing the comma separator
- values (fields) containing newlines of some sort; the precise "transition character(s)" differ per OS
So, in general, using the comma as a separator, the problem is using the comma itself inside a value, the "CSV-mode" of awk makes allowances for that. As a rule, it is not safe to use
awk -F',' ... for CSV input processing.
Additionally, as it seems in your case, another separator (
;) is being used. That may have been done to mitigate the "comma in value" problem that requires an escaping mechanism for the comma. But in general, even using a semicolon as separator needs an escaping-mechanism when a semicolon is used in a value.
For CSV processing using GoAWK:
- Modernizing AWK, a 45-year old language, by adding CSV support by Ben Hoyt 2022
- GoAWK's CSV and TSV file support
#1 is nice for initial overview of CSV processing using GoAWK.
#2 is to be used as a guide when you are working with
sysutils/goawk
For GoAWK, the correct set up for use of CSV input with the use of
; as a separator seems to be:
Code:
[1-0] % goawk -i 'csv separator=;' '{ print NR,NF }' i1-m13
1 9
2 9
3 9
4 9
5 9
6 9
7 9
8 9
where the
i1-m13 is your example CSV input from message 13.
As was mentioned by
sko , this example run confirms a constant number of values/fields/columns: 9.
Using proper quoting, goawk seems capable to process values that contain a new line:
Code:
[1-0] % cat i9
a;"bla
blup";c
d;e;f
x;y;z
[2-0] % goawk -i 'csv separator=;' '{ print NR,NF }' i9
1 3
2 3
3 3
P.S. I'm completely unfamiliar with the mentioned
pandas.read_csv
However, your problem seems to be about a CSV that has a small number a values/fields (=9), and also a small number of transactions; using goawk seems workable.[/icode]
