A CSV file, as the name suggests, contains a list of values separated by comma. The end of each row is marked by a line break. This is one of the oldest file format used in computers and hence is supported in almost all operating systems. In this post, I will describe how to use a CSV file to populate a MySQL database.
First of all, to make things simple, lets see an example where a CSV file is read and the data is displayed in a HTML table. Here is the code.
In the above example the CSV file is located on another domain. But it doesn't matter. You can replace it with the path to your CSV file. You can also use an upload form to upload CSV file. The function fopen() is used to open the file in readonly mode. The function fgetcsv() reads the CSV file line by line and puts it in an array. Looping through the array, each value can be obtained which is echoed within the correct HTML tags to obtain the table. This is quite simple and in this case, you need not know the number of fields before hand. But if you want to populate a database, then you should know the number of fields per row and also the order in which they are present in the CSV file.
Now lets look at the code to populate a MySQL table using data from a CSV file. It is assumed that the table and the CSV file contains three fields in the same order.
The code is very much similar to the previous one and requires no explanation. Make sure to replace the MySQL connection parameters, the location of CSV and the database and table names. Now go ahead and try this by yourself.
Please feel free to drop in your comments.
First of all, to make things simple, lets see an example where a CSV file is read and the data is displayed in a HTML table. Here is the code.
<TABLE border="1" > <?php $url = "http://www.example.com/file.csv"; $fp = fopen($url, "r"); while($data = fgetcsv($fp, NULL, ",")) { echo '<TR>'; foreach($data as $d) { echo '<TD>'.$d.'</TD>'; } echo '</TR>'; } ?> </TABLE>
In the above example the CSV file is located on another domain. But it doesn't matter. You can replace it with the path to your CSV file. You can also use an upload form to upload CSV file. The function fopen() is used to open the file in readonly mode. The function fgetcsv() reads the CSV file line by line and puts it in an array. Looping through the array, each value can be obtained which is echoed within the correct HTML tags to obtain the table. This is quite simple and in this case, you need not know the number of fields before hand. But if you want to populate a database, then you should know the number of fields per row and also the order in which they are present in the CSV file.
Now lets look at the code to populate a MySQL table using data from a CSV file. It is assumed that the table and the CSV file contains three fields in the same order.
<?php $conn = mysql_connect("localhost", "username", "password"); mysql_select_db("mydatabase"); $url = "http://www.example.com/file.csv"; $fp = fopen($url, "r"); while($data = fgetcsv($fp, NULL, ",")) { $query = "INSERT INTO tablename (field1, field2, field3) VALUES ('$data[0]', '$data[1]', '$data[2]')"; mysql_query($query); } ?>
The code is very much similar to the previous one and requires no explanation. Make sure to replace the MySQL connection parameters, the location of CSV and the database and table names. Now go ahead and try this by yourself.
Please feel free to drop in your comments.
ths code giving not purfect result insted of actuall result giving some garbase value ;
ReplyDeleteDoes your CSV file match the description given in the first paragraph? I find a lot of so-called CSV files that are actually TSV (Tab Separated Value) files or sometimes containing even more weird stuff. I may be able to help you if I see your CSV file.
Delete