I want to read .csv file in PHP and put its contents into the database. I wrote the following code:
$row = 1;
$file = fopen("qryWebsite.csv", "r");
while (($data = fgetcsv($file, 8000, ",")) !== FALSE) {
$num = count($data);
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "\n";}}
fclose($file);
I do not get any error but it does not show me result.
I am using parseCSV class to read data from csv files. It can give more flexibility in reading csv file.
this is not tested... but something like this should do the trick:
$row = 1;
if (($handle = fopen("xxxxxxxxx.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++) {
$blackpowder = $data;
$dynamit = implode(";", $blackpowder);
$pieces = explode(";", $dynamit);
$col1 = $pieces[0];
$col2 = $pieces[1];
$col3 = $pieces[2];
$col4 = $pieces[3];
$col5 = $pieces[5];
mysql_query("
INSERT INTO `xxxxxx`
(`xxx`,`xxx`,`xxx`,`xxxx`,`xxx`)
VALUES
('".$col1."','".$col2."','".$col3."','".$col4."','".$col5."')
");
}
}
}
LOAD DATA that can handle CSV utilising TERMINATED BY
LOAD DATA is often disabled on shared hosting due to the possible security threat.
$fp = fopen('ReadMe.csv','r') or die("can't open file");
print "<table>\n";
while($csv_line = fgetcsv($fp,1024)) {
print '<tr>';
for ($i = 0, $j = count($csv_line); $i < $j; $i++) {
print '<td>'.$csv_line[$i].'</td>';
}
print "</tr>\n";
}
print '</table>';
fclose($fp) or die("can't close file");
One liner to parse a CSV file into an array by using str_getcsv.
$csv = array_map( 'str_getcsv', file( 'qryWebsite.csv' ) );
To build a database query that will import all the values into database at once:
$query =
"INSERT INTO tbl_name (a,b,c) VALUES " .
implode( ',', array_map( function( $params ) use ( &$values ) {
$values = array_merge( (array) $values, $params );
return '(' . implode( ',', array_fill( 0, count( $params ), '?' ) ) . ')';
}, $csv ) );
This will build a prepared statement with question mark placeholders, like:
INSERT INTO tbl_name (a,b,c) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?)
, and variable $values will be one-dimensional array that holds values for the statement. One caveat here is that csv file should contain less than 65,536 entries ( maximum number of placeholders ).
If you're using the composer package manager, you can also rely on league/csv
According to theire documentation:
use League\Csv\Reader;
//load the CSV document from a file path
$csv = Reader::createFromPath('/path/to/your/csv/file.csv', 'r');
$csv->setHeaderOffset(0);
$header = $csv->getHeader(); //returns the CSV header record
$records = $csv->getRecords(); //returns all the CSV records as an Iterator object
Try this....
In PHP it is often useful to be able to read a CSV file and access it’s data. That is where the fgetcsv() function comes in handy, it will read each line of a CSV file and assign each value into an ARRAY. You can define the separator in the function as well see PHP docs for fgetcsv() for more options and examples.
function readCSV($csvFile){
$file_handle = fopen($csvFile, 'r');
while (!feof($file_handle) ) {
$line_of_text[] = fgetcsv($file_handle, 1024);
}
fclose($file_handle);
return $line_of_text;
}
// Set path to CSV file
$csvFile = 'test.csv';
$csv = readCSV($csvFile);
echo '<pre>';
print_r($csv);
echo '</pre>';
You can try the below code. It works perfect for me. I have comment to make it more understandable. You can take reference from this code.
<?php
//display error message if any
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
//openup connection to database
include('dbconnection.php');
//open csv file
if (($handle = fopen("files/cities.csv", "r")) !== FALSE) {
$flag = true;
$id=1;
//fetch data from each row
while (($data = fgetcsv($handle, ",")) !== FALSE) {
if ($flag) {
$flag = false;
continue;
}
//get data from each column
$city_id = $data[0];
$country_name = $data[1];
$city_name = $data[2];
$state_code = $data[3];
//query to insert to database
$sql = "INSERT IGNORE INTO `DB_Name`.`cities`
(`id`,`city_id`, country_name`, `city_name`,`state_code`)
VALUES
('$id','$city_id','$country_name','$city_name','$state_code')";
echo $sql;
//execute the insertion query
$retval = mysql_query($sql, $conn);
if($retval == false )
{
die('Could not enter data: ' . mysql_error());
}
echo "<p style='color: green;'>Entered data having id = " .$id. " successfully</p><br>";
$id++;
}
echo "<br><p style='color: orange;'>Congratulation all data successfully inserted</p>";
fclose($handle);
}
//close the connection
mysql_close($conn);
Try this....
$cel_values = [];`
$column_keys = [];
if (($handle = fopen("imagenes_1.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$values = [];
$num = count($data);
$row++;
for ($c = 0; $c < $num; $c++) {
if($row == 1){
$column_keys[] = $data[$c];
}else{
$values[] = $data[$c];
}
}
if($row != 1){
$cel_values[] = (object) array_combine($column_keys, $values);
}
}
fclose($handle);
}
var_dump($cel_values);
exit;
Result
array(20) {
[0]=>
object(stdClass)#1 (3) {
["id"]=>
string(10) "0000000000"
["date"]=>
string(26) "2023-02-24 15:46:44.000000"
["name"]=>
string(26) "1-16772509601764653725.png"
}
...
}
