0

I have a requirement to import a CSV into a mysql database on a daily basis. The CSV will be on the web server in a directory.

This is the PHP script (named invoicedataimport.php) that I have at the moment:

#!/bin/bash

DB_USER='company_reports';
DB_PASSWD='companypassword';

DB_NAME='company_reports';
TABLE='Invoices';

INPUT_FILE='/home3/company/companyreport/invoices/*.csv';

SQL="USE $DB_NAME; LOAD LOCAL DATA INFILE '$INPUT_FILE' REPLACE INTO TABLE `$TABLE` LINES TERMINATED BY ',' CHARACTER SET utf8;"
mysql --user=$DB_USER --password=$DB_PASSWD --default_character_set utf8 $DB_NAME -e "$SQL"

The reason I have used *.csv above rather than an absolute filename is that each day the CSV file will have a date stamp in the filename.

Once I have gotten the above to work - I need to figure out how to set up another cron to move all CSV files from that directory to another "Archive" directory.

I have setup a cron job - I'm not sure if I have done this right:

15 16 * * * /usr/bin/php /home3/company/companyreport/crons/invoicedataimport.php

When the time passes - I receive an email notification from my server with just the contents of the PHP file?

halfer
  • 19,824
  • 17
  • 99
  • 186
Rick
  • 45
  • 7
  • 1
    You're using `php` to run a shell script - rename it to `invoicedataimport.sh` and remove the `/usr/bin/php`. The hash-bang comment line will ensure it is run in Bash - just make sure it has execute permissions set on it (usually `chmod u+x invoicedataimport.sh` depending on the owner of the file and the owner of the cron). – halfer Sep 29 '16 at 17:27
  • (The extension of the filename does not matter, but it is useful to give it a sensible name so that it is clear what type it is). – halfer Sep 29 '16 at 17:30
  • Hi Halfer - I did the above and this is the email notification I received: /home3/company/companyreport/crons/invoicedataimport.sh: line 11: Invoices: command not found Warning: Using a password on the command line interface can be insecure. ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCAL DATA INFILE '/home3/company/companyreport/invoices/Invoices*.csv' REPLACE ' at line 1 Do you know what this means? – Rick Sep 30 '16 at 00:04
  • Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) – e4c5 Sep 30 '16 at 02:38
  • That is not the format of the syntax of `load data infile` – Drew Sep 30 '16 at 03:33
  • from what i can see the possible duplicate PHP regular backup of mysql data is to do with exporting data into a csv - when in reality, i have the data already that i want to import. all i need to do is get the script right to import the csv into my mysql db and to setup a cron to run it every day. – Rick Sep 30 '16 at 04:22
  • cheers drew, any pointers to fix it up? – Rick Sep 30 '16 at 04:23
  • Firstly, if you have SSH access to this box, disable the cron for now and run it directly on the console. It will be a slow debugging experience if you have to modify, upload, wait for the cron to kick in, and then email the results. – halfer Sep 30 '16 at 07:29
  • Secondly, if you have a syntax eror in your SQL, take a look at the docs for the command in question (search for `LOCAL DATA INFILE MySQL` in your favourite search engine) and see what needs to be amended. – halfer Sep 30 '16 at 07:30
  • OK I have tried to re-approach this and I have come up with the below: LOAD DATA INFILE '/home3/company/companyreport/invoices/Invoices*.csv' INTO TABLE Invoices FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; I put this into a .sh file and when the cron runs it comes back with: see next comment – Rick Oct 03 '16 at 00:34
  • /home3/company/company/crons/invoicedataimport.sh: line 1: LOAD: command not found /home3/company/company/crons/invoicedataimport.sh: line 2: INTO: command not found /home3/company/company/crons/invoicedataimport.sh: line 3: FIELDS: command not found /home3/company/company/crons/invoicedataimport.sh: line 4: ENCLOSED: command not found /home3/company/company/crons/invoicedataimport.sh: line 5: LINES: command not found /home3/company/company/crons/invoicedataimport.sh: line 6: IGNORE: command not found – Rick Oct 03 '16 at 00:34
  • in reply to the comment regarding SSH access - I am new to this - but I have managed to connect to the server box by putty... and when i try to run this command it indicates that LOAD: command not found – Rick Oct 03 '16 at 00:35

0 Answers0