Creating a Custom database from a spreadsheet.

Setting up a MySQL database isn't  that difficult.  Going from several spreadsheets, I was able to create the standard databases each in about 90 minutes.  To have keyed all of that data in for each list would have been at least a whole days work (probably longer.)  The good news is, that to create a long list is actually no more work then creating a short list.  The bad news is, that there is no exact recipe and it is all going to vary depending on the format of the spreadsheet that it is coming  from.  Also there are a number of good books on MySQL:

          The Complete Reference MySQL, Vikram Vaswani
          Any book published under O'Reilly (They are usually pricey though)
          There are also several books published under Dummies.

Commands
 Comments


Make sure MySQL is installed and running
Find an Excel Spreadsheet on the Internet with your desired data in it.
Export the data to a csv format (Comma Separated File.)
Start a MS-DOS command prompt
cd \mysql\bin
mysql -u <user> -p <user name> is your user name, you will be prompted for your password.  If your MySQL is set up with out a password, omit the -p parameter.
create database <database name>; <database name> is the name of your database, in my case I will use the name herschel.  I recommend using all lowercase letters with no special characters and no spaces.
use <database name>; This just changes to our new working database.
create table work (       
    ngc          varchar(10),
    Her          varchar(10),
    n          varchar(10),
    RAHrs    varchar(10),
    RAMin      varchar(10),
    DecDeg      VarChar(10),
    DecMin      VarChar(10),
    Type          VarChar(10),
    Con          VarChar(10),
    Mag          VarChar(10),
    w01          varchar(10),   
    w02          varchar(10),   
    w03          varchar(10),   
    w04          varchar(10),   
    w05          varchar(10),   
    w06          varchar(10),   
    w07          varchar(10));		 We need to create a table that has a field (or column) for every column in our spreadsheet that we exported to the csv.  There were 15 columns in my spreadsheet.  This may vary depending on where you got your data.

The last 7 fields I don't care about and won't be using, so I gave them dummy names.

You can look at what you have created by issuing on of the following;
Show tables:
           Lists any tables that exist in your database.  If none exist, your create statement was incorrect.
show columns from work;      
           Work is the table we have created and this shows all of the fields within our table.

Note if you are having trouble with typos and such, you can create a script file in notepad.  And then just execute it at the command prompt with:
   
\. <script file name>

As errors occur, correct it through notepad and then rerun it.  After a few trial runs, it should be working.
load data local infile '<csv file>' into table work fields terminated by ',' lines terminated by '\r\n'; After the table is created correctly we will load our csv file into it with this command.

The csv file is the file name you exported your spreadsheet from.  Note if you are importing it to a Linux server, remove  \r in  the        lines terminated by.

Some useful commands to know at this point are:
          select * from work;                   This will list the entire table.
          select * from work limit 20;      This will list the first 20 records only.
select * from work where ngc = '"NGC"';
delete from work where ngc = '"NGC"';		 The first record is just headers, I want to delete it.  Delete is a destructive command, so I am going to run a select with the same where conditions first.  If there is only one record listed, I know it will be safe to run the delete command. You may note that all alphabetic data is in double quotes, this will  have to be included in our where clause.
update work set Type = substring(Type, 2, length(Type) -2);

update work set Con = substring(Con, 2, length(Con) -2);

Select * from work limit 10;		 Looking at my data, it already looks pretty good.  The alphabetic data is surrounded by double quotes, those will need removed.  Note these are destructive commands, use with caution.  If you totally mutilate your database, you can delete all records from your table and re-import them.  To delete all records from a table issue a "delete from <table>" where <table> is work in our example, then just proceed back to the import step.

Fields Type and Con are my only alphabetic fields, so I will issue the commands on both of them.

After issuing a destructive command, issue a "select * from work limit 10;" to see if the command was successful.
Update work set ngc = Concat('0', ngc) where Length(ngc) <  4;

Update work set ngc = Concat('0', ngc) where Length(ngc) <  4;

Update work set ngc = Concat('h', ngc);		 I notice that the ngc field (The NGC Number) is of a varying length.  I don't like that, it will mess up sorting and indexing later on.  We can fix it here.                      

Note you will need to issue this command two times,  since some ngc's are of 1, 2 or 3 characters in length.


Why?  Because I want to!!!



\. \mysql\<Script File>      
show tables;
 We now need to create all of the  tables used in the database.  You will now need to download the script on my web page to create an empty database.  Once downloaded, save it to the \mysql\scripts\ directory and then issue the following command:




You should see something similar to the following:

+--------------------+
| Tables_in_herschel |
+--------------------+
| const              |
| location           |
| object             |
| objtype            |
| telescope          |
| work               |
+--------------------+
6 rows in set (0.00 sec)
Insert into object (ObjIdx, ObjName, Magnitude) select Concat('ngc ', ngc), concat('NGC ', ngc), Mag from work; We need to now start building our working database.  This commands copies some of the fields from work into our future database.
Update object, work set object.ObjDecSign = '-' where work.DecDeg < 0 and work.ngc = object.ObjIdx;

Update object, work set object.ObjDecDeg =  abs(work.DecDeg ) where work.ngc = object.ObjIdx;

Update object, work set object.ObjDecMin =  work.DecMin where work.ngc = object.ObjIdx;
 We now need to get the RA and Dec values into our database.

Several of the spreadsheets I worked with had RA and Declination as single fields.  They didn't break them apart into Degrees/Hours, Minutes and seconds.  This does create a little more work, forcing me to use the substring command.

SubString(Field, begin column, length)

So If my Declination field is as follows:  "-12 06" then to extract the data, we would do something like this:

Update object, work set object.ObjDecSign = '-' where substring(work.Dec,1,1) = '-' and work.ngc = object.ObjIdx;
Update object, work set object.ObjDecDeg =  substring(work.Dec,2,2) where work.ngc = object.ObjIdx;
Update object, work set object.ObjDecMin =  substring(work.Dec,5,2) where work.ngc = object.ObjIdx;
Update object, work set object.ObjRAHrs =  cast(work.RAHrs as signed) where work.ngc = object.ObjIdx;

Update object, work set object.ObjRAMin =  cast(work.RAMin as signed) where work.ngc = object.ObjIdx;

update object set ObjRASec = (ObjRAMin - Floor(ObjDecMin)) * 60;

update object set ObjRAMin = Floor(ObjRAMin  + 0.5);







Some of the RAMin values had values to the right of the decimal point.  This moves that over to our seconds field and then rounds our minutes field.
Update object, work set object.ObjCon = work.Con Where work.ngc = object.ObjIdx;

Update object, work set object.ObjType = work.Type Where work.ngc = object.ObjIdx;
 Puts data in our consellation and object type fields.
insert into const (ConstIdx) select ObjCon from object group by ObjCon order by ObjCon;

insert into objtype (ObjTypeIdx) select ObjType from object group by ObjType order by ObjType;
 Populates our Constellation and Object Type lookup tables.  You may want to go in after the fact and give them a proper description.
grant select, update, insert, delete on <database name>.* to astrouser@127.0.0.1;
 Gives our client program access writes to the database.

Now just start the Messier Program and go to:
    Edit ->    Set Local DB
and enter the following:
    Server:        localhost (or just leave blank)
    Database:    The new database name that you just created.
    User:            astrouser

and then click on the update button.