SQL import large CSV file

thimble

Hi, I wrote a simple php script to insert a comma seperated file into an sql database. The csv file is very large, and it stops loading on 1/10th of the file, so the sql databank is not updated completely... are there restrictions in size or so?? any help?

Thanks!

Johan

this is my upload script , it gives the text "database error", but 1/10th of the file is inserted. the csv file is +/- 1MB in size

<?
require("config.php");
require("connection.inc");

$row = 1;
$fp = fopen ("test.csv","r");

while ($data = fgetcsv ($fp, 1000, ",")) {

$nummer=$data[0];
$groep=$data[1];
$omschr=$data[2];
$afm=$data[3];
$prijs1=$data[4];
$prijs2=$data[5];
$verpakking=$data[6];

$query="INSERT INTO prijslijst (nummer,groep,omschr,afm,prijs1,prijs2,verpakking) VALUES ('$nummer', '$groep', '$omschr', '$afm', '$prijs1', '$prijs2', '$verpakking');";

$result = mysql_query($query) or die ("database error");

}
fclose ($fp);

jayant

See MySQL docs of the query -> LOAD DATA ... INTO ...

thoand

It could also be that your php.ini (configuration file) has a execution timeout of 30 sec. or so, also check the value of the max_upload_filesize variable.

best regards,
Thomas Andersen

thimble

Hi,

The file is already on the server, so the max file upload was not the problem. LOAD INTO doesnt work either...

i used :

$query="LOAD DATA INFILE /usr/local/psa/home/vhosts/lxxxxx.com/httpdocs/test/upload/test.csv INTO TABLE prijslijst FIELDS TERMINATED BY ',' ";
$result=mysql_query($query) or die ("error");

and i get my error...

other possibilities to load a csv file of +/-1mb into a sql database?

thanks!

Johan

jayant

You have not given proper syntax of the query

You will know it when you use mysql_error to display the description of why the error occured.

I have exam in the morning, I will post the complete query /files for this later, in case you haven't made it by then.

thimble

I was lame , and I found a script on the web which I used... It works for converting the csv data to the mysqlm database, but my csv files are with "blahblah","blahblah", for text and just without quotes ,00.25,00453.2, for numbers . For a reason I dont understand, it will not read the numbers and puts them in the last text field...

So far for that script...

Now I am not a bit further... reading the large file seems not so simple. I have written a simple function for reading csv files and put the data in a mysql database, but it doesn't wrk on the large one

Thx
johan

programmer79

Hey,

I am having the same problem. Did you ever find a fix?

thimble

Yes i fixed it...

If you disable safe mode you can add the line
set_time_limit(900)
wich sets the time limit to 15mins . Standard it's 30secs and therefor the large file will not loa completely.

Johan

boboli

I'm writing a code to let the employers insert into a specific table their excel sheet saved as csv but I'm having troubles...

can you post the code you wrote ???
consider that the data are 8 to 10 rows of 15 records each...

thank you
Roberto

thimble

This is a very basic code wich reads info out of a csv file.
It prints the data on your screen. You can put the sql code in it and write to a databank in stead of the screen

<?php
$row = 1;
$fp = fopen ("test.csv","r");
while ($data = fgetcsv ($fp, 1000, ",")) {

$num = count ($data);
print "<p> $num fields in row $row: <br>\n";
$row++;
for ($c=0; $c < $num; $c++) {
    print $data[$c] . "<br>\n"; // insert sql code here
}

}
fclose ($fp);
?>

Greetz,
Johan

boboli

thank you !!!
I'll see what I can manage....