Excel

How can we import data in MySQL from CSV file

Run this SQL script either from a mysql console or from your favorite scripting language (PHP, PERL…)

LOAD DATA LOCAL INFILE ‘/path-to-file/dump_file.csv’
INTO TABLE table_name
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(Field_1, Filed_2, Field_3);

I believe the syntax is more than clear so I won’t bother explaining too much.

  • Share/Bookmark

Tags: , , , ,

Tuesday, April 22nd, 2008 MySQL, PHP No Comments

How can we export MySQL data as CSV

IN PHP:

?Download mysql2csv.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

$table2dump = 'table_name';
$file_content = '';
 
$sql = "SHOW COLUMNS FROM " . $table2dump ;
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) 
{
	$file_content .= $row['Field'].',';
}
$file_content = substr($file_content, 0, -1)."\n";
 
$sql = "SELECT * FROM " . $table2dump ;
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) 
{
	$file_content  .= implode(',', $row)."\n";
}
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv; filename=" . date("Y-m-d") . "_" . $table2dump . ".csv; size=".strlen($file_content));
echo $file_content;
exit;
  • Share/Bookmark

Tags: , , , , , ,

Tuesday, April 22nd, 2008 MySQL, PHP No Comments

Categories