How to get spreadsheet data into a meaningful/useful format using php and sqlite

<p>I had some data recently that i thought would be very good in a web page format. So i started copy and pasting it into a html editor and then realised that this data is all in the same format as its in a sheet (duh). Why am i spending the same amount of time formatting this data over and over again. Why not just add it to a database table and list it.</p>
<p>Not sure if this will be quicker to do than just doing it manually, but i think that it might be.&nbsp;</p>
<p>Here is the data: <a href="https://kruxor.com/view/content/ImM5t/">https://kruxor.com/view/content/ImM5t/</a></p>
<p>Now we have to setup the table format for our SQLite db, im using core for this which handles all of the basic crud stuff.</p>
<h3>Main Table Items</h3>
<p>Here are the titles of each of the table items we will add to our sqlite table</p>
<table dir="ltr" border="1" cellspacing="0" cellpadding="0"><colgroup><col width="131" /><col width="185" /><col width="227" /><col width="91" /><col width="130" /><col width="220" /><col width="182" /></colgroup>
<tbody>
<tr>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Skill&quot;}">Skill</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Most efficient (10m/hr TVC)&dagger;&quot;}">Most efficient (10m/hr TVC)&dagger;</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Reqs&quot;}">Reqs</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Cost With Boosts\nGp/xp&quot;}">Cost With Boosts<br />Gp/xp</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Xp/hr (Base)&quot;}">Xp/hr (Base)</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Video Of Method\n(For complicated methods)&quot;}">Video Of Method<br />(For complicated methods)</td>
<td data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Notes&quot;}">Notes</td>
</tr>
</tbody>
</table>
<p>This is not in the best format for sql as you can not use these extended characters so we will have to map more code friendly values to each of these items.</p>
<p>Here is the list of value titles or data rows.&nbsp;&nbsp;</p>
<ul>
<li>Skill</li>
<li>Most efficient (10m/hr TVC)</li>
<li>Reqs</li>
<li>Cost With Boosts&nbsp;Gp/xp</li>
<li>Xp/hr (Base)</li>
<li>Video Of Method&nbsp;(For complicated methods)</li>
<li>Notes</li>
</ul>
<p>And here is a more data friendly version, i like to use lower case for all table names as it makes the code easier later on. rather than having to call data from a row like TiTle you can always know that its title will be lower case.</p>
<p>Also with spaces i like to replace them with as this seems to be more friendly to php and sql. rather than using dashes (-) which can cause issues sometimes.&nbsp;</p>
<ul>
<li>skill</li>
<li>mostefficient</li>
<li>reqs</li>
<li>costwithboosts</li>
<li>xphr</li>
<li>video</li>
<li>notes</li>
</ul>
<p>Now that our list is cleaned, we can add these to the main data array to create the new table.&nbsp;</p>
<p>Copy the template class extend file and rename it to something related to the data.</p>
<p>I usually use the class extend moo as a template. Not sure why i called it that but its easy to remember. I should probably rename it to template or something. ;)</p>
<p><img src="https://i.imgur.com/ES4w4Ab.png" /></p>
<p>Now we can add our new values, keeping the defaults above the moo title, you can replace moo as this is a test variable.&nbsp;</p>
<p>Create the new class extend file called "<strong>rs3efficientskill.extend.php</strong>"</p>
<p>And we are going to call the new class extend <strong>rs3efficientskill</strong> this enables us to call all of the core class functions under the new extend name <strong>rs3efficientskill</strong> to access the data.&nbsp;</p>
<p>Add the new values like so, only replace from moo down:</p>
<p><img src="https://i.imgur.com/kkMf7CS.gif" /></p>
<p>Now we have our new class extend.</p>
<p>Here is what it looks like:</p>
<p><img src="https://i.imgur.com/VRoqwyi.png" /></p>
<p>Now as we enabled the "add to section menu" flag in the class it will be added over to the right there.</p>
<p><img src="https://i.imgur.com/7pXZb53.gif" /></p>
<p>Now hopefully the import CSV is still working for this, i have not used it for a while. Should work!&nbsp;</p>
<p><video controls="controls" width="100%" height="528">
<source src="https://i.imgur.com/2nwGNsS.mp4" type="video/mp4" /></video></p>
<p>Rename this exported csv to <strong>rs3efficientskill.csv</strong> and upload it into the import directory.</p>
<p>Upload this file into the /import/ directory</p>
<p>Run the importer and see if it has any errors.&nbsp;</p>
<p><img src="https://i.imgur.com/Hz8pzOt.gif" /></p>
<p>If there is no errors on the top it means the data has imported correctly, you can then list the data using the section menu and see if it all looks ok.&nbsp;</p>
<p>So if all has gone well, we should be able to list the content we added from the csv / google sheet.</p>
<p class="alert alert-secondary"><strong>Pro(?) Tip</strong>: When copying and pasting from imgur, make sure to just copy the image and not all the divs around it into your editor...</p>
<p>Ok now we should be able to list the data like this:&nbsp;<br /><a href="https://kruxor.com/list/rs3efficientskill/">https://kruxor.com/list/rs3efficientskill/</a>&nbsp;or this in a table <a href="https://kruxor.com/list-table/rs3efficientskill/">https://kruxor.com/list-table/rs3efficientskill/</a>&nbsp;they dont look great out of the box as they are using the default template for lists. We want them to look more like this.</p>
<p>So i found another issue when looking through the data imported, if the sheet has hyper links they will be converted as text so rather than having the video link they just have the wording text, so i had to go back into the original data and add all the video hyper links. Also i removed rows that were non helpful like the one for training one of the combat skills.</p>
<p>Good idea to re-check the data that is imported you can see advanced field details by either editing the item or you can click view raw fields under the post.</p>
<p><img src="https://i.imgur.com/zG8W3eR.gif" alt="" /></p>
<p>Ok now that the data is correct we can modify the default listing template to show all the items nicely rather than the default format which just shows the basics.&nbsp;</p>
<p>All items in the class are automatically added to the list so you can just manually add them to the html template.</p>
<p><img src="https://i.imgur.com/a5SbpJa.png" alt="" /></p>
<p>We can change this to show the new fields we added so you end up with something like this:</p>
<p><strong>File Name</strong>: rs3efficientskill-list-item.html will automatically replace the default list template for this item.&nbsp;</p>
<p>Here is the final result.&nbsp;</p>
<p><a class="btn btn-primary btn-block" href="https://kruxor.com/list/rs3efficient_skill/">RS3 Most Efficient Skilling Methods</a></p>