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

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.

Not sure if this will be quicker to do than just doing it manually, but i think that it might be. 

Here is the data: https://kruxor.com/view/content/ImM5t/

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.

Main Table Items

Here are the titles of each of the table items we will add to our sqlite table

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.

Here is the list of value titles or data rows.  

• Skill
• Most efficient (10m/hr TVC)
• Reqs
• Cost With Boosts Gp/xp
• Xp/hr (Base)
• Video Of Method (For complicated methods)
• Notes

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.

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. 

• skill
• most_efficient
• reqs
• cost_with_boosts
• xp_hr
• video
• notes

Now that our list is cleaned, we can add these to the main data array to create the new table. 

Copy the template class extend file and rename it to something related to the data.

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. ;)

Now we can add our new values, keeping the defaults above the moo title, you can replace moo as this is a test variable. 

Create the new class extend file called "rs3_efficient_skill.extend.php"

And we are going to call the new class extend rs3_efficient_skill this enables us to call all of the core class functions under the new extend name rs3_efficient_skill to access the data. 

Add the new values like so, only replace from moo down:

Now we have our new class extend.

Here is what it looks like:

Now as we enabled the "add to section menu" flag in the class it will be added over to the right there.

Now hopefully the import CSV is still working for this, i have not used it for a while. Should work! 

Rename this exported csv to rs3_efficient_skill.csv and upload it into the import directory.

Upload this file into the /import/ directory

Run the importer and see if it has any errors. 

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. 

So if all has gone well, we should be able to list the content we added from the csv / google sheet.

Pro(?) Tip: When copying and pasting from imgur, make sure to just copy the image and not all the divs around it into your editor...

Ok now we should be able to list the data like this: 
https://kruxor.com/list/rs3_efficient_skill/ or this in a table https://kruxor.com/list-table/rs3_efficient_skill/ 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.

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.

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.

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. 

All items in the class are automatically added to the list so you can just manually add them to the html template.

We can change this to show the new fields we added so you end up with something like this:

File Name: rs3_efficient_skill-list-item.html will automatically replace the default list template for this item. 

Here is the final result. 

RS3 Most Efficient Skilling Methods