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
– adardesign, Commented Jun 22, 2011 at 20:09

Khanh Le · Accepted Answer · 2013-08-02 03:54:19Z

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

Mridul Kashatria · Accepted Answer · 2011-06-22 19:54:45Z

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, 2011 at 19:54

Mridul Kashatria

4,1972 gold badges21 silver badges15 bronze badges

Comments

Geoffrey Wagner · Accepted Answer · 2011-06-23 19:21:44Z

3

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.

answered Jun 23, 2011 at 19:21

Geoffrey Wagner

8181 gold badge5 silver badges11 bronze badges

Comments

jeroen · Accepted Answer · 2011-06-22 20:00:18Z

1

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.

answered Jun 22, 2011 at 20:00

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

1 Comment

adardesign Over a year ago

All column's will have the VARCHAR data-type, I hope the sketch i uploaded will help u understand the question better.

Alex McLintock · Accepted Answer · 2011-06-22 20:01:33Z

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, 2011 at 20:01

Alex McLintock

1695 bronze badges

1 Comment

adardesign Over a year ago

mclintock Thanks, Please see updated question, where i added a design sketch, hope this helps.

Collectives™ on Stack Overflow

Automatically build mySql table upon CSV upload

5 Answers 5

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related