Tag Archives: mySQL

How to import EXCEL into mySQL

This is a workaround for (mostly) non-programmers, found in a forum for ‘os Commerce’ – but I thought, it might be helpful here, too. 🙂


Original article is at: http://www.aspfree.com/c/a/Database/Converting-Your-Excel-Worksheet-into-a-Working-MySQL-Database/

You can only export one worksheet at a time to your MySQL database.  Your worksheet should be free from images, colors, wrapping text, and fancy fonts.  The first row is considered your Heading row starting in A1.

Steps to import a CSV file created in Excel into a MySQL table for a MySQL database stored on a server (not XAMPP localhost):

1.     Create your Excel file using the EXACT field names as in your MySQL table.  If you are importing into a table with many field names, you may want to EXPORT the table first, open in Excel and save it as your template.  If you are adding new data, make sure you delete any data that might have exported with your field names.  If your table has a Primary Key, it should be the first field listed.  If you have it set to auto_increments, leave it blank in your Excel file.

2.     Save it as a CSV file (SUGGESTION:  you may want to save the Workbook the same name as your table in the same format.  Ex:  products_attributes).

  • Go to File -> save as “CSV (Comma delimited).” If there is a warning that says “Do you want to keep the workbook in this format?” Click “YES” and close the workbook. Click “NO” if there is a warning that asks “Do you want to save the changes made to yourworkbook.csv?”

3.     Then go to http://csv2sql.evandavey.com/. This will convert csv data into MySQL insert statements.

  • Table Name: Type the EXACT table name you are importing to.
  • CSV File: Browse to the location of the CSV file you have saved in the above steps.
  • Mode: “Insert Statements”
    • If you are updating existing data, choose Updating Statement.
  • Primary Key Field (Update Only): leave blank.
  • After all are set, click “GO”
  • Copy the created statement(s).  You can leave off the following: “– Generated by http://csv2sql.com online CSV to SQL converter”

4.     Go to phpMyAdmin >> select your database

5.     Click on the “SQL” tab at the top

6.     Paste your statement(s) in the “Run SQL query/queries…” box

7.     Click “GO”

[amazon_image id=”3836218771″ link=”true” target=”_blank” size=”medium” ]Einstieg in PHP 5.4 und MySQL 5.5: Für Programmieranfänger geeignet (Galileo Computing)[/amazon_image] •  [amazon_image id=”3815828201″ link=”true” target=”_blank” size=”medium” ]Das große Buch: MySQL[/amazon_image] • [amazon_image id=”3826675185″ link=”true” target=”_blank” size=”medium” ]MySQL 5 (bhv Einsteigerseminar)[/amazon_image] • [amazon_image id=”389721525X” link=”true” target=”_blank” size=”medium” ]MySQL kurz & gut[/amazon_image]