Import donorschoose.org Data to MySQL Server

Whenever I write <YOUR XXX>, you should write XXX depending on your setting without "<>". For example, when I write "CD <YOUR FOLDER>", that might mean "CD C:\Program Files\MySQL\MySQL Server 8.0\bin". But that really depends on your machine. You should double check.‌

1. Copy Data and SQL Script into a RIGHT Folder

‌

First of all, you should copy data and SQL script from iSpace. There are EIGHT files:​‌

IMPORTANT: The last file is a SQL command you will use later. It should be downloaded as load_data.sql.‌

A. Windows

‌

You should COPY those eight files into the folder where mysql.exe is stored. It is "very likely" to be C:\Program Files\MySQL\MySQL Server 8.0\bin. If so, whenever I write <YOUR FOLDER> , that actually means C:\Program Files\MySQL\MySQL Server 8.0\bin‌

B. MacOS

‌

You should COPY those eight files into SOME folder you want to run MySQL from. Unlike Windows, you can run MySQL everywhere. IF you are going to run MySQL from some folder named ABC, then copy everything into the folder ABC. From now on, whenever I write <YOUR FOLDER>, that actually means some folder you chose (in the earlier example, ABC). You ALWAYS have to run MySQL from <YOUR FOLDER>.‌

2. Run MySQL

‌

A. Windows

‌

(1) Run Windows cmd with an administrator authority as below:​

DO NOT use MySQL Workbench or MySQL Shell.‌

The command line should look like below:​‌

(2) Move to the folder where MySQL is installed by typing below:

cd <YOUR FOLDER WHERE mysql.exe IS>

‌

The folder where mysql.exe resides is "very likely" to be "C:\Program Files\MySQL\MySQL Server 8.0\bin". In that case, you should type:

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

You should type this WITH quotation marks because there are spaces within the folder name (e.g. "MySQL✓Server✓8.0").‌

(3) Run MySQL by typing

mysql -u root -p

‌

It will ask you a password. Type the password and enter. Don't worry even if your password doesn't show up on the screen. You are actually typing.‌

B. MacOS

‌

(1) Open terminal:​‌

(2) In terminal, type the following:

sudo mysql -u root -p

‌

It will ask you passwords twice. First, you have to type in your MacOS password. Type that in and push enter. Then it will ask you for your MySQL password. Type that in and push enter. Don't worry even if your password doesn't show up on the screen. You are actually typing.‌

C. All

‌

If you successfully ran MySQL the screen should look like the following:​‌

3. Create a database

‌

First, you should create a database with MySQL. This new database is a playground you will use throughout the course. You can think of a database as "a collection of tables." We will name the new database EBIS3003.‌

In MySQL prompt, type the following:

mysql> CREATE DATABASE EBIS3003;
mysql> exit;

You should run MySQL first, and then run the code above.

Never forget semicolons (;) in MySQL.‌

exit; means you are exiting from MySQL.‌

4. Create Tables in the Database

‌

In cmd (Windows) or terminal (MacOS), and in <YOUR FOLDER>, type:

mysql -u root -p EBIS3003 < load_data.sql

‌

This should run load_data.sql file in <YOUR FOLDER> to create blank tables where our data will be stored.‌

5. (Windows Only) Security Setting

‌

Find a file, C:\ProgramData\MySQL\MySQL Server 8.0\my.ini and open it with notepad program. Within the file, find the line [client]. Write local_infile=1 right below the [client]. Like,

[client]local_infile=1

‌

Then, save the file, and restart your computer.‌

6. Put Data into the Tables

‌

Now you are putting the data (seven csv files you copied from iSpace) into the blank table you just created. Go back to MySQL by typing in <YOUR FOLDER>:‌

(Windows)

mysql --local_infile -u root -p

‌

(MacOS)

sudo mysql -u root -p

‌

Then type the following in MySQL:

mysql> USE EBIS3003;

‌

This is to use our course database EBIS3003. You have to do this every time you newly open MySQL from cmd or terminal.‌

Then, do this to change the setting:

mysql> SET GLOBAL local_infile=1;

‌

Next, you will read data sets with this command:

mysql> LOAD DATA LOCAL INFILE '<FILENAME>.csv'        
       INTO TABLE <FILENAME> 
       FIELDS TERMINATED BY ',' 
       ENCLOSED BY '"' 
       LINES TERMINATED BY '\n'        
       IGNORE 1 ROWS;

In the place of <FILENAME>, you should put the real file name. There are seven file names, donations, projects, schools, teachers, donors, focus_areas, focus_subjects. Don't forget 's' at the end. Don't forget the semicolon at the end.

You SHOULD NOT push enter til the end of the command above. I broke the lines for clarity. Push enter AFTER the semicolon at the end.‌

For example,

mysql> LOAD DATA LOCAL INFILE 'projects.csv'    
       INTO TABLE projects        
       FIELDS TERMINATED BY ','        
       ENCLOSED BY '"'        
       LINES TERMINATED BY '\n'        
       IGNORE 1 ROWS;

‌

You have to do this seven times for the seven data sets (the csv files)!

If you misspecified the table names, you can delete a table by doing this (in this example, donation table.):

DROP TABLE donations IF EXISTS;

and start over the step 6.