title: Import data from csv into database when not all columns are guaranteed
body: <p>I am trying to build an automatic feature for a database that takes NOAA weather data and imports it into our own database tables.</p>
  
  <p>Currently we have 3 steps:</p>
  
  <pre><code>1. Import the data literally into its own table to preserve the original data
  2. Copy it's data into a table that better represents our own data in structure
  3. Then convert that table into our own data
  </code></pre>
  
  <p>The problem I am having stems from the data that NOAA gives us. It comes in the following format:</p>
  
  <pre><code>Station    Station_Name    Elevation    Latitude    Longitude    Date   MXPN    Measurement_Flag    Quality_Flag    Source_Flag    Time_Of_Observation   ...
  </code></pre>
  
  <p>Starting with MXPN (Maximum temperature for water in a pan) which for example is comprised of it's column and the 4 other columns after it, it repeats that same 5 columns for each form of weather observation. The problem though is that if a particular type of weather was not observed in any of the stations reported, that set of 5 columns will be completely omitted.</p>
  
  <p>For example if you look at Central Florida stations, you will find no SNOW (Snowfall measured in mm). However, if you look at stations in New Jersey, you will find this column as they report snowfall. This means a 1:1 mapping of columns is not possible between different reports, and the order of columns may not be guaranteed.</p>
  
  <p>Even worse, some of the weather types include wild cards in their definition, e.g. SN*# where * is a number from 0-8 representing the type of ground, and # is a number 1-7 representing the depth at which soil temperature was taken for the minimum soil temperature, and we'd like to collect these together.</p>
  
  <p>All of these are column headers, and my instinct is to build a small Java program to map these properly to our data set as we'd like it. However, my superior believes it may be possible to have the database do this on a mass import, but he does not know how to do it.</p>
  
  <p>Is there a way to do this as a mass import, or is it best for me to just write the Java program to convert the data to our format?</p>
  
  <p>Systems in use:
  MariaDB for the database.
  Centos7 for the operating system (if it really becomes an issue)
  Java is being done with JPA and Spring Boot, with hibernate where necessary.</p>