You are here
Database Completed, No More Errors.
Within the couple of weeks, I’ve been working on my database in CMOP Server. Finally got my programming to work and print information from the database I had create from the beginning. Charles has guide me through the way to complete my project. Using the data from the Yukon River – Water quality data. Extracting the .xlsx file to .cvs file then inserting them to the database. Starting on new data – Site Data
The sites spreadsheet is:
http://wwwbrr.cr.usgs.gov/
(I will forward you the email from Paul Schuster from Jun 19 if you don't have it.)
Looking through it, it looks like it is very similar to the water chemistry spreadsheet: 5 tabs, 1 for each year, with a header line (no second line for units though). It looks like it has the same issues with column order shifting between years, and may have the same issues with inconsistent column headers between years.
So
1) save out the 5 years as csv files
2) upload them to ambcs01 using the ftp program
3) create a new table in the database in the win schema for site data, with column names matching up with the spreadsheet header column names, an data types matching the column data types (text, double precision, or timestamp)
4) check through the column headers to see if they change from year to year
5) use the _populate_lookup.py script to add the spreadsheet column headers and the matching database column names to win.lookup. To do that, you'll need to
5a) modify the program to get the column names from you new sites data table
instead of from win.chemistry (at line 16 and 17)
5b) modify the program to skip over adding spreadsheet header column names if
they are already in the win.lookup table. The easiest way to do this is
probably to insert each header column name pair in a separate insert statement,
and let error handling take care of the duplicates. To do this, move line 40 and lines 49-62 inside of the for loop that starts at line 41. That way, lookup_list will only have 1 pair of values each time it gets used to create the sql insert statement.
6) You will also need to modify read_csv_alt_toad.py (create a new version) to
read the sites data spreadsheet and insert it into your new sites data table,
but let's work on updating the win.lookup table before I give you details on
that.
