I have the unenviable task of migrating some Microsoft Office Access tables into MySQL. The first step, assuming you don't want to go through the pain of setting up a MySQL ODBC connection is to export these tables to Excel then use Excel to export the spreadsheets to CSV format. One might think one would be able to export to CSV directly from Access but one might think a lot of things about Microsoft products.

You'll need to create your table structure in MySQL based on the header row of the CSV and type of data in the columns. Don't create any auto-incrementing ID index or any of the extra columns you want to add yet; this will have to be done after the import since MySQL will treat the columns in your CSV on a FIFO basis. Be sure to structure your table accordingly.

Once your table has been prepared edit the CSV and delete the column header row, any blank or invalid lines and check the cells for formatting. If you are trying to import prices to a FLOAT column and they include currency symbols, spaces or delimiters you are going to have problems if you don't take this into account first. Excel wraps cells containing commas (or whatever cell delimiter you specified) in double quotes; MySQL will read these commas as delimiters anyway so if you need them included in your dataset you might need to choose a more appropriate delimiter instead (the pipe | for example). These double quotes will also negatively impact cells of a type which do not support them (INT, FLOAT) and be included in those that do (VARCHAR, TEXT) so it is advisable to run a search-and-delete prior to importing.

Once your CSV passes inspection upload it to your SQL server and after logging in to the MySQL CLI and selecting the appropriate database:

mysql> load data local infile 'myfile.csv' into table `mytable` fields terminated by ',' lines terminated by '\n';
Query OK, 2189 rows affected, 85 warnings (0.01 sec)
Records: 2189  Deleted: 0  Skipped: 0  Warnings: 84

Replace ',' with an appropriate delimiter if you are not using a true CSV, i.e. '\t' for TSV (tab separated values).

If this has produced warnings you're going to have to TRUNCATE TABLE, go back to your CSV and inspect the formatting again. Run

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1262 | Row 487 was truncated; it contained more data than there were input columns  |
| Warning | 1265 | Data truncated for column 'thing1' at row 531                                |
| Warning | 1265 | Data truncated for column 'thing2' at row 531                                |

...

to see what went wrong and identify the line number(s) requiring modification.