microsoft word - assignment 4.3 for students_2185€¦ · web viewassignment 4.3 for students...

Data 620 Assignment 4.3 Your

name:

Date:

Deliverables PossiblePoints

TURN IN #1 – SQL script 5TURN IN #2 – csv file 5TOTAL 10

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

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 Wizard4. 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.

document.docx

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.)

document.docx

Make Week 4 folder

2. Navigate to the Week 4 assignment, and download our Excel document (titled “Assignment 4.3 ‐AGE_01.demo.xlsx”) into your Week 4 folder. This document contains information downloaded from https://www.census.gov/support/USACdataDownloads.html #AGE . Verify you can see your Week 4 folder and the Excel sheet in there.

3. Use Excel to open the “Assignment 4.3 – AGE01 demo.xlsx” spreadsheet. (You can do this by first running Excel, and then doing File >Open, or by right clicking on the AGE01 file and ‐ ‐choosing Open.) If it opens in Protected View, it’s OK to Enable Editing.

4. You want to save this as a .csv. Choose File > Save As > and then choose .csv as the type. Most ‐ ‐of the time, we do better choosing the plain vanilla .csv type, not the MS DOS type or (!) the ‐Mac type.

document.docx

document.docx

Excel

on your

Yes: this csv format

No: not these csv formats

5. It will grumble a little bit about features being lost – that’s OK. Tell it you want to keep using that format anyways:

6. From your local PC (workstation), open your file in Notepad. (You can do this by right clicking ‐on your file, and choosing Open With > Notepad. You can also run Notepad by running it from ‐the Start menu in the bottom left hand corner. Make sure you open the .csv version of your file, not the .xlsx version of it.)

document.docx

Yes: right ‐click the .csv

No: not the.xlsx

7. Make sure that the column names are the ones you want. For importing to SQL, it’s often easiest if column names are relatively brief and don’t contain spaces or special symbols. Our column names here are pretty clean, so we’re good to go.

8. Let’s make our lives even easier and rename this thing to a shorter file name. I’m going to save mine as “age01.csv”.

document.docx

Column names are

clean

Renamed a shorter name, kept it as .csv

file (with .txt in bottom box)

Data Import: Use the Wizard (maybe a little more user friendly)‐

This assumes you have created a raw .csv data file and gotten it somehow saved in your Week 4 folder in the Student Workspace. Now we will show you how to import the .csv file into SQL.

1. From your local PC or workstation, run MySQL Workbench. Connect to MySQL Database using existing connections to Azure resources.

2. Create a database. You can use the CREATE and USE SQL commands, or you can use the GUI in MYSQL WORKBENCH. You’ll have to click the little round arrows to refresh your schema so you can see it. (You may have some debris from previous weeks’ work on yours, such as your previous databases. I deleted and re built my MySQL connection fresh for this set of ‐instructions, so my workspace is pretty clean just now. But it’s OK if you still have your other stuff in MySQL.)

Create database test_import1;

Use test_import1;

document.docx

3. The easiest way is to use the import wizard. Under the database test_import1, right click on the Tables. You’ll see the Table Data Import Wizard menu.

4. The Table Data Import Wizard will ask you which file you want to import. Browse over to your Week 4 folder, and tell it you want to import the Assignment 4.3 - AGE01 demo.csv file. Press Open. On the next menu, press Next.

document.docx

I am in Week 4

Here’s my file

5. Tell it you want to create a new table (it may already prefill this) and click Next

document.docx

Probably prefilled for

you

6. Then you will get to choose which columns to import (or not), and what sorts of fields you want. Here, we can go with the defaults. Press Next.

7. Click Next so it will actually run the Import Data task

document.docx

8. When it is done, it will display a finished message, and you can click Next until you get to click Finish.

document.docx

Checkmarks indicate it

ran

More signs of success!

9. You can see in the Output that it ran a bunch of MySQL behind the scenes. This should all give you the happy green check mark of an OK run.

10. Poke around a little bit in your database and make sure you can see the table contents and the records. It should be an exact match to the .csv file you imported. Two ways to check on your database are to use the GUI (right click on the table elements until you see them displayed) and to use your MySQL coding skills (something like ‘SELECT *’ will work.)

document.docx

Log showing everything it

did

document.docx

Should match .csv

Hooray for you! You did it!

document.docx

EXAMPLE of an EXPORT:

The previous example imported data into our MySQL from Excel. Now we are going to do an example of how to export data *from* MySQL into a .csv file you can read in Excel.

There are many ways to export data. In this class we will use one of these ways.

Using Azure and MySQL Workbench

1. Azure MySQL does not support a script based “Select…. INTO OUTFILE” command.

2. Therefore, you will be using MySQL CLI (Command Line Interface) to interact with the database.

3. Open MySQL WorkBench and Write the query to include comma separated values with each column identified.

For example:

select Areaname, ZIP, AGE01, AGE02, AGE03

from age01;

Then change above query to add the separated values

select '"', Areaname,'"' ',', ZIP, ',', AGE01, ',', AGE02, ',', AGE03

from age01;

When doing this method it important to keep the same order as the select statement.

Because AreaName has comma in the field then “” is needed around the values to keep it in one column after the

export.

document.docx

Separates each column with a comma

Recommend copy and paste. Do not recommend you retype this. This is a single quote, a double quote, a single quote, a space, then a single quote. The computer wants it very specifically just like that because

Areaname has commas within the field.

Here is the Workbench Example:

Save your script in MySQL in a file location.

4. First you have to locate where mysql.exe command is stored. Usually it is located under “C:\Program Files\

MySQL\MySQL Workbench 6.3 CE” if you chose the default installation.

Open a command prompt and change the directory to “C:\Program Files\MySQL\MySQL Workbench 6.3 CE”. We do

this using the “cd” (for *c*hange *d*irectory) command, as shown below:

document.docx

5. At the command prompt type;

mysql.exe -h <mysql server host name> -P 3306 -u <mysql user name> -p --batch < input\SQL Script\location\

filename.sql > output\file\location\filename.csv

where:

<mysql server host name> is the my sql server name that you used to connect to cloud database

<mysql user name> is the user name connects to the database and press enter.

<input\SQL Script\location\filename.sql > is the location of the SQL script

<output\file\location\filename.csv> is where you would like to save the output .csv file.

For example, FA Isaak Morales (who wrote this) has a user name of imorales, and wrote this snippet for his

DATA 620 Section 9041 class in the spring:

mysql.exe -h data620-9041-imorales.mysql.database.azure.com -P 3306 -u adminuser@ data620-9041-

imorales -p --batch < C:\Temp\AgeExample.sql > C:\Temp\Age.csv

6. When prompted enter the password. After few seconds you should see the Age.csv file in your output location

(Password is the same as the one you entered while setting up your mysql resources on Azure.)

7. Now let’s use Excel (or Notepad) to pull up our output file. Here it is.

document.docx

document.docx

ASSIGNMENT:

1. Start with the Zip Code Data Excel file (attached to this assignment as “Assignment 4.3 – zip code data.XXXX.xlsx”). The XXXX is just a version control number and will probably be some series of numbers. You can disregard the exact numbers.

2. Insert a record for your favorite US city (make sure it’s not a duplicate of something already on there), and assign your favorite city a tourist rating of 6.

3. Save it as a .csv file, and import it into your MySQL. Make a new database called my_zip_codes and name the table in it zip_code_data.

4. Write an SQL script which will query your zip_code_data table as follows:a. Select just the city and zip code and rating (not the state)b. For which the tourist rating is a 4 or higher (this will include all ratings of 4)c. Sort it descending by tourist rating (so the highest rating is the first record)d. Put a comma between all fields

5. Name your script ‘tourist.sql’ .6. Make sure it produces a results file with your personalized information in it. If my name is

Carrie, my script should produce a resulting file named “carrie_hotspots.csv” . And Carrie’s favorite city should be featured at the top line among the results.

7. Use the Azure Export example above to export the MySQL file into an outfile called “XXXX_hotspots.csv” where XXXX is your first name. (It’s OK if you want to use a suffix, like v01, v02, etc. when debugging and then just leave the suffix on when turning it in.)

a. Hint from FA Bhakthi Liyanage – you are going to want some CLI that looks a little bit like the following, where you have replaced the “xxxxxx-spring” with the name of your database.

b. mysql.exe -h data620-xxxxxx-spring.mysql.database.azure.com -P 3306 -u adminuser@data620-xxxxxx-spring -p < tourist.sql > c:\Documents\week 4\XXXX_hotspots.csv

c. where tourist.sql is your sql script and XXXX_hotspots.csv is your output file.

8. TURN IN #1: Attach your script (Note the requirement here is a full SQL script, with comments; see the SQL script rubric from Week 1 to see how to do this. It is OK if your script requires the user to have created, loaded, and activated the database before running the script, and simply issues the SQL commands. If this is the case, make sure your comments reflect that.)

9. TURN IN #2: Attach your .csv file.

document.docx