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.

@Sjoerd, i updated the question and added a design sketch, hope that helps. — adardesign, Jun 22 '11 at 20:09

score 8 · Answer 1 · answered Aug 02 '13 at 03:54

$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);

score 4 · Answer 2 · answered Jun 22 '11 at 19:54

4

Maybe this function will help you.

fgetcsv

(PHP 4, PHP 5)

fgetcsv — Gets line from file pointer and parse for CSV fields

http://php.net/manual/en/function.fgetcsv.php

answered Jun 22 '11 at 19:54

Mridul Kashatria

4,157
2
18
15

score 3 · Accepted Answer · answered Jun 23 '11 at 19:21

http://bytes.com/topic/mysql/answers/746696-create-mysql-table-field-headings-line-csv-file has a good example of how to do this.

The second example should put you on the right track, there isn't some automatic way to do it so your going to need to do a lil programming but it shouldn't be too hard once you implement that code as a starting point.

score 1 · Answer 4 · answered Jun 22 '11 at 20:00

Building a table is a query like any other and theoretically you could get the names of your columns from the first row of a csv file.

However, there are some practical problems:

How would you know what data type a certain column is?
How would you know what the indexes are?
How would you get data out of the table / how would you know what column represents what?

As you can´t relate your new table to anything else, you are kind of defeating the purpose of a relational database so you might as well just keep and use the csv file.

All column's will have the VARCHAR data-type, I hope the sketch i uploaded will help u understand the question better. — adardesign, Jun 22 '11 at 20:25

score 1 · Answer 5 · answered Jun 22 '11 at 20:01

1

What you are describing sounds like an ETL tool. Perhaps Google for MySQL ETL tools...You are going to have to decide what OS and style you want.

Or just write your own...

answered Jun 22 '11 at 20:01

Alex McLintock

159
4

mclintock Thanks, Please see updated question, where i added a design sketch, hope this helps. – adardesign Jun 22 '11 at 20:16

Automatically build mySql table upon CSV upload

5 Answers5