Parse text file add semicolons before and after numbers; join all other words that are before/ between numbers with hyphens

Question

I have a text file with accounts like:

Input Sample

Paid 100 15/02/2022 3000
recd 50 15/02/2022 nelur trip 3050
PAID 80 25/03/2022 Adjusted towards trip 3130
14 PAID 50  26/03/2022 Given to Nate Cash (padma ac) 3180

I want to ignore numbers at start or join them to first word, then first word(s) join them with a dash/hyphen there is a number put a ; then a date put a ; then words so put a ; and join them then a number so put a ; remove white space except new lines

Can be a script too, perl/ php, bash, awk, sed anything that I can easily install and run on my linux laptop.

Output sample

Paid;100;15/02/2022;3000
recd;50;15/02/2022;nelur-trip;3050
PAID;80;25/03/2022;Adjusted-towards-trip;3130
14-PAID;50;26/03/2022;Given-to-Nate-Cash-(padma-ac);3180

I want to import it to a spreadsheet so any other way is fine too. There around 300 lines.

What did I try before? Using spreadsheet to clean up the data that I got from an image and then a image to text convertor.

Also tried with notepad++ and column select. i briedly tried to learn AWK. I know Java but this would take too long on that. Few things I learnt after getting the answers:

1. leave line numbers if you have them helps to know which rows are missing if you use awk. AS the output is given without any warning of lines skipped. might be a setting to bypass that, not sure
2. always ask for accounts in csv, spreadsheet or PDF and dont accept images!

I got the input by running a image to text on images I got from a friend on our accounts

Answer 1

perl -pe 's/(?:^[0-9]+|[^0-9])\K (?=[^0-9])/-/g;
          s/ /;/g
' -- file

• -p reads the input line by line and prints each line after processing;
• The first substitution uses look-around assertions: it says replace a space by a dash if it was preceded by something that was a number at the beginning of the string or that wasn't a number and was followed by something that wasn't a number;
• The second substitution replaces the remaining spaces by semicolons.

If the two spaces after 50 on the last line aren't a typo, you need to change the second substitution to s/ +/;/g to replace all the adjacent spaces to a single semicolon.

Answer 2

Since you already have an answer...

Using GNU awk for the 3rd arg to match():

awk -v OFS=';' '
    match($0,/\S\s+([0-9]+)\s+(([0-9]{2}\/){2}[0-9]{4})(\s+(.*\S))? ([0-9]+)$/,a) {
        $0 = substr($0,1,RSTART) OFS a[1] OFS a[2] OFS a[5] OFS a[6]
        gsub(/\s+/,"-")
        print
    }
' file
Paid;100;15/02/2022;;3000
recd;50;15/02/2022;nelur-trip;3050
PAID;80;25/03/2022;Adjusted-towards-trip;3130
14-PAID;50;26/03/2022;Given-to-Nate-Cash-(padma-ac);3180

The above assumes your input format is:

<anything1> <integer1> <date>[ <anything2>] <integer2>
  (.*\S)     ([0-9]+)     ^    ( (.*\S))?    ([0-9]+)
                          |
              (([0-9]{2}\/){2}[0-9]{4})

where there can be any non-newline white space between fields, the anything fields inside <...> always end with a non-space but can contain spaces or any other characters, cannot have a date string preceded by an integer in anything1, [ <anything2>] is optional, and you want the output given that to be:

<anything1>;<integer1>;<date>;<anything2>;<integer2>

That means that Paid;100;15/02/2022;3000 in the expected output should actually be Paid;100;15/02/2022;;3000 so every output line has the same number of fields for importing into a spreadsheet as the OP says they want to do.

I'm using a single \S at the start of the regexp and then using substr() to get the head anything string instead of using (.*\S) to populate an array element as I originally intended, and would work with the posted sample input in the question, as I realised that would fail given input like:

Paid 100 15/02/2022 foo 100 15/02/2022 3000

where there are 2 dates in the line and the strings around the 2nd one match the rest of the regexp, e.g. given this modified input with the problematic line at the bottom:

$ cat file
Paid 100 15/02/2022 3000
recd 50 15/02/2022 nelur trip 3050
PAID 80 25/03/2022 Adjusted towards trip 3130
14 PAID 50  26/03/2022 Given to Nate Cash (padma ac) 3180
Paid 100 15/02/2022 foo 100 15/02/2022 3000

note the undesirable last line of output if we used (.*\S) at the start of the regexp:

$ awk -v OFS=';' '
    match($0,/(.*\S)\s+([0-9]+)\s+(([0-9]{2}\/){2}[0-9]{4})(\s+(.*\S))? ([0-9]+)$/,a) {
        $0 = a[1] OFS a[2] OFS a[3] OFS a[6] OFS a[7]
        gsub(/\s+/,"-")
        print
    }
' file
Paid;100;15/02/2022;;3000
recd;50;15/02/2022;nelur-trip;3050
PAID;80;25/03/2022;Adjusted-towards-trip;3130
14-PAID;50;26/03/2022;Given-to-Nate-Cash-(padma-ac);3180
Paid-100-15/02/2022-foo;100;15/02/2022;;3000

vs the correct output using the suggested script:

$ awk -v OFS=';' '
    match($0,/\S\s+([0-9]+)\s+(([0-9]{2}\/){2}[0-9]{4})(\s+(.*\S))? ([0-9]+)$/,a) {
        $0 = substr($0,1,RSTART) OFS a[1] OFS a[2] OFS a[5] OFS a[6]
        gsub(/\s+/,"-")
        print
    }
' file
Paid;100;15/02/2022;;3000
recd;50;15/02/2022;nelur-trip;3050
PAID;80;25/03/2022;Adjusted-towards-trip;3130
14-PAID;50;26/03/2022;Given-to-Nate-Cash-(padma-ac);3180
Paid;100;15/02/2022;foo-100-15/02/2022;3000

---

EDIT based on feedback in comments - to log any lines that do not match the regexp:

awk -v OFS=';' '
    match($0,/\S\s+([0-9]+)\s+(([0-9]{2}\/){2}[0-9]{4})(\s+(.*\S))? ([0-9]+)$/,a) {
        $0 = substr($0,1,RSTART) OFS a[1] OFS a[2] OFS a[5] OFS a[6]
        gsub(/\s+/,"-")
        print
        next
    }
    { print > "/dev/stderr" }
' file 2>unmatched.log

Answer 3

sed -E 's,([^0-9 ]) +([^0-9 ]),\1-\2,g
        s,^([0-9]+) +,\1-,g
        s, +,;,g
'

Answer 4

Using Raku (formerly known as Perl_6)

First see if you can get all lines to parse:

~$ raku -ne '.match(/ ^ \d* \s? <.alpha>+ \s \d+ \s [ \d**2 \/ \d**2 \/ \d**4 ] \s [[<.graph>+]+ % " " \s]? \d+ $ /).say;'  file

Above is an approach in Raku, a Unicode-ready member of the Perl-family of programming languages. Because you're starting with a complex Regex problem, it's best to see that all lines parse correctly from /^ … $/ beginning to end. (You'll probably want to run something like raku -pe 's:g/\s+/ /;' to normalize whitespace first). The code above will return Nil for lines that don't parse correctly, and here's the result for the OP's whitespace-normalized data:

Sample Input:

Paid 100 15/02/2022 3000
recd 50 15/02/2022 nelur trip 3050
PAID 80 25/03/2022 Adjusted towards trip 3130
14 PAID 50 26/03/2022 Given to Nate Cash (padma ac) 3180

Parsed Lines:

｢Paid 100 15/02/2022 3000｣
｢recd 50 15/02/2022 nelur trip 3050｣
｢PAID 80 25/03/2022 Adjusted towards trip 3130｣
｢14 PAID 50 26/03/2022 Given to Nate Cash (padma ac) 3180｣

Now that you see all lines are parsed from beginning-to-end, you can capture matching elements. Denote captures with parentheses, noting that Raku captures start from $0:

~$ raku -ne '.match(/ ^ (\d* \s? <.alpha>+) \s (\d+) \s ( \d**2 \/ \d**2 \/ \d**4 ) \s [(<.graph>+)+ % " " \s]? (\d+) $ /).say;'  file
｢Paid 100 15/02/2022 3000｣
 0 => ｢Paid｣
 1 => ｢100｣
 2 => ｢15/02/2022｣
 4 => ｢3000｣
｢recd 50 15/02/2022 nelur trip 3050｣
 0 => ｢recd｣
 1 => ｢50｣
 2 => ｢15/02/2022｣
 3 => ｢nelur｣
 3 => ｢trip｣
 4 => ｢3050｣
｢PAID 80 25/03/2022 Adjusted towards trip 3130｣
 0 => ｢PAID｣
 1 => ｢80｣
 2 => ｢25/03/2022｣
 3 => ｢Adjusted｣
 3 => ｢towards｣
 3 => ｢trip｣
 4 => ｢3130｣
｢14 PAID 50 26/03/2022 Given to Nate Cash (padma ac) 3180｣
 0 => ｢14 PAID｣
 1 => ｢50｣
 2 => ｢26/03/2022｣
 3 => ｢Given｣
 3 => ｢to｣
 3 => ｢Nate｣
 3 => ｢Cash｣
 3 => ｢(padma｣
 3 => ｢ac)｣
 4 => ｢3180｣

---

Final answer -- Use either Raku's .subst( /…/, {…} ) or Raku's s/// substitution commands/idioms. Note: both these substitution idioms take "Callable" code in within curlie braces in the replacement, greatly increasing the power of each command. So you can .translate remaining whitespace characters into - hyphens, then join on ; semicolons:

~$ raku -ne '.subst(/ ^ (\d* \s? <.alpha>+) \s (\d+) \s ( \d**2 \/ \d**2 \/ \d**4 ) \s [(<.graph>+)+ % " " \s]? (\d+) $/,  \
             {join ";", $0.trans(" " => "-"), $1, $2, $3.trans(" " => "-") // "", $4}).put;'  file
#OR:
~$ raku -pe 's/ ^ (\d* \s? <.alpha>+) \s (\d+) \s ( \d2 / \d2 / \d**4 ) \s [(<.graph>+)+ % " " \s]? (\d+) $  

              /{join ";", $0.trans(" " => "-"), $1, $2, $3.trans(" " => "-") // "", $4}/;'  file

Sample Output (from either solution immediately above):

Paid;100;15/02/2022;;3000
recd;50;15/02/2022;nelur-trip;3050
PAID;80;25/03/2022;Adjusted-towards-trip;3130
14-PAID;50;26/03/2022;Given-to-Nate-Cash-(padma-ac);3180

Bonus points: You can normalize case by using something like {$0.uc} to convert the first column all to PAID, RECD, etc.

Simplifying assumptions: You can simplify the Regex to use \d for digit and \D for non-digit, but you risk not parsing lines with mixed non-digit/digit "words", like UK postcodes or Airline confirmation codes (especially in Column 4).

https://docs.raku.org/routine/subst#Callable
https://docs.raku.org/language/regexes
https://raku.org