Automatically build mySql table upon CSV upload

Question

Automatically build mySql table upon a CSV file upload.

I have a admin section where admin can upload CSV files with different column count and different column name.

which it should then build a mySql table in the db which will read the first line and create the columns and then import the data accordingly.

I am aware of a similar issue, but this is different because of the following specs.

• The name of the Table should be the name of the file (minus the extension [.csv])
• each csv file can be diffrent
• Should build a table with number of columns and names from the CSV file
• add the the data from the second line and on

Here is a design sketch

Maybe there are known frameworks that makes this easy. Thanks.

Answer 1

$file = 'filename.csv';
$table = 'table_name';

// get structure from csv and insert db
ini_set('auto_detect_line_endings',TRUE);
$handle = fopen($file,'r');
// first row, structure
if ( ($data = fgetcsv($handle) ) === FALSE ) {
    echo "Cannot read from csv $file";die();
}
$fields = array();
$field_count = 0;
for($i=0;$i<count($data); $i++) {
    $f = strtolower(trim($data[$i]));
    if ($f) {
        // normalize the field name, strip to 20 chars if too long
        $f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 20);
        $field_count++;
        $fields[] = $f.' VARCHAR(50)';
    }
}

$sql = "CREATE TABLE $table (" . implode(', ', $fields) . ')';
echo $sql . "<br /><br />";
// $db->query($sql);
while ( ($data = fgetcsv($handle) ) !== FALSE ) {
    $fields = array();
    for($i=0;$i<$field_count; $i++) {
        $fields[] = '\''.addslashes($data[$i]).'\'';
    }
    $sql = "Insert into $table values(" . implode(', ', $fields) . ')';
    echo $sql; 
    // $db->query($sql);
}
fclose($handle);
ini_set('auto_detect_line_endings',FALSE);