Project #1 AUEnggStaffDirectory from Web Scraping using Excel

It is very easy to scap data for academic purpose.

Suppose i want to careta enggstaff Directory how to do it. Let us deep dive into this.

Step 1: 

Open Chrome, look for Engg staff detailes, find out the url. Suppose for chemical engg department we found out the following url for display faculty details. Copy the URL in the addressbar.

Chemical Engg. Open this. it will appear likethis.

Step 2: Open a new blank WorkBook

In Cell A1 type Dept, B1 type URL

Enter E01 in A2, copy the URL : https://annamalaiuniversity.ac.in/E01_factmem.php?dc=E01 in  B2 cell

Enter E02 in A3. 

enter   ="https://annamalaiuniversity.ac.in/"&A2&"_factmem.php?dc="&A2""  in B2.

You will use excel  Fill Pattern feature by pulling + sign at the lower bottom of the A2 cell and B2 cell

You will get the following excel contents. Once you convert all the contents as Table 

by pressing CNTL + T

 

Step 3:

Now click Data, From Web on the toolbar. it will open a dialog box and ask for url. Paste the copies url in to the textbox as shown below:

Press ok to get the details.

Click Transform Data to get the screen below.

Please note that unwanted blank columns are deleted in the above snap shot.

Step 4:

Click Home tab and click the Advanced Editor to get the following screen:

In the shown Text area, enter 

(URL) as table=>

in the top as shown. Modify the function web.contents(URL) removing actual URL inside the quotes.

click DONE Button in editor window. Now you will get the function invoking screen similar to this:

Now click  Close& Load button.

Now select anywhere in the table displayed. Click  Add Column, Custom Column to get window similar to this:

type after = fxTablefetch([URL]).

Once you invokde like this, by pressing  OK button. you get the follwing continue button screen. 

Press Continue button to get the following dialog.

Click the Ignore Check Box and Save Button. It will bring all the tables as shown below.

Click the Expansion in the Right Top in the Custom Column First Cell.

In this window, Deselct the Use Original column name as prefix labelled Check Box. Click Ok Button to get the following web scraped data.

This way you can get the excel file. Now you can edit and modify:

Select Contact Column in the table, Click the Add Column, Column by Example to get the Column 1 Col as shown. Select the the second cell from top in the contact col, you will get 

drcktech@rediffmail.com9865356561. In which select only email and past in the first cell of Text before Delimeter Column. You can see the screen similar to the following screen: Click OK to add to the table. Similary add Mobile. Finally you will the screen like this. 

Click Home, and then Load and Close. 

Remove URL, Contact Details. Rename new coulmns are as email and Mobile.

You will get sheet similar to the below:

Yeah you have done it. Good Job! Now Engineering Staff Contact Directory is ready. Just Like that,

Enjoy Web Scrapping !!!!😊😊😊😊

You Tube Channels to watch with Great Content

You Tube Channels for Watching

Technolgy Channels 

English

•       Kimberley Brehm – Excel with Statistca

•       Program with Erik – Programming Tutorials

•       Florin Pop – Javascript projects in 10 Hours

•       Nick White – Algorithms and Data Structure – Interview Questions

•       Ben Awad – Web Development / VS code Stories

•       Clement Mihailescu – S/W engineering

•       Engineer Man – Super Technical + Deep Dives

•       Mayuko – lifestyle – rich content

•       WDS – Web Dev Simplified : Web development Tutorials – Super Quality

•       KalleHaliden: Cinematography – Visually Appealing Videos

•       Sentdex : Programming over 1000 videos

•       The Coding train : Live stream coding – highly entertaining

•       Traversy Media : All kinds of Developers to find other YT channels

•       Thenewboston- open-source community; 45000 videos – 4years

•       freeCodecCamp- Largest tutorial channel – technical courses

Tamil channels

•       Tech Boss

•       Tamil Tech

•       Money

•       P R Sundar (Large Twitter Base)

•       Money Pechu – Anand Srinivasan

•       Couple Vloggers

•       Sathish Shanmu / Sathish Deepa

•       Suhail Pami

•       Ram with Janu

•       Theneer Idaivelai

•       Nakkalites

•       Madras Samayal

•       Parithabangal

•       TTF Vasan

•       Madan Gowri

•       Irfan’s View

•       Village Cooking Channel

•       Empty Hand, Cherry Vlogs, Rakesh and Jeni, The Traditional Life , Dad’s Life, & MIC Set

As  per ChatGPT (until Sept 2021) : In the digital realm where creativity flows, YouTube channels rise and in brilliance glow.

Name	URL	Views	Rev	CPM	Likes	Dislikes	#
CreatorsUnite	youtube.com/creatorsunite	10M	$150K	$15	100K	5K	200
Nature'sWhisper	youtube.com/natureswhisper	25M	$300K	$12	250K	10K	150
ScienceSpectrum	youtube.com/sciencespectrum	50M	$500K	$10	500K	20K	300
CulinaryCanvas	youtube.com/culinarycanvas	15M	$200K	$13	150K	8K	180
TravelersTales	youtube.com/travelerstales	30M	$400K	$14	300K	12K	220
MelodyMasters	youtube.com/melodymasters	20M	$250K	$12.5	180K	7K	120
ArtisticAlchemy	youtube.com/artisticalchemy	18M	$220K	$12.2	170K	6K	160
MindfulMusings	youtube.com/mindfulmusings	12M	$180K	$15	120K	4K	100