MySQL Cookbook, 4th Edition

Chapter 13. Importing and Exporting Data

13.0 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 YYYY-MM-DD format. How can you do this?

Data transfer problems with specific requirements occur frequently when you transfer data into MySQL. Datafiles are not always formatted for being 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 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 impossible task) but to show representative techniques and utilities. Use them as is or adapt them. (There ...