Importing excel data in mysql database

Question

Hi friends, I have a excel file with about 5000 rows to be insersted at one of my mysql databse table, can anyone give quick and dirty solution ? Thanks

Answer 1

Quick and dirty:

Put a calculated column in Excel to generate a "insert" statement. Then COPY+PASTE all sentences into MySQL command interpreter.

Your cell should have a formula like this:

=CONCATENATE("insert into your table (col1, col2) values (", A1, ", ", B1, ");")

Then copy the formula on all the rows and you'll have your script.

Other quick & dirty:

Open your Excel file with ACCESS, then use "Export to ODBC" function to put your data in MySQL. It's a little bit more complicated since you will have to setup your ODBC driver and Connection but if you plan to do this regularly, it might be a better choice.

Answer 2

Save as CSV; use the "Import text file" feature of a graphical mySQL client like HeidiSQL to import.

Answer 3

Have a look at:

• Converting Your Excel Worksheet into a Working MySQL Database
• A lot of other softwares/scripts

Answer 4

You can export the Excel file to a CSV file, then import it to the MySQL database using phpMyAdmin.

Answer 5

I have done this in the past by exporting the file as a CSV. Creating your table in MySQL, reading the CSV line by line in PHP and using explode to build your query.

$file = fopen("myfile.csv", "r");
$sql = "INSERT INTO table(field1, field2, field3...";
while(!feof($file))
  {
      $fields = explode(',', fgets($file));
      $sql .= "VALUES("
      foreach($fields as $field)
      {
         $sql .= "$field, ";
      }
      $sql = substr($sql, 0, -2);
      $sql .= "), ";
  }
  $sql = substr($sql, 0, -2);
 fclose($file);
  mysql_query($sql);

Answer 6

Unless you've some additional tools lying around, there is no quick way.

You could write an excel macro and transfer the data via ODBC.

You could export it as CSV then import it using PHPMyAdmin

You could export the CSV then write an awk script to convert the csv data into a set of insert statements

You could use (insert language of choice here)

Do you have an ODBC link? Do you have any programming languages? PHPMyAdmin? Some other MySQL tool?