How to get data Dr. Steven Bitner Announcement HW # 8 is now due - - PowerPoint PPT Presentation

• Dr. Steven Bitner

How to get data

Announcement

 HW # 8 is now due on Thanksgiving (Nov 22nd)

 Pause for booing  This assignment was supposed to be due on Tuesday, so if you

did the assignment, then you’ve got nothing to worry about

 This delay is due to some permissions and access problems that

some individuals were having with the assignment

 Please do not delay in notifying me if you cannot complete the

• assignment. I can’t help if I don’t know

KC PUG (PHP User’s Group) meeting

 This Saturday (17 November) at 2:30pm  Location: Red Nova Labs

 4830 Rainbow Blvd, Westwood, KS

 5% extra credit

 Option a: present a short (< 5 minutes) brief to present to the

class covering what you learned

 Option b: minimum 300 word summary of the meeting turned

in to me

Data is everywhere

 Except in your handy little database

Find a source

 Can be an existing DB  Find a spreadsheet with the data  Find a website with the data  Last resort, hand typing it

In an existing database?

 Use the database export functionality to create the SQL

needed to get the data on your server

 If you can’t create SQL from the database (many versions of

Access are this way), save it as a CSV (comma delimited file)

In a spreadsheet

 If it is in a spreadsheet, simply save the sheet as a CSV file

that you can use to import

 e.g. UMKC course listing

Use MS Excel

 Click on the “data” tab  Click on “from web”  Enter in the url of the site with the table

 e.g. http://www.fedjobs.com/pay/restofus.html

 Click on the arrow next to the table you want and Excel does

the rest

Excel solution

 Obviously much easier than manually copying/pasting the

data yourself

 Extremely limited

 Websites using <div> instead of <table> for layout won’t work  Many websites using table still don’t work

Importing a CSV in phpMyAdmin

 Must be CSV  Limited by max_file_upload parameter  Column headers will be added as rows, so delete them if they

exist

 If you insert into an existing table, the column count must

match up or you have to define the column matchings

Spiders

 When all else fails, you can hand-jam the data, or write a

custom spider

 Useful if you need to routinely fetch new data  Also useful if the data set is very large but consistently

formatted