Shell Programming and Scripting

Maybe something like this:

Code:

 echo "LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/$(date +%Y-%m-%d)/traveldata.csv'" | mysql -u yourname -p

Try this alone first to judge the output:

Code:

$ echo "LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/$(date +%Y-%m-%d)/traveldata.csv'"
LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/2009-03-13/traveldata.csv'

Code:

$ echo "LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/$(date +%Y-%m-%d)/traveldata.csv'"
LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/2009-03-13/traveldata.csv'

Hey, that worked great on an echo/output -- I'm going to do some tests on a dummy table and hopefully it'll be fine!.

I've tried it this on the actual .sql file which holds the load data stuff;

Code:

LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/$(date +%Y-%m-%d)/traveldata.csv'

But I get this error;

Seems like I cannot mix and match MySQL/Shell commands inside .sql files. I tried putting the mysql date function, but this doesn't appear to work either.

Just to clarify, this is what my process is.

1. Use sh to extract contents of a zip file to a date based folder (YYYY-MM-DD)
2. Use sh to run a mysql import command

Code:

mysql -u someroot -psomepassword -h localhost somedb < /path/to/csv_import.sql

3. Import the CSV file into MySQL. The csv_import.sql is a really big long file where the LOAD DATA LOCAL INFILE exists. It needs to get the date of the latest csv file extracted.

Ideally I'd like shell to do all the heavy lifting rather than a PHP script, or perhaps there's a better way of making step 3 working?

I'm open to any ideas.

Thanks.

Code:

/path/to/inbounddata/inbounddata/data/

... is not to be taken literally. You must substitute the correct path on your system.

I know that .. I replace it with the path I use. I'm going to try again with making a date command inside a .sql file, and hopefully I can resolve the issue.