MySQL I : Batch Operations and Data Import

1. Create a new database. From the shell prompt, in the bin directory type:
mysqladmin -u root -p create accounting2
2. We can use the MySQLDump files as scripts to recreate our tables. These .sql text files are located in the “c:\temp” directory, where we dumped to in the backup exercise. Locate “c:\temp\expenses.sql” and examine it with a text editor. Close the file when done.
3. Recreate the expenses table. Type:
mysql -u root -p -D accounting2 < c:\temp\expenses.sql
4. Recreate the expense_categories table. Type:
mysql -u root -p -D accounting2 < c:\temp\expense_categories.sql
5. Recreate the remaining tables:
mysql -u root -p -D accounting2 < c:\temp\invoices.sql
mysql -u root -p -D accounting2 < c:\temp\clients.sql
mysql -u root -p -D accounting2 < c:\temp\users.sql
6. To see the results of your batch files on UNIX you can add the ” | more” command to view output.
7. Now that you have recreated the table structure, repopulate data from external text files. Remember that to import data, the target table must exist, and the text file that you are importing must have the same name as the table.
8. Data is often imported from an external text file. Before importing, check the formatting of the text file by opening “c:\temp\invoices.txt” in a text editor. Notice that a tab delimits fields. In your text editor (Notepad for example), find and replace the tab with a comma. You can use a fancy text editor like EMACS, VI, or TextPad to automate the replacement process.
9. Notice that the data in the last field has comma’s as part of the string. MySQL would see this as separate fields upon import, and the import would fail, so we need to escape the extra commas by inserting a backslash before them. When done save the file. It should look like: (see next page)

10. 1,4,2002-04-24,1902.34,Conjugation: verbs\, nouns\, adjectives.
2,4,2004-07-20,942.00,Technical writing.
3,2,2003-07-20,54.25,Hand wringing
4,3,2002-04-24,1.00,Miscellaneous Services
5,1,2002-04-10,654.34,Work\, work\, work.
6,1,2004-01-20,98754.00,Technical writing.
7,2,2003-07-18,54.25,Pacing.
8,3,2002-04-24,3210.84,Pondering
9,4,2003-04-08,6.64,Shady dealings.
10,4,2004-11-20,2.00,Brilliance.
11,5,2001-07-03,9.96,Don’t ask.
12,2,2002-04-22,315.94,Miscellaneous Services
13,3,2002-12-24,9751.94,Reading.
14,4,2002-07-20,321.55,HTML\, PHP\, MySQL Web development.
15,6,2001-07-27,2.55,Hand wringing
16,7,2003-10-24,64.64,Miscellaneous Services

Text editors like EMACS, VI, or TextPad are great at automating this sort of formatting. Such automation is called a “Macro”. Find a text editor and GET TO KNOW IT WELL! EMACS is the most powerful of many. HTMLKit, Homesite are others that you might consider using, but are not as powerful.
11. Enter the MySQL Monitor client and type: USE accounting2;
12. Load the external data into the invoices table. Type:
LOAD DATA INFILE ‘c:/temp/invoices.txt’
INTO TABLE invoices
FIELDS TERMINATED BY ‘,’;

Note that we are using a UNIX style directory delimiter here, the forward slash (/). MySQL does not care which you use, but for the sake of portability, consider using the forward slash.
13. Check that your import worked. Type:
SELECT * FROM invoices;
14. The trickiest part of loading external data is making sure your data format matches the table format. Spend time making sure that you external data text file is formatted correctly. EMACS is invaluable.
15. See the documentation for more examples of importing data.