Hi all, i’m trying to make an script that load a file from a .cvs format to mysql. The script works right with this instruction
"'LOAD DATA LOCAL INFILE \"/Users/carlosperez/Desktop/documentos.csv\" REPLACE INTO TABLE documentos '"
But when i try to use \ to scape and " to enclose fields, the next error occur:
“sh: -c: line 1: unexpected EOF while looking for matching `‘’
sh: -c: line 2: syntax error: unexpected end of file”
this is the right instruction for the terminal:
mysql> LOAD DATA LOCAL INFILE “/Users/carlosperez/Desktop/documentos.csv” REPLACE INTO TABLE documentos FIELDS TERMINATED BY ‘,’ ENCLOSED BY '"'ESCAPED BY ‘\’ LINES TERMINATED BY ‘\r’;
and this is the applescript
set mysql_user to "root"
set mysql_host to "localhost"
set mysql_pw to ""
set mysql_db to "calidad"
--set archivo to (choose file)
--set PSXpath to POSIX path of archivo
try
set mySQLInsertInTo to "'LOAD DATA LOCAL INFILE \"/Users/carlosperez/Desktop/documentos.csv\" REPLACE INTO TABLE documentos FIELDS TERMINATED BY \"','\" ENCLOSED BY \"'\"'\" ESCAPED BY \"'\\'\"LINES TERMINATED BY \"'\\r'\"'"
set MySQLConnection to do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e " & mySQLInsertInTo
on error e
return e
end try
i don’t get the error when i quote the string this way:
set mysql_user to "root"
set mysql_host to "localhost"
set mysql_pw to ""
set mysql_db to "calidad"
--set archivo to (choose file)
--set PSXpath to POSIX path of archivo
try
set mySQLInsertInTo to "LOAD DATA LOCAL INFILE \"/Users/carlosperez/Desktop/documentos.csv\" REPLACE INTO TABLE documentos FIELDS TERMINATED BY ',' ENCLOSED BY '\"'ESCAPED BY '\\' LINES TERMINATED BY '\\r'"
set MySQLConnection to do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e " & mySQLInsertInTo
on error e
return e
end try
does that work for you? if so, just a mess of quotes to sort out.
"/usr/local/mysql/bin/mysql Ver 14.7 Distrib 4.1.10a, for apple-darwin7.7.0 (powerpc)
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
well, from the looks of things the quotes aren’t such a problem anymore. the ‘error’ you are getting is just the typical startup of MYSQL. seems like it’s not getting your ‘-D’ command or anything after that. you could try ‘writing’ your ‘do shell script’ out to a file to see what the commands look like to the terminal like this:
set mysql_user to "root"
set mysql_host to "localhost"
set mysql_pw to ""
set mysql_db to "calidad"
--set archivo to (choose file)
--set PSXpath to POSIX path of archivo
try
set mySQLInsertInTo to "LOAD DATA LOCAL INFILE \"/Users/carlosperez/Desktop/documentos.csv\" REPLACE INTO TABLE documentos FIELDS TERMINATED BY ',' ENCLOSED BY '\"'ESCAPED BY '\\' LINES TERMINATED BY '\\r'"
set MySQLConnection to do shell script "/bin/echo /usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e " & mySQLInsertInTo & " > /MYSQLCommand"
on error e
return e
end try
once the file is written out, you should be able to run it as a shell script. if not, the problem is not in AppleScript.
as a word of advice, in the past i’ve written out commands like this to a file and then done something like:
no problem. here is another stab. this time i included everything in one file. i also changed a few things. let me know how this does:
set mysql_user to "root"
set mysql_host to "localhost"
set mysql_pw to ""
set mysql_db to "calidad"
--set archivo to (choose file)
--set PSXpath to POSIX path of archivo
try
set mySQLInsertInTo to quoted form of "LOAD DATA LOCAL INFILE \"/Users/carlosperez/Desktop/documentos.csv\" REPLACE INTO TABLE documentos FIELDS TERMINATED BY ',' ENCLOSED BY '\"'ESCAPED BY '\\' LINES TERMINATED BY '\\r';"
set MySQLConnection to do shell script "/bin/echo /usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e " & mySQLInsertInTo & " > /MYSQLCommand"
do shell script "/bin/chmod +x /MYSQLCommand"
do shell script "/bin/sh -c " & "/MYSQLCommand"
do shell script "/bin/rm -rf /MYSQLCommand"
on error e
return e
end try
also, instead of just running this and reporting back, try to run the /MYSQLCommand from the terminal and see if it works. also, open it in text editor or something and look to make sure the commands are right. i don’t know MYSQL well enough to tell myself.
man, i am been tankful for your help, i try the script in comand line and get the same error that i have in applescript when i use sintax as in mysql console.
Ver 14.7 Distrib 4.1.10a, for apple-darwin7.7.0 (powerpc)Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: /usr/local/mysql/bin/mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to use
‘rehash’ to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
–disable-auto-rehash.
ok, so i think the AppleScript itself is sound, but our shell script within is the problem.
when you look at /MYSQLCommand, does it look right? if there’s anything that you think should be changed, do so, and test it in the Terminal until it works right.
if you can do that and post the correct syntax, i can correct the ‘do shell script’ portion of our AppleScript.
for completeness, here is the content of /MYSQLCommand on my Mac:
/usr/local/mysql/bin/mysql -D calidad -u root -h localhost -p -e LOAD DATA LOCAL INFILE "/Users/carlosperez/Desktop/documentos.csv" REPLACE INTO TABLE documentos FIELDS TERMINATED BY ',' ENCLOSED BY '"'ESCAPED BY '\' LINES TERMINATED BY '\r';
like i said, i don’t know MYSQL well enough to tell you what’s wrong with that. but if we can get it to work in the Terminal i can fix the AppleScript.
EDITED TO ADD: you can check the /MYSQLCommand by commenting out the ‘do shell script /bin/rm -rf /MYSQLCommand’. you probably already know that, but just for the lurkers.
good to hear. here is a final version of the AppleScript that outputs your shell script as posted. i’ve also realized i need to use admin privs to ‘rm -rf’ MYSQLCommand, so i added that, and i put a ‘try’ block in because i don’t have mysql installed on this machine. feel free to change as needed:
set mysql_user to "root "
set mysql_host to "localhost"
set mysql_pw to ""
set mysql_db to "calidad"
--set archivo to (choose file)
--set PSXpath to POSIX path of archivo
try
set mySQLInsertInTo to quoted form of "/Users/carlosperez/Desktop/documentos.csv"
set MySQLConnection to do shell script "/bin/echo /usr/local/mysql/bin/mysqlimport -fields-terminated-by=, --fields-escaped-by='\\\\' --lines-terminated-by='\"''\\r''\"' --ignore-lines=1 --user " & mysql_user & mysql_db & space & mySQLInsertInTo & " > /MYSQLCommand"
do shell script "/bin/chmod +x /MYSQLCommand"
try
do shell script "/bin/sh -c " & "/MYSQLCommand"
end try
do shell script "/bin/rm -rf /MYSQLCommand" with administrator privileges
on error e
return e
end try
