How to import data mocked into an Excel file into a MySql database table?

Question

I am not so into database and I have the following situation: I am working using MySQL and I have to import some data moacked into a Microsoft Excel file.

In this file the first line cells represents a table fields (each cell is a field), the below rows cells contains the value related to these fields.

At the beginning I had thought to develop a Java program that access to this Excel file, parse it and populate my DB. But this solution is unsustainable because I had a lot of Excel files (each file contains the mocked data for a specific table).

Can I directly use an Excel file (or the converted version in .csv) to populate a MySql table? Exist an easy way to do it?

If so what are the precautions to be taken into account?

Answer 1

Mysql Query (if you have admin rights)

SELECT * INTO OUTFILE "/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test;

Or an easy PHP script

$result = $db_con->query('SELECT * FROM `some_table`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}