I would like to import all csv files in a directory into MySQL using a batch script. The data within the csv files is enclosed by quotes, which I attempt to wa MySQL about during the data load (see script below). My data load command is failing due to a " character. I need help understanding how to escape the " character so that this script successfully completes:
@ECHO off
setlocal enabledelayedexpansion
FOR %%f IN ("*.csv") DO (
set myfile=!C:\Users\xxx\%%f!
mysql -e "load data local infile '"!myfile!"' into table db.test fields terminated by ',' enclosed by '"' lines terminated by '' IGNORE 1 LINES (@vtime,@vtag,@vvalue) SET eventID=null,time=STR_TO_DATE(@vtime, '%c/%e/%Y %l:%i:%s %p'),tag=nullif(@vtag,''),value=nullif(@vvalue,'');" -u root -p
echo %%~nxf DONE
)
If I delete [enclosed by '"'] the batch script runs fine, but then the data is enclosed by quotes which doesn't work with my table's datatype and I would like to avoid this. The load data query runs fine within MySQL, so the error seems tied to the batch script.
I've tried using ^", ", /", and "" in place of " and none of these have worked. The error received is "SET was unexpected at this time." If I use "" then I get what looks like a mysql man page.