Posted in core
2370
12:52 am, August 18, 2020
 

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

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

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

View Statistics
This Week
88
This Month
361
This Year
284

No Items Found.

Add Comment
Type in a Nick Name here
 
Search Articles
Search Articles by entering your search text above.
Welcome

This is my test area for webdev. I keep a collection of code here, mostly for my reference. Also if i find a good link, i usually add it here and then forget about it. more...

Subscribe to weekly updates about things i have added to the site or thought interesting during the last week.

You could also follow me on twitter or not... does anyone even use twitter anymore?

If you found something useful or like my work, you can buy me a coffee here. Mmm Coffee. ☕

❤️👩‍💻🎮

🪦 2000 - 16 Oct 2022 - Boots
Random Quote
"Let us prepare our minds as if we'd come to the very end of life. Let us postpone nothing. Let us balance life's books each day ... The one who puts the finishing touches on their life each day is never short of time."
Seneca
Random CSS Property

syntax

Experimental: This is an experimental technologyCheck the Browser compatibility table carefully before using this in production.
syntax (@property) css reference