Issue
I get a statement like this in text format every month. I can not import it in a spreadsheet easily.
28/03/2023 NETBANKING TRANSFER (Ref# 328012838897) 7,465.00 Cr
29/03/2023 BHAVNA CHEMIST 848.00
29/03/2023 ANUPAM STATIONERY MUMBAI 199.00
04/04/2023 SpayBBPS 2100001818 Mahan 30933624 1,134.00
The first column is date, the last (in some cases second last) column is amount. The middle part is "description"
28/03/2023 | NETBANKING TRANSFER (Ref# 328012838897) | 7,465.00 | Cr
29/03/2023 | BHAVNA CHEMIST | 848.00 |
29/03/2023 | ANUPAM STATIONERY MUMBAI | 199.00 |
04/04/2023 | SpayBBPS 2100001818 Mahan 30933624 | 1,134.00 |
Is there any way to export it to csv?
I tried this command:
awk '{print $1 " | some magic | " $NF}' test.txt
How to replace the "some magic" to the real code? I am OK if 'Cr' appear in the last column, I can change that manually.
I can get the second column using cut like this...
cat test.txt | cut -d ' ' -f2- | rev | cut -d ' ' -f2- | rev
But I am not sure how to format it with first and last column.
Solution
This might work for you (GNU sed):
sed -E 's/( .*)( [0-9,.]+) ?/ |\1 |\2 | /' file
Using greed: place 3 |
delimiters (with spaces either side), replacing the first space, the one before the last number and following the last number.
Answered By - potong Answer Checked By - Terry (WPSolving Volunteer)