MySQL Cookbook, 3rd Edition

Chapter 11. Importing and Exporting Data

Introduction

Suppose that a file named somedata.csv contains 12 data columns in comma-separated values (CSV) format. From this file you want to extract only columns 2, 11, 5, and 9, and use them to create database rows in a MySQL table that contains name, birth, height, and weight columns. You must make sure that the height and weight are positive integers, and convert the birth dates from MM/DD/YY format to CCYY-MM-DD format. How can you do this?

In one sense, that problem is very specialized. But it’s not at all atypical because data transfer problems with specific requirements occur frequently when you transfer data into MySQL. Datafiles are not always nicely formatted and ready to load into MySQL with no preparation. As a result, it’s often necessary to preprocess information to put it into a format acceptable for MySQL. The reverse also is true; data exported from MySQL may need massaging to be useful for other programs.

Although some data preparation operations are so difficult that they require a great deal of hand checking and reformatting, in most cases you can do at least part of the job automatically. Virtually all such problems involve at least some elements of a common set of conversion issues. This chapter and the next discuss what these issues are, how to deal with them by taking advantage of the existing tools at your disposal, and how to write your own tools when necessary. The idea is not to cover all possible situations (an ...