UMGC Data 620 Assignment 4.3 Your name:Date:   DeliverablesPossiblePointsTURN

UMGC Data 620 Assignment 4.3 Your name:

Date:

Deliverables

Possible

Points

TURN IN #1 – SQL script

5

TURN IN #2 – csv file

5

TOTAL

10

This assignment covers how to import data from Excel to MySQL, and how to export data from MySQL to Excel.

If you have a Mac, please see the last page (Appendix) for some helpful notes.

Reading:

?        MySQL Reference Manual, Section 13.2.9.1 – how to use the SELECT INTO command

This document contains an EXAMPLE of each type of operation; you do not need to turn in anything related to the examples. The basic logic path of this assignment is:

1.       Get your SQL up and running, and connected to the Cloud.

2.       Prepare your Excel file into a .csv for import and get it in the right spot.

3.       Import: Using Table Import Wizard

4.       Export: One option presented; this sends your output file to a designated folder on your local machine.

After the examples, there is an assignment. Your deliverable here is marked with TURN IN and consists of:

?        An SQL script

?        An output .csv file

I have chosen to err on the side of lavish documentation here, so if you’re feeling Spartan instead, you are welcome to skip straight to the assignment on the last few pages. But if you’d like an extravagant collection of screenshots with commentary, turn straight to page 2.

EXAMPLE of an IMPORT:

Raw data stored in xls/xlsx files can be imported into MySQL databases easily. The following tutorial will demonstrate the data import process. This tutorial demonstrates a simple 50 row Excel sheet import process:

Please be aware of the following facts before you attempt the import process:

?       You can only export one worksheet at a time to the MySQL database that you have created. Exporting is fast and easy provided they are in compatible formats and within a reasonable file size.

?       The Excel worksheet should be clean (free of fancy formatting such as images, colors, wrap text and fancy fonts) before exporting. (The example we are doing here is well?behaved, but if you are trying to import other data from the wild, you may need to tame it a bit before doing the import.)

Get your Excel file prepared for upload to MySQL:

1.       Make yourself a “Week 4” folder. Pay attention to whether you put a space between the “Week” and the “4” – I suggest you put a space there because I did, and then your filename/paths will match these directions. (If the script calls for “Week 4”, and you have “Week4” or “Week_4”, the computer won’t find it.)