title: Laravel 5.4 - Saving Excel data into dynamically created columns in MySQL
body: <p>I have a internal webpage that makes data from excel searchable and readable from a 3rd party excel export file. The webpage allows for the uploading of multiple excel files in which the data gets read and stored in a MySQL database.</p>
  
  <p>We want to update the application to keep a history of the uploaded data (it's data that has monthly values) so we can easily search, filter and generate graphs from the uploaded data.</p>
  
  <p>So I am using Laravel 5.4 and have <code>maatwebsite\excel</code> to import and parse the excel file.
  The Excel file always consists of the following columns (<a href="https://docs.google.com/spreadsheets/d/1SPATxNM40nc5qG_xYhwbuLgFoTW3gveeKbL-Gs9dp78/edit?usp=sharing" rel="nofollow noreferrer">Dummy File</a>)</p>
  
  <p><code>| Item group | item # | item name | Item Currency | &lt;month&gt; &lt;year&gt; |</code></p>
  
  <p>After <code>Item Currency</code> there is always 36 columns for the past 3 years of data from the current month so a column would be named like <code>dec 2017</code></p>
  
  <p>Now in Laravel I have created a Model for the item named <code>Item</code> and a model for the monthly values named <code>ItemMonthly</code></p>
  
  <p>Now I am able to read the file and create columns dynamically in the database but I feel like this is very ugly and not efficient at all:</p>
  
  <p><a href="https://gist.github.com/anonymous/7c039ec24bd5eafd6b5adc936f0f18bb" rel="nofollow noreferrer">(Gist) Code for Models and Excel Function</a></p>
  
  <h2>Biggest problem</h2>
  
  <p>Because I need to read all the monthly data and since I need them in order of month I can't really rename all the columns as far as I know. I need to be able to get all the columns to render in a Highchart graph and in a Datatable. and some items don't have the same monthly data (some only go till 2015 for example.</p>
  
  <h2>Needed advice</h2>
  
  <p>I've read a couple of solutions here some of them saying instead of creating columns in MySQL just store the monthly data as a json object in a single column.</p>
  
  <p>Some answers just completely advice on changing from MySQL to MongoDB</p>
  
  <p>I am kind of at a loss to find the best approach for this, and am sincerely wondering if MySQL is the right way to go. The solutions I have been trying so far all seem to involve really hacky ways of doing this.</p>
  
  <p>If there is more info needed please let me know. I don't want to write an immense wall of text but I also want to provide the correct amount of information.</p>
  
  <p>Many thanks!</p>