How to import csv file to mysql

How to import csv file to mysql - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How to import csv file to mysql (/showthread.php?tid=43989)

How to import csv file to mysql - El Forum - 07-29-2011

[eluser]Near[/eluser]
Good day! may i ask how can a user upload a csv or txt file in mysql using code igniter?
in my past projects im using phpmyadmin or this code

Code:
load data infile'c://uploads//import.csv into table_name fields terminated by ','

... how can a user upload a file using upload button?

thanks

How to import csv file to mysql - El Forum - 07-30-2011

[eluser]Aken[/eluser]
Consider using a CSV parsing class to turn the CSV data into a PHP array or similar resource, then loop through it and add it to the DB using CodeIgniter's database classes.

How to import csv file to mysql - El Forum - 08-06-2011

[eluser]Jaketoolson[/eluser]
Why would you use a CSV parsing class and then loop through it when all he's trying to do is insert a CSV into MySQL? His SQL query was correct!

Just do what you've done in the past. What you pasted is a valid SQL query, so just execute it in your code after the file has been uploaded. In order to get the CSV file to the server, upload it using a form and then update the directory in your query. This is exactly what we do. You might need to change it to 'local'.

Code:
LOAD DATA LOCAL INFILE '/path/to/csvfile.csv' REPLACE INTO TABLE `table` 

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"' 

ESCAPED BY '\\' 

LINES TERMINATED BY '\r\n' 

IGNORE 1 LINES

The ignore 1 lines would be used if your first row contains headers.

How to import csv file to mysql - El Forum - 08-08-2011

[eluser]Near[/eluser]
Hi thanks for the help. i managed to insert the contents of csv files into mySQL using this query.

Code:
$fcontents = file('./uploads/'.$filename);

for($i=0;$i<sizeof($fcontents); $i++)

{

$line = trim($fcontents[$i]);

$arr = explode(",",$line);

$sql = "INSERT INTO user(name,id) VALUES ('".implode("','",$arr)."')";

$result = mysql_query($sql);

if(mysql_error())

{

$flag = 2;

return $flag;

return $filename;

die('upload failed');

}

}

else

{

$flag = 2;

return $flag;

}

How to import csv file to mysql - El Forum - 08-08-2011

[eluser]Near[/eluser]
[SOLVED]

How to import csv file to mysql - El Forum - 08-08-2011

[eluser]Jaketoolson[/eluser]
Just seems weird that you went from using a great convention for inserting CSV files into MySQL directly, to a process requiring PHP to open the CSV file and parse the contents line by line, performing an insert on each line! At least perform an extended insert if you're going to use this drawn out method. On a daily basis, using the code I pasted to you (which is the same method you used in your original inquiry), I am inserting 18-32MB csv files in a matter of seconds.

How to import csv file to mysql - El Forum - 08-08-2011

[eluser]Aken[/eluser]
I forgot that you can import files directly using MySQL. It's not something I've needed to do in a long time, so I just dove straight into a PHP solution.

How to import csv file to mysql - El Forum - 08-09-2011

[eluser]Near[/eluser]
hi Jaketoolson.. is my method of inserting records is wrong?

an why

Code:
LOAD DATA LOCAL INFILE '/path/to/csvfile.csv' REPLACE INTO TABLE `table`

i will use

Code:
REPLACE into table

instead of

Code:
INSERT

?

thanks... i will try this code later when i got home...

How to import csv file to mysql - El Forum - 08-09-2011

[eluser]nikes[/eluser]
I forgot that you can import files directly using MySQL