How to use Automator to extract specific Data from a text file?

The following code is based on assumptions:

1. The text files are all formatted exactly alike, and only the data values vary between them.
2. The text files have the extension .txt.
3. I do not have MS Excel to test with, so I am generating a CSV based on rows of data from each text file.
4. Your text files are not in multiple levels of sub-directories. I am asking for a parent folder and the text files are all in it.
5. I make no effort to sort the CSV. You can do this after you open it in Excel.

The following Ruby code receives a quoted POSIX path of the starting folder location, and a pre-determined CSV filename, which it will create on the Desktop. The application gets every text file in a list, and from that list, it parses the text file for the predesignated data. It then appends that data to the CSV file, then processes the next text file, etc.

First, here is the Ruby code as it would run from the Terminal:

And the AppleScript that you copy/paste into the Script Editor (Dock : Launchpad : Other : Script Editor), click compile, and then run.

set OUTCSV to POSIX path of ((path to desktop as text) & "ntext.csv") as text
set start_folder to (POSIX path of (choose folder))
set args to (text 1 thru -2 of start_folder)'s quoted form & space & OUTCSV's quoted form
log args
my build_csv_from_files(args)

return


on build_csv_from_files(nargs)
	return do shell script "ruby <<'EOF' - " & nargs & "
#!/usr/bin/ruby

require 'csv'

header = ['Invoice Number', 'Date', 'Grams', 'TotalAmt', 'Tax', 'Tax2',
               'Invoice Amount']

# regular expressions to isolate required column data
inv_and_date = /[A-Z]{2}[0-9]+|\\d+-\\d+-\\d+/
grams = /(?<=Grams)\\s*(\\d+\\.\\d+)$/
amounts = /Rs\\.\\d+\\.\\d+/

start_folder, outcsv = ARGV unless ARGV.length < 2

# heads only once
unless File.exist?(outcsv)
   # write unquoted headers. Switch comments if quoted headers needed.
   # CSV.open(outcsv, 'a+', headers: true,
   #           :quote_char => '\"',
   #           :force_quotes => true) do |csv|
   CSV.open(outcsv, 'a+', headers: true) do |csv|
       csv << header
    end
end

# get each text file, then extract/append data to CSV file
Dir.glob(\"#{start_folder}/*.txt\").each do |f|
  txt = File.read(f).split('\\n').sample
  csv_row = txt.scan(/#{inv_and_date}/)
  csv_row += txt.scan(/#{grams}/).flatten
  csv_row += txt.scan(/#{amounts}/)[1..-1]
  CSV.open(outcsv, 'a+', headers: true) do |csv|
    csv << csv_row
  end
end
EOF"
end build_csv_from_files

And the tested Automator solution that produces the same results as the preceding AppleScript/Ruby solution. Takes an starting folder argument, processes all .txt files in that folder, and generates an ASCII CSV on the Desktop as next.csv.

Automator Application Workflow

Ruby code for the Run Shell Script action

#!/usr/bin/ruby

"""
Process a folder of text files, extracting specific data from them
using custom regular expressions. Append rows of this data to a
comma-separated-values (CSV) file for subsequent opening in Excel.

Tested: macOS Mojave 10.14.5, Ruby 2.3.7p456
Version: 1.3
Author: vikingOSX, 2019-05-18, Apple Support Communities, no warranty.
"""

require 'csv'

header = ['Invoice Number', 'Date', 'Grams', 'TotalAmt', 'Tax', 'Tax2',
          'Invoice Amount']

# regular expressions to isolate required column data
inv_and_date = /[A-Z]{2}[0-9]+|\d+-\d+-\d+/
grams = /(?<=Grams)\s*(\d+\.\d+)$/
amounts = /Rs\.\d+\.\d+/

start_folder = ARGV.first
outcsv = File.expand_path('~/Desktop/ntext.csv')

# only one CSV header is written
unless File.exist?(outcsv)
  # write unquoted headers. Switch comments if quoted headers needed.
  # CSV.open(outcsv, 'a', headers: true,
  #          quote_char: '"',
  #          force_quotes: true) do |csv|
  CSV.open(outcsv, 'a', headers: true,
  	       col_sep: ",") do |csv|
    csv << header
  end
end

# get each text file, then extract/append data to CSV file
# appends to the csv on separate runs
Dir.glob("#{start_folder}/*.txt").each do |f|
  txt = File.read(f).split('\n').sample
  csv_row = txt.scan(/#{inv_and_date}/) + txt.scan(/#{grams}/).flatten +
            txt.scan(/#{amounts}/)[1..-1] # toss first unneeded Rs value

  CSV.open(outcsv, 'a', headers: true) do |csv|
    csv << csv_row
  end
end

Good that it was of some help, particularly in saving time.

The three regular expressions are designed to expect the capture content in the same exact place within each text document. Any variation may cause the regular expression to fail, and that particular row of CSV data would then be incomplete.

Here is the output from running the above AppleScript on a folder containing five copies of the original sample text document. In one document, I changed the grams value.