1. 

   ajdjackson, Member

   Hi

   I'm a bit stumped and was hoping that you may be able to help me out.

   I need to load a csv coming off another system. The fields are separated by " and lines terminated by CRLF.

   As you can't use LOAD DATA INFILE in a stored procedure I installed lib_mysqludf_sys on my server. It installed OK and command sys_exec() tested.

   I created a import text file which includes the LOAD DATA INFILE statement.

   It contains the following:

   mysql --user=username --password=password --database=db_name
   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,
   etc
   set dc_DevelopmentID = IF(@DevelopmentID='',null,@DevelopmentID),
   dc_RegionID = IF(@RegionID='',null,@RegionID),
   dc_HouseTypeID = IF(@HouseTypeID='',null,@HouseTypeID),
   dc_PhaseID = IF(@PhaseID='',null,@PhaseID),
   dc_Released = IF(@Released='',null,@Released),
   dc_NumericHouseNumber = IF(@NumericHouseNumber='',null,@NumericHouseNumber),
   dc_NumberOfBedrooms = IF(@NumberOfBedrooms='',null,@NumberOfBedrooms),
   dc_SquareFootage = IF(@SquareFootage='',null,@SquareFootage),
   etc);

   the Allprorties cvs file has only 410 rows but it has approx. 145 columns.

   In mydbr I've created a stored procedure as follows:

   CREATE PROCEDURE sp_DBR_Dev_Connect_Import()
   
   BEGIN
   
   declare exec_str varchar(500);
   
   set exec_str="cmd c:/devc_import2.txt";
   
   do sys_exec(exec_str);

   When I run this the browser just waits. On the server in the task manager I can see the cmd process loaded but doing nothing.

   I know that the import text file works OK as I can run it directly from cmd on windows.

   Can't think what I'm doing.

   I know that this is not directly a problem with mydbr but can you shed any light on what's going on?

   Many thanks

   Jake
2. 

   ajdjackson, Member

   Hi

   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.sql

   The 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 someone - I spent a full day trying so many permutations and it turned out to be easy.

   Jake

Reply

You must log in to post.