MySQL Load Data Infile with Stored ProcedureKedarNovember 30, 2010 Did you ever need to run LOAD DATA INFILE in a procedure? May be to automate or dynamically perform the large data file load to the MySQL database. In this… View Post
Kedar
35 responses to “MySQL Load Data Infile with Stored Procedure”
Not able to download Mysql UDF from below your mentioned url and getting below error
$ wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz
–10:28:38– http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz
=> `lib_mysqludf_sys_0.0.3.tar.gz’
Resolving http://www.mysqludf.org... done.
Connecting to http://www.mysqludf.org[192.30.252.154]:80... connected.
HTTP request sent, awaiting response… 404 Not Found
10:28:39 ERROR 404: Not Found.
can somebody please create batch file command for load.sh given above, thanks in advance.
Great post.
Hi Kedar,
mysql -uusername -ppassword -e “load data local infile \”$1\” into table $2;”
In load.sh file, replaced only “ to ” and also debugging the all scenarios.
Done…. your code is work 🙂
Thank you very much…
Regards,
Aniruddha
Thanks for the information Kedar ……
“mysql> CALL sp_load_data(‘/tmp/loadtest.txt’ , ‘optimuserp_gdm.t’);
+————————————————————-+———+
| exec_str | ret_val |
+————————————————————-+———+
| sh /home/tsd/tmp/load.sh /tmp/loadtest.txt optimuserp_gdm.t | 32256 |
+————————————————————-+———+
1 row in set (0.05 sec)
+—————————————–+
| Result |
+—————————————–+
| Please check file permissions and paths |
+—————————————–+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
mysql>
”
please help how to solve this ?.Thanks
Hi Anirudhdha,
I’d encourage you to debug the issue here as such system call fails. Can you confirm you shell script is running fine manually?
See too run it from prompt:
sh /home/tsd/tmp/load.sh /tmp/loadtest.txt optimuserp_gdm.t
Thanks.
Hi all
Got it working 🙂
I created a batch file called import.bat
The contents are:
mysql --user=username --password=password --database=db_name < c:/devc_import2.sqlThe sql file is the script I created in Workbench.
In the SP the only change I made is:
set exec_str="c:/import.bat";It worked!
Hope this helps – I spent a full day trying so many permutations and it turned out to be easy.
Jake
Nice one Jake… After those permutations it must be a What the Fun moment for you 🙂
Cheers.
Hi
Did anyone manage to get this to work on a windows server?
I’ve tried but unsuccessful.
I created a SP with the following:
declare exec_str varchar(500);
set exec_str="cmd c:/devc_import2.txt";
do sys_exec(exec_str);
The dvc_import.txt is as follows:
mysql --user=username --password=password --database=bd_name -e "load data local infile 'c:/Allproperties.csv' into table devc_import FIELDS TERMINATED BY ','ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (dc_PropertyID,@DevelopmentID,@RegionID,@HouseTypeID,......)set dc_DevelopmentID = IF(@DevelopmentID='',null,@DevelopmentID),......;"
The csv file I’m trying to import has only 410 rows but has approx. 145 columns.
I can run the contents od the txt file directly form a command window and it works great but when I run the SP the browser just waits. Looking in task manager I see the cmd process but it doing nothing.
Any help most appreciated.
Jake
How about on Windows?
Hi Uma,
I’ve not tried this on Windows though I expect it should be working… would you mind sharing your attempt or error you faced!?
Regards,
Kedar.
I am trying to call a batch file from sys_exec as
do sys_exec(‘E:\load.bat’)
but it is not giving any result
Hey Red83,
Did you check the comments? I’m pasting it here again for your convenience:
Just verify:
– Make sure your dll / udf is installed & working.
– What’s your bat file? Does it execute the load command when run manually, from outside the SP?
– You changed SP, no path issues and all?
– Check if mysql can access your bat file (are you on Win7/Vista)
If sys_exec is running fine on your windows than there shouldn’t be any issue for it to run in above procedure.
I’m sorry I haven’t tried it on windows (as I’m mostly on *nix) but if you can post your changes in above code I may try to lookup !!
Let me know.
I try to execute sys_exec with string (not from file).
but it gives me error ‘Please check file permissions and paths’
please help how to solve this ?.Thanks
Hey Emmanuel,
Can you tell me what exactly you’re doing?
You can also debug this by checking out what exact value is being passed in the exec_str by adding an extra line to the procedure as follows:
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
select exec_str;
set ret_val=sys_exec(exec_str);
Let me know,
Thanks.