1

I want to build an mysql insert script from the files in a directory

I have alias v ='ls -l --time-style=+"%Y-%m-%d %H:%M:%S"'

The directory has about 1000 files and I want to extract the files *.afl and store them into a table with a script constructed from an awk command:

ls -1 *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(<FILE CREATION DATE>,\x27%Y-%m-%d\x27),,\x27\x27,\x27)" "File Name: "$0"\n"ENVIRON["here"]"\n"source"\x27"}'

Creates this line:

INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(<FILE CREATION DATE>,'%Y-%m-%d'),'','File Name: 2011 02 21 drdttl.afl
C:\Users\athena\Downloads\Project_1\00.MBT
Source: Parallel action

I'm stuck here because I want the file timestamp to be included, so that means I can't use ls -1.

My depth of knowledge here has it's bottom.

Any savvy users have some tricks up their sleeves?

ANSWER I USED

stat -c '%y %n' *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(\x27"substr($0,1,18)"\x27,\x27%Y-%m-%d\x27),\x27\x27,\x27File Name: "substr($0,37)"\n"ENVIRON["here"]"\n"source"\x27"}'
  • 1
    Use stat instead. The -1 is useless anyway, ls applies that option by default when the output is not to a terminal – muru Sep 19 '19 at 23:59
  • 2
    stat -c '%y %n' *.afl no need for a loop or ls at all – muru Sep 20 '19 at 1:15
  • 1
    The width of the timestamp is constant. Just use whatever comes after that as the filename. Are there newlines in the filenames? – muru Sep 20 '19 at 1:30
  • 1
    Use ls without -1 in the pipe and you'll see it behaves the same. That's what I meant by -1 is useless. ls behaves differently in the pipe. – muru Sep 20 '19 at 1:32
  • 1
    Great, you can post your final version as an answer – muru Sep 20 '19 at 2:01
2

I'd use perl rather than awk for this because:

  • perl can read directory contents itself with readdir()

  • perl has a built-in stat() function, with similar capabilities as the stat command.

  • and a very nice Date::Format module for formatting dates and times

  • perl also has DBI, DBD, and DBD::mysql modules to directly interact with the database (e.g. insert the records into mysql itself).

  • ? placeholders in DBI prepared statements take ALL the pain out of escaping and quoting variables for use in SQL command strings.

  • one script does it all - and it's a lot less hassle than dealing with shell quoting or messing around with passing environment variables to awk.

#!/usr/bin/perl

use strict;
use Date::Format;
use DBI;

# Fill in your database details here.
my $database='';
my $hostname='';
my $port='';
my $user='';
my $password='';

# set up connection to database.
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password);

# set up sql statement
my $sth = $dbh->prepare('INSERT INTO action_diary (entry_date, entry_description, entry_details) VALUES (?,?,?)');

use Cwd;
my $cwd = getcwd;

# 'source' must be exported from the parent environment.
# alternatively, pass it as a command-line arg and read it from, e.g., $ARGV[0]
my $source=$ENV{'source'};

# find all .afl files in current dir, store with ctime in %files hash
# use `(stat($_))[9]` if you want the file's mtime rather than ctime.
opendir(DIR, '.') || die "Can't opendir .: $!\n";
foreach (readdir(DIR)) {
  next unless (-f "./$_" && m/\.afl$/);
  $files{$_} = (stat($_))[10];
};
closedir(DIR);

# sort the hash by value (timestamp)
foreach my $f (sort { $files{$a} <=> $files{$b} } keys %files) {
  my $Y = time2str('%Y',$files{$f});
  my $M = time2str('%m',$files{$f});
  my $D = time2str('%d',$files{$f});
  my $YMD = "$Y-$M-$D";

  my $details = "File Name: $Y $M $D $f\n$cwd\n$source";

  $sth->execute($YMD,'',$details);
}

$sth->finish();
$dbh->disconnect();

If you just want the script to output a series of SQL statements that can be saved to a file or piped into mysql, it's a little simpler:

#!/usr/bin/perl

use strict;
use Date::Format;
use Cwd;

my $cwd = getcwd;

# 'source' must be exported from the parent environment.
# alternatively, pass it as a command-line arg and read it from, e.g., $ARGV[0]
my $source=$ENV{'source'};

# find all .afl files in current dir, store with ctime in %files hash
# use `(stat($_))[9]` if you want the file's mtime rather than ctime.
my %files=();
opendir(DIR, '.') || die "Can't opendir .: $!\n";
foreach (readdir(DIR)) {
  next unless (-f "./$_" && m/\.afl$/);
  $files{$_} = (stat($_))[10];
};
closedir(DIR);

my $FMT="INSERT INTO action_diary (entry_date, entry_description, entry_details) VALUES ('%s','%s','%s')\n";

# sort the hash by value (timestamp)
foreach my $f (sort { $files{$a} <=> $files{$b} } keys %files) {
  my $Y = time2str('%Y',$files{$f});
  my $M = time2str('%m',$files{$f});
  my $D = time2str('%d',$files{$f});
  my $YMD = "$Y-$M-$D";

  my $details = "File Name: $Y $M $D $f\n$cwd\n$source";

  # backslash-escape any quotes that may be in $details (i.e. from $f or $source).
  # NOTE: very primitive.  There are lots more characters that might need escaping
  # or special handling than just a single-quote.
  $details =~ s/'/\\'/g;


  printf $FMT, $YMD,'',$details;

}
| improve this answer | |
  • I'll grant you, it is a more potent answer. It's more involved than I needed. It was a one-time event so I wanted one line to solve the problem. – Ken Ingram Sep 20 '19 at 21:49
  • It's a great example of solving a problem algorithmically, and a great exercise in breaking down a problem and showing all the tools that are available to manipulate data in a *nix system – Ken Ingram Sep 20 '19 at 21:50
  • It's not actually that much more work to write scripts like this than it is to come up with a one-liner (easier, in some ways - editing in vim is a lot nicer than editing a long bash command-line). While some details vary (e.g. the sql command and printf format string), there's not a single thing in the scripts above that I haven't written dozens or hundreds of times before - it's all "boilerplate" stuff. It's just a matter of figuring out what I want to do and how and then putting the pieces together - pretty much the same as building a pipeline of commands. – cas Sep 20 '19 at 23:52
  • e.g. for this i knew I needed to: 1. get a list of matching files. 2. get the timestamps for those files. 3. format the timestamps appropriately. 4 either print an sql command with those details, or insert records into the database. After that, the code pretty much writes itself (i have to look up details like the array returned by perl's stat() function. and there is a fair bit of trial and error involved - i am not a great programmer or even a particularly good one) – cas Sep 20 '19 at 23:53
  • 1
    I'm an OK-ish programmer with a lot of practise. And the ability to read man pages and google stuff and copy and paste examples from them (and from my own previous scripts) and, most importantly, adapt them to whatever it is i'm currently trying to do. If you can read and understand and adapt stuff, it's a lot easier than you think. – cas Sep 21 '19 at 0:10
1

Used suggestion from muru

stat -c '%y %n' *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO activity_diary (entry_date, entry_description, entry_details) VALUES (STR_TO_DATE(\x27"substr($0,1,19)"\x27,\x27%Y-%m-%d\x27),,\x27Monitoring Log\x27,\x27" "File Name: "substr($0,37)"\nFile Location: "ENVIRON["here"]"\n"source"\x27)"}' >> new_activity_data.sql
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.