PHP Functions to Import/Export CSV data
These two function make it very easy to import and export CSV in PHP.
Documentation
CSVEport()
This function has just one argument - the query that returns the necessary data to be exported.
Example: CSVExport("SELECT name,username,email,url FROM User WHERE status=1");
CSVImport()
This will upload a CSV file and import the data into the specified table. This function must have the following arguments...
- $table
- The name of the table the data must be imported to
- $fields
- An array of fields that will be used
- $csv_fieldname
- The name of the CSV file field
Example: CSVImport("User", array('name','username','email','url'), "csv_file");
Code
<?php
//Get the result of the query as a CSV stream.
//http://www.bin-co.com/php/scripts/csv_import_export/
function CSVExport($query) {
$sql_csv = mysql_query($query) or die("Error: " . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer
header("Content-type:text/octect-stream");
header("Content-Disposition:attachment;filename=data.csv");
while($row = mysql_fetch_row($sql_csv)) {
print '"' . stripslashes(implode('","',$row)) . "\"\n";
}
exit;
}
//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
//Aruguments : $table - The name of the table the data must be imported to
// $fields - An array of fields that will be used
// $csv_fieldname - The name of the CSV file field
function CSVImport($table, $fields, $csv_fieldname='csv') {
if(!$_FILES[$csv_fieldname]['name']) return;
$handle = fopen($_FILES[$csv_fieldname]['tmp_name'],'r');
if(!$handle) die('Cannot open uploaded file.');
$row_count = 0;
$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
$rows = array();
//Read the file as csv
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row_count++;
foreach($data as $key=>$value) {
$data[$key] = "'" . addslashes($value) . "'";
}
$rows[] = implode(",",$data);
}
$sql_query .= implode("),(", $rows);
$sql_query .= ")";
fclose($handle);
if(count($rows)) { //If some recores were found,
//Replace these line with what is appropriate for your DB abstraction layer
mysql_query("TRUNCATE TABLE $table") or die("MySQL Error: " . mysql_error()); //Delete the existing records
mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.
print 'Successfully imported '.$row_count.' record(s)';
} else {
print 'Cannot import data - no records found.';
}
}
Comments
you saved me from a big problem
The Third parameter will contain the input fieldname
here is an example :
<input type= "file" name="csvfile" value="">
call the function like this.
$field = array('name','email','phone');
$table = $tbl_contact
CSVImport($table, $fields, 'csvfile')
I hope now it will help you.
Best regard.
Basant
Both Are working nice...
It's good and help me too much.
Thanks.
I trouble with only one problem that how can I shown header whenever exports functionality doing?
1) prevent SQL Injection attacks with mysql_real_escape_string()
2) try to enter empty values as NULL, rather than empty (so that mysql will error correctly on non-populated fields that should be populated)
To do these, I would change your line:
$data[$key] = "'" . addslashes($value) . "'";
to:
$data[$key] = $value ? "'".mysql_real_escape_string( $value )."'" : "NULL" ;
array_shift( $rows );
// Then continue with:
$sql_query .= implode("),(", $rows);
Bob, your post about the header was just what I was looking for!
Also, thanks Bob Jones for your posts, they were just what I was looking for!
On A Side Note, I could not get your "array_shift($rows);" to work properly, but my script seems to work without shifting the array.
And thank you Bob Jones for your posts on the header managment and SQL Injection prevention.
Both Are working nice...
do i replace my user and array value in the argument list? If so, how does the $sql_query variable know what $table is?
what must i give in by "csv_file" ???
can you help me and can you give an example ?
thanks
godd !!:)
this is nice.
Can you give please give a sample code for using these functions in a HTML page so that the CSV file can be read(for import)/saved(for export) on the client machine's hard drive (via file selection dialog) ?
thanks in advance.
thanks for any help or advice
Mark
What is the meaning of this comment ?
in import function, what the problem...
column count doesn't match value count at row 1
thanks
dion
which place to dec db name
a, strong, em, b, i, code, pre, pandbrallowed. Other tags will be shown as code(< will become <). Urls, Line breaks will be auto-formated.