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

11/DEC/2013 will require a slight change.

Use this and see notes below for importing into Excel:

#/bin/bash

Report=~/Desktop/Report.txt


for f in ~/Downloads/*
do
     if [ -f "$f" ] ; then
          DATE=$(/usr/bin/grep -E -o -m1 '[0-9]{2}/[A-Za-z]{3}/[0-9]{4}' "$f" | sed 's/\([0-9][0-9]\/\)\([A-Za-z][A-Za-z][A-Za-z]\/*\)/\2\1/')
          CODE=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of CODE' "$f" | sed 's/[^0-9]*//g')
          DATA=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of DATA' "$f" | sed 's/[^0-9]*//g')
          XDATA=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of XDATA' "$f" | sed 's/[^0-9]*//g')
          FARCODE=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of FARCODE' "$f" | sed 's/[^0-9]*//g')
          echo $DATE ${f##*/} $CODE $DATA $XDATA $FARCODE >> "$Report"
     fi
done

Open Excel, File->Open Reports.txt, Select Click [Next], select Click [Next], selct for Col 1, Click [Finish]

ChrisAbreu wrote:

Tony your last suggestion worked like a charm except for the date field! If you could please tell me how to change it so it reads it properly I would be so grateful! Again it should read it as 08/Dec/2012 etc.

Use this and see notes below for importing into Excel:

#/bin/bash

Report=~/Desktop/Report.txt

for f in ~/Downloads/*
do
     if [ -f "$f" ] ; then
          DATE=$(/usr/bin/grep -E -o -m1 '[0-9]{2}/[A-Za-z]{3}/[0-9]{4}' "$f" | sed 's/\([0-9][0-9]\/\)\([A-Za-z][A-Za-z][A-Za-z]\/*\)/\2\1/')
          CODE=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of CODE' "$f" | sed 's/[^0-9]*//g')
          DATA=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of DATA' "$f" | sed 's/[^0-9]*//g')
          XDATA=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of XDATA' "$f" | sed 's/[^0-9]*//g')
          FARCODE=$(/usr/bin/grep -E -o -m1 '^[ [:blank:] | [:digit:] ].*bytes of FARCODE' "$f" | sed 's/[^0-9]*//g')
          echo $DATE ${f##*/} $CODE $DATA $XDATA $FARCODE >> "$Report"
     fi
done

Open Excel, File->Open Reports.txt, Select Click [Next], select Click [Next], select for Col 1, Click [Finish]

Hello

You may try the following script, provided that you need, e.g., 12/08/2012 in output file for 08/Dec/2012 in input file.

Regards,

H

set f to (choose folder with prompt "Choose the root folder to start searching")'s POSIX path
if f ends with "/" then set f to f's text 1 thru -2

do shell script "/usr/bin/perl -CSDA -w <<'EOF' - " & f's quoted form & " > ~/Desktop/out.csv
use strict;
use open IN => ':crlf';

# retrieve map files
chdir $ARGV[0] or die qq($!);
local $/ = qq(\\0);
my @ff = map {chomp; $_} qx(find . -type f -iname '*.map' -print0);
local $/ = qq(\\n);

# 
#     CSV spec
# 
#     - record separator is CRLF
#     - field separator is comma
#     - every field is quoted
#     - text encoding is UTF-8
# 
local $\\ = qq(\\015\\012);    # CRLF
local $, = qq(,);            # COMMA

# build months table (name => ordinal number)
my $i = 0;
my %months = map { $_ => ++$i } qw(jan feb mar apr may jun jul aug sep oct nov dec);

# print header row
my @dd = ('column 1', 'column 2', 'column 3', 'column 4', 'column 5', 'column 6');
print map { s/\"/\"\"/og; qq(\").$_.qq(\"); } @dd;

# print data row per file
while (@ff) {
    my $f = shift @ff;    # file path
    if ( ! open(IN, '<', $f) ) {
        warn qq(Failed to open $f: $!);
        next;
    }
    $f =~ s%^.*/%%og;    # file name
    @dd = ('', $f, '', '', '', '');
    while (<IN>) {
        chomp;
        my $m;
        $dd[0] = (($m = $months{lc $2}) ? sprintf('%02d', $m) : '??') . \"/$1/$3\" if m%Link Time\\s+=\\s+([0-9]{2})/(.{3})/([0-9]{4})%o;
        ($dd[2] = $1) =~ s/ //g if m/([0-9 ]+)\\s+bytes of CODE\\s/o;
        ($dd[3] = $1) =~ s/ //g if m/([0-9 ]+)\\s+bytes of DATA\\s/o;
        ($dd[4] = $1) =~ s/ //g if m/([0-9 ]+)\\s+bytes of XDATA\\s/o;
        ($dd[5] = $1) =~ s/ //g if m/([0-9 ]+)\\s+bytes of FARCODE\\s/o;
        last unless grep { /^$/ } @dd;
    }
    close IN;
    print map { s/\"/\"\"/og; qq(\").$_.qq(\"); } @dd;
}
EOF"

Hi Guyzs,

I'd like to create an action that will enable me to list my file names in Numbers, file type and date of creation and modification.

The thing is that I have 900 documents (PDFs, ppt, xls, doc, etc...) contained in folders and sub folders, to list one by one in Numbers, with 4 columns: file name (just the name, not the extension, to be in column A), file extension (with the DOT before the extension name, to be in column B), creation date (to be in column E) and last modification date (to be in column F).

I'm very new to Automator and Apple Scripts, as I only tried long time ago once or twice and miserably failed, while aided by tutorials.

Regards,

Fab'

Leborde wrote:

Hi Guyzs,

I'd like to create an action that will enable me to list my file names in Numbers, file type and date of creation and modification.

The thing is that I have 900 documents (PDFs, ppt, xls, doc, etc...) contained in folders and sub folders, to list one by one in Numbers, with 4 columns: file name (just the name, not the extension, to be in column A), file extension (with the DOT before the extension name, to be in column B), creation date (to be in column E) and last modification date (to be in column F).

This will create a Text Report on the Desktop with what you want.

In Terminal:

Lists ALL files in the Documents Folder:

find ~/Documents -exec stat -f "%N %Sc %Sm" {} \; | sed 's/\/.*\///' > ~/Desktop/Report.txt

To only list xls and doc files:

find ~/Documents \( -iname *.xls -o -iname *.doc \) -exec stat -f "%N %Sc %Sm" {} \; | sed 's/\/.*\///' > ~/Desktop/Report.txt

See if this can be imported into Numbers

Not too eligent, but this command in Terminal will create a CSV file on your Desktop that you can then open in Numbers.

This searches for xls, doc, and pdf files starting from the Documents Folder (edit to start from another folder and to add more file types)

find ~/Documents \( -iname *.xls -o -iname *.doc -o -iname *.pdf \) -exec stat -f "%N %Sc %Sm" {} \; | sed 's/\/.*\///' | sed -e 's/^[^\.]*/&,/' -e 's/\.[^ ]*/&,/' -e 's/, [A-Z].*[1-2][0-9][0-9][0-9] /&,/' -e 's/ ,/,/' -e 's/, /,/' > Desktop/Report.csv

(You can run this in Automator by copying the above into the Automator Service: Utilities->Run Shell Script)

Hi Tony,

I have several hundred text files that contain a lot of information. Each file has 3 columns (the first two are the same for all the files).

I need to merge the third column of all the files in a new file. And insert a column header with the name of the file from where the column belongs.

The txt files with the three columns like this:

-118.33333333333279 40.041666666667908 11.409999847412109

-118.29166666666612 40.041666666667908 11.090000152587891

-118.24999999999946 40.041666666667908 10.920000076293945

-118.20833333333279 40.041666666667908 10.949999809265137

The txt file I am trying to create should look like this:

Is this possible? I can't find a way to do so. Please help!!!

Pepo

Best to post this as a new topic.

Try "R" - it's a statistical scripting language/command shell utility that can handle vectors and matrices, not unlike matlab. It should be able to handle this, I think.