0

I have a CSV file with approximately 657,000 records.
I want to insert this to my MySQL database.

<?php
$handle = fopen("tdvddir.csv", "r");
$data = fgetcsv($handle, 1000, ",");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
    echo "INSERT INTO `tdvddir` VALUES ('', '{$data[0]}', '{$data[1]}', '{$data[2]}', '{$data[3]}', '".addslashes($data[4])."');\n";
}
fclose($handle);
?>

php csv-sql.php > temp.sql
nohup mysql --user=root --database=data --password=password < temp.sql &

Problem is, this is taking way too much time. It's been more than 7 hours and only a little more than half got inserted so far.

I even tried importing a CSV via command-line.

load data local infile 'tdvddir.csv' into table data.tdvddir
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(col2, col3, col4, col5, col6);

ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql --version

mysql  Ver 14.14 Distrib 5.5.60, for debian-linux-gnu (x86_64) using readline 6.3


This is on my Ubuntu 14.04 laptop and not on any server.

anjanesh
  • 3,771
  • 7
  • 44
  • 58
  • https://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile – koalaok Jul 08 '18 at 11:23
  • I can see that --local-infile worked fine - thanks - Except the CSV file has 657009 lines and ID's last value is 656575 – anjanesh Jul 08 '18 at 11:27
  • From what i read. The error reported by MySQL is misleading IMHO. Since it's not about versioning but config... – koalaok Jul 08 '18 at 11:29
  • About the count mismatch you should consider relying on some indexing (unique key) to look after duplications.... or insert errors... – koalaok Jul 08 '18 at 11:31
  • I would infer that 434 lines were not formed correctly and load data skipped them. For example if they are missing fields, or have too many fields. Maybe some data contains a `,` symbol, which makes the line appear to have too many fields? – Bill Karwin Jul 08 '18 at 14:44
  • 1
    You might like my presentation [Load Data Fast!](https://www.slideshare.net/billkarwin/load-data-fast) – Bill Karwin Jul 08 '18 at 14:44
  • It may have some quotation marks - hence I used addslashes in my code which imported all the data. In case anyone is interested to know, the CSV is from http://censusindia.gov.in/2011census/censusdata2k11.aspx - http://censusindia.gov.in/2011census/tdvddir.rar – anjanesh Jul 08 '18 at 15:12

0 Answers0