Geek Answers Handbook

Automatically create mySql table when loading CSV

Automatically build mySql table when loading CSV file.

I have an admin section where an administrator can upload CSV files with a different number of columns and a different column name.


which then has to build the mySql table in db, which will read the first row and create columns, and then import the data accordingly.

I know a similar problem , but this is different from the following specifications.

  • The table name must be the file name (minus the extension [.csv])
  • each csv file may differ
  • It is necessary to create a table with the number of columns and names from the CSV file
  • add data from the second row and

Here is a design sketch

Perhaps there are well-known frameworks that make this easy. Thank you

+4
source share
5 answers

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 is no automatic way to do this, so you will need to do lil programming, but it should not be too complicated as soon as you implement this code as a starting point.

+3
source 
$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);
+6
source

Perhaps this feature will help you.

fgetcsv

(PHP 4, PHP 5)

fgetcsv - returns a string from a file pointer and parse CSV fields

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

+4
source

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

However, there are some practical problems:

  • How do you know which data type a particular column has?
  • How do you know what indexes are?
  • How do you get data from a table / how do you know which column represents what?

Since you cannot link your new table to anything else, you seem to defeat the purpose of the relational database so that you can simply save and use the csv file.

+1
source

What you describe sounds like an ETL tool. Perhaps Google's tools for MySQL ETL ... you will need to decide which OS and style you want.

Or just write your own ...

+1
source

More articles:


All Articles