5

I have a case to import data into mysql automatically every 6pm. data to be imported is "data.txt".

I created in mysql like this:

CREATE EVENT EVENT_NAME
ON SCHEDULE EVERY '18: 00:00 'DAY
DO
LOAD DATA LOCAL INFILE 'd :/ data.txt'
INTO TABLE table_name
FIELDS terminated BY ','
LINES terminated BY '\ n'
(atribut1, atribut2, atribut3);

if i just write like this

LOAD DATA LOCAL INFILE 'd :/ data.txt'
INTO TABLE table_name
FIELDS terminated BY ','
LINES terminated BY '\ n'
(atribut1, atribut2, atribut3);

the query was successfully executed

but if i write the code like a fist code instead there is an error "LOAD DATA is not allowed in stored procedures". is it really like that? if it's like that how do i to handle such cases? thanks in advance sorry for my bad english

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Dhoni Eko Wahyu Nugroho
  • 51
  • 1
  • 1
  • 2

1 Answers1

1

Indeed, you are out of luck.

You will need to resort to an external mechanism in order to automate this import (such as a cron job).

You can submit arbitrary statements from outside MySQL by calling the mysql command-line client like this:

    shell > mysql [options] -D [database] -e "LOAD DATA INFILE..."
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • 1
    This -e command doesn't work anymore. :\ I'm using Powershell and all it does is spit out the manual. – ihodonald Feb 20 '19 at 23:17
  • @ihodonald I think you got confused, perhaps you meant to downvote yourself? [Are you even using MySQL at all](https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_execute)? Oh well, if that makes you feel better, suit yourself. – RandomSeed Feb 21 '19 at 16:01
  • 1
    after doing some research, I actually found out that your answer is specific to the Linux operating system. In my case, I created a scheduled task. Spicy. – ihodonald Feb 21 '19 at 23:52
  • 1
    In Windows (Powershell), you have to be explicit. `--execute`. Also, saying "you are out of luck" doesn't answer the question. – ihodonald Feb 22 '19 at 21:26