How to import an excel file in to a MySQL database

Question

Can any one explain how to import a Microsoft Excel file in to a MySQL database   For example  my Excel table looks like this   Country       Amount       Qty ---------------------------------- America       93           0 60  Greece        9377         0 80  Australia     9375         0 80

User · Accepted Answer

Export it into some text format  The easiest will probably be a tab-delimited version  but CSV can work as well  Use the load data capability  See http   dev mysql com doc refman 5 1 en load-data html Look half way down the page  as it will gives a good example for tab separated data   FIELDS TERMINATED BY   t  ENCLOSED BY    ESCAPED BY     Check your data  Sometimes quoting or escaping has problems  and you need to adjust your source  import command-- or it may just be easier to post-process via SQL

User · Answer

the best and easiest way is to use  MySQL for Excel  app that is a free app from oracle  this app added a plugin to excel to export and import data to mysql  you can download that from here

User · Answer

Not sure if you have all this setup  but for me I am using PHP and MYSQL  So I use a PHP class PHPExcel  This takes a file in nearly any format  xls  xlsx  cvs     and then lets you read and   or insert   So what I wind up doing is loading the excel in to a phpexcel object and then loop through all the rows  Based on what I want  I write a simple SQL insert command to insert the data in the excel file into my table   On the front end it is a little work  but its just a matter of tweaking some of the existing code examples  But when you have it dialed in making changes to the import is simple and fast

User · Answer

For a step by step example for importing Excel 2007 into MySQL with correct encoding  UTF-8  search for this comment    Posted by Mike Laird on October 13 2010 12 50am   in the next URL   http   dev mysql com doc refman 5 1 en load-data html

User · Answer

Another useful tool  and as a MySQL front-end replacement  is Toad for MySQL  Sadly  no longer supported by Quest  but a brilliant IDE for MySQL  with IMPORT and EXPORT wizards  catering for most file types

User · Answer

Fastest and simpliest way is to save XLS as ODS  open document spreasheet  and import it from PhpMyAdmin

User · Answer

There s a simple online tool that can do this called sqlizer io      You upload an XLSX file to it  enter a sheet name and cell range  and it will generate a CREATE TABLE statement and a bunch of INSERT statements to import all your data into a MySQL database    Disclaimer  I help run SQLizer

User · Answer

Step 1 Create Your CSV file  Step 2 log in to your mysql server       mysql -uroot -pyourpassword    Step 3 load your csv file       load data local infile    home my-sys my-excel csv  into table my tables fields terminated by     enclosed by      Country  Amount Qty

User · Answer

There are actually several ways to import an excel file in to a MySQL database with varying degrees of complexity and success    Excel2MySQL   Hands down  the easiest and fastest way to import Excel data into MySQL   It supports all verions of Excel and doesn t require Office install     LOAD DATA INFILE  This popular option is perhaps the most technical and requires some understanding of MySQL command execution   You must manually create your table before loading and use appropriately sized VARCHAR field types  Therefore  your field data types are not optimized   LOAD DATA INFILE has trouble importing large files that exceed  max allowed packet  size   Special attention is required to avoid problems importing special characters and foreign unicode characters   Here is a recent example I used to import a csv file named test csv    phpMyAdmin  Select your database first  then select the Import tab   phpMyAdmin will automatically create your table and size your VARCHAR fields  but it won t optimize the field types   phpMyAdmin has trouble importing large files that exceed  max allowed packet  size    MySQL for Excel  This is a free Excel Add-in from Oracle   This option is a bit tedious because it uses a wizard and the import is slow and buggy with large files  but this may be a good option for small files with VARCHAR data   Fields are not optimized

User · Answer

Below is another method to import spreadsheet data into a MySQL database that doesn t rely on any extra software  Let s assume you want to import your Excel table into the sales table of a MySQL database named mydatabase    Select the relevant cells    Paste into Mr  Data Converter and select the output as MySQL    Change the table name and column definitions to fit your requirements in the generated output    CREATE TABLE sales     id INT NOT NULL AUTO INCREMENT PRIMARY KEY    Country VARCHAR 255     Amount INT    Qty FLOAT    INSERT INTO sales    Country Amount Qty  VALUES     America  93 0 60       Greece  9377 0 80       Australia  9375 0 80      If you re using MySQL Workbench or already logged into mysql from the command line  then you can execute the generated SQL statements from step 3 directly  Otherwise  paste the code into a text file  e g   import sql  and execute this command from a Unix shell   mysql mydatabase  lt  import sql  Other ways to import from a SQL file can be found in this Stack Overflow answer

User · Answer

If you are using Toad for MySQL steps to import a file is as follows   create a table in MySQL with the same columns that of the file to be imported  now the table is created  goto  gt  Tools  gt  Import  gt  Import Wizard now in the import wizard dialogue box  click Next  click Add File  browse and select the file to be imported  choose the correct dilimination   quot   quot  seperated for  csv file  click Next  check if the mapping is done properly  click Next  select the  quot A single existing table quot  radio button also select the table that to be mapped from the dropdown menu of Tables  Click next and finish the process

User · Answer

When using text files to import data  I had problems with quotes and how Excel was formatting numbers  For example  my Excel configuration used the comma as decimal separator instead of the dot   Now I use Microsoft Access 2010 to open my MySql table as linked table  There I can simply copy and paste cells from Excel to Access   To do this  first install the MySql ODBC driver and create an ODBC connection  Then in access  in the  External Data  tab  open  ODBC Database  dialog and link to any table using the ODBC connection   Using MySql Workbench  you can also copy and paste your Excel data into the result grid of MySql Workbench  I gave detailed instructions in this answer

User · Answer

You could use DocChow  a very intuitive GIU for importing Excel into MySQL  and it s free on most common platforms  including Linux     More especially if you are concerned about date  datetime datatypes   DocChow easily handles datatypes  If you are working with multiple Excel spreadsheets that you want to import into one MySQL table DocChow does the dirty work

[mysql] How to import an excel file in to a MySQL database

Examples related to mysql

Examples related to excel