This is what I did to create a small MySQL database out of some population data.
Found the data in tabular form on the web at http://www.demographia.com/db-uscity1790.htm
Copied and pasted the data into an Excel spreadsheet. The tabs in the data put the numbers into the right columns (lucky me!), so I just rearranged the two-part table into a single table by cutting and pasting the bottom table to the right of the top table. Compared the city names to make sure everything was lining up properly.
Saved the spreadsheet as a comma-separated-values (CSV) file called populations.csv.
Opened the CSV file with vi to take a closer look. Noticed some quirks:
echo 'tr "\r" "\n" < $1 > $1.tmp' tr "\r" "\n" < $1 > $1.tmp echo mv $1.tmp $1 mv $1.tmp $1
Designed a simple-minded SQL table, and wrote the CREATE statement to build the table:
DROP TABLE IF EXISTS populations; CREATE TABLE populations ( city text, year int, population int );
Wrote a Python script to convert the CSV file into a sequence of SQL INSERT statements.
Combined the DROP/CREATE statements with the INSERT statements into a single file called populations.sql.
Logged into cs-research1.mathcs.carleton.edu and Used the mysql command and the login information that Mike Tie sent me to load my table into my MySQL database, like this:
mysql -u myusername -h localhost -p myusername < populations.sql
Ran the mysql command prompt and tried a quick test:
mysql -u myusername -h localhost -p myusername
and then at the prompt:
mysql> SELECT * FROM populations WHERE year=1900 ORDER BY population DESC;