csv cronjob to import in mysql database table

[Nermin]

Hello,

I am having a CSV daily sent via ftp to my public_html folder on a website. I am trying to make a cron job to import it into database called "grijanjetuzla_stanjeracuna" in table "saldo" , and delete records in table "saldo" it next day so I can import it again with fresh data.

This is what I have so far:

mysql --host localhost --user grijanjetuzla_saldouser --password="PASSWORD" -D stanjeracuna -e "TRUNCATE TABLE saldo;" ;

mysql --host localhost --user grijanjetuzla_saldouser --password="PASSWORD" -D grijanjetuzla_stanjeracuna -e "LOAD DATA LOCAL INFILE "/home4/username/public_html/Saldo.csv" IGNORE INTO TABLE saldo COLUMNS ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\r\n';";

Error I got is this:
/bin/sh: -c: line 0: unexpected EOF while looking for matching `''
/bin/sh: -c: line 1: syntax error: unexpected end of file

Does anybody know how to import it, I've spent days on this issue.
It does import thru phpmyadmin but not thru cron job.

Thanks

 

[ffeingol]

First, I'd normally not suggest posting password in a public forum. Having said that, it may be the "&" in the password. You might want to try it as "uuu\&uuu" . The "\" will "escape" the ampersand. Otherwise it may be interpreted as part of shell command.

 

[cPRex]

I'd normally not suggest posting password in a public forum.

Definitely. @Nermin - I've edited your post to remove that password. Please try and keep all identifiable data (IP addresses, domains, usernames, email addresses, passwords, etc.) out of posts.

Can you try @ffeingol's suggestion to see if that helps?

 

[Nermin]

First, I'd normally not suggest posting password in a public forum. Having said that, it may be the "&" in the password. You might want to try it as "3,we\&K7n4]kR" . The "\" will "escape" the ampersand. Otherwise it may be interpreted as part of shell command.

Ok, I was going to change password anyway, because website is not active. I will not post anymore id data, sorry.
Anyway, I still have this issue:

This is the error I got now when changed password (pass is simple only text and two numbers, no special characters):
/bin/bash: -c: line 0: unexpected EOF while looking for matching `''
/bin/bash: -c: line 1: syntax error: unexpected end of file

I have tried to make PHP script to do this import and it does the job, but it imports it with wrong encoding. There are no čšćđž characters. CSV is in UTF-16LE. Then, I have tried to use cronjob with this command "iconv -c --from-code=UTF-16LE --to-code= ASCII//TRANSLIT public_html/Saldo.csv > public_html/Saldo_ascii.csv" in order to import čšćđž characters but it iports cscdz instead. It is some improvement but not solution.
$csvFile = fopen('Saldo_ascii.csv', "r");
fgetcsv($csvFile);
while(($line = fgetcsv($csvFile)) !== FALSE){
$name = $line[0];
$email = $line[1];
$phone = $line[2];
$status = $line[3];
$db->query("INSERT INTO `Saldo` (`col_1`, `col_2`, `col_3`, `col_4`) VALUES ('".$name."', '".$email."', '".$phone."','".$status."')");
}
fclose($csvFile);
$qstring = '?status=succ';

Also I have tryed with this code:
mysqlimport --host=localhost --local -u grijanjetuzla_saldouser –columns=col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8 -pPASSWORD grijanjetuzla_stanjeracuna /home4/grijanjetuzla/public_html/Saldo.csv --fields-terminated-by="," --lines-terminated-by="\r\n" --ignore-lines=1
but with no luck.

I am trying to resolve this import for days.
Any help would be appriciated!

Thank you

 

[Spirogg]

iconv -c --from-code=UTF-16LE

maybe try UTF16LE_BOM not 100% sure but worth a try ?

 

[Nermin]

I've managed to make it work with this code:
mysqlimport --host=localhost --local -u grijanjetuzla_saldouser -pPASSWORD grijanjetuzla_stanjeracuna /home4/grijanjetuzla/public_html/saldo.csv --fields-terminated-by=',' --lines-terminated-by='\n' --default-character-set=utf16le_bin

Thanks!