Welcome Guest, Not a member yet? Register   Sign In
How to import csv file to mysql
#1

[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
#2

[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.
#3

[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.
#4

[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;
}
#5

[eluser]Near[/eluser]
[SOLVED]
#6

[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.
#7

[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.
#8

[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...
#9

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




Theme © iAndrew 2016 - Forum software by © MyBB