Posted: Sun Mar 19, 2017 2:30 pm
by dbhguru

This is the first of a series of planned posts to help with conversions to get data into the proper format for the Superlative Native Tree Database. You can always elect to send me your data for conversion, but if you want to learn how to do it on your own, hopefully, the series of posts will help.

These posts may prove more than most of you need, but going from a source that is often text-based to numeric format for the MYSQL VA Tech database can be a real pain, especially where you already have your data in a list form that is not strictly numeric and you want to import it into an SQL database looking for numeric formats. Here is an example, it is not uncommon for people to enter heights into a column in a format such as 120.5' or 120.5 ft. The destination is a field that expects a pure number as opposed to a string of characters (text) that includes a numeric piece of information. If the information is already in Excel, we have one of two courses: (1) edit the 120.5' or 120.5 ft to just be 120.5 or convert the the 120.5' or 120.5 ft to 120.5 by some conversion method that can be applied to all cells in the column with the text-based heights. The attachment (to come) will provide a solution for Excel columns with text-based entries that start as numbers and end with a character such as ' or " that is non-numeric, or possible two characters such as Ft or In. One formula won't suffice, as you'll see in the upcoming attachment.

I'll be providing solutions for converting lat and long coordinates to all decimal formats, and other good stuff. It is pretty basic to those who write macros in Excel, but can look like gibberish to others. These solutions only make sense where you already have lots of data in a list or table format, but it is not in an acceptable form to our database. Also, I don't always think of the most compact solution for a particular problem. Please feel free to propose Excel formulas that do the job more efficiently.