How to import Microsoft Excel data to MySQL

Introduction

I was recently approached with a question relating to import data from an Excel spreadsheet into a MySQL database.

My initial response was to use LOAD DATA INFILE, however this was failing with an error.

The Problem

On further investigation the source data was requested and reviewed. The data in the final column contained text data. This text data was however punctuated and contained many ","  As the data was being exported as CSV from Excel this created a CSV file with differing numbers of columns per row and was therefore not able to be imported with LOAD DATA INFILE.

The Solution

Export the Excel file, Tab Separated Text is the ideal option in this case given the actual data.

Copy the file to a location where MySQL can read the file (/tmp in my case)

Create a table (MyTable) in my database

Import the data, this was my SQL command

    load data infile '/tmp/MyFile.txt' into table MyTable
    fields terminated by '\t' 
    lines terminated by '\r\n'

Each field was separated by a tab (\t)

As the file originated on a PC and was moved to my test box via Samba the file was DOS format, so line termination was CR/LF (\r\n), the line termination obviously needs to reflect your actual data file.

Conclusion

MySQL is complex database system, it is essential that you spend a bit of time getting to understand the basics of how the system operates. Most issues can be resolved with a simple understanding of what goes on underneath the GUI.

About the Author

Alex Monaghan is a Certified MySQL Professional and was the 2nd UK listed person on the MySQL Listing of Certified MySQL Professionals (there are a number of UK listings now). Alex runs an IT consultancy Monaghan Consultants Ltd and Web Hosting via DIY Hosting

This article is published at Monaghan Consultants MySQL Tips page and may not be republished without permission.