csv cronjob to import in mysql database table

Operating System & Version
CentOS v7.9.2009
cPanel & WHM Version
cPanel & WHM v102.0.18 (STANDARD)

Nermin

Member
Mar 7, 2017
16
4
53
Tuzla
cPanel Access Level
Root Administrator
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
 
Last edited by a moderator:

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
743
302
363
cPanel Access Level
DataCenter Provider
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.
 
Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
11,731
1,864
363
cPanel Access Level
Root Administrator
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

Member
Mar 7, 2017
16
4
53
Tuzla
cPanel Access Level
Root Administrator
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
 

Nermin

Member
Mar 7, 2017
16
4
53
Tuzla
cPanel Access Level
Root Administrator
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!
 
  • Like
Reactions: cPRex and Spirogg