Friday, March 29, 2013

YouTube API with .NET
How to pull list of videos uploaded by a specific user on YouTube?

This post is next in series of our learning YouTube API with .NET. In the last post we learn about pulling single video metadata using .NET and YouTubeService. In this post we will learn to pull list of videos uploaded by a specific user on YouTube.

For demonstration purpose I have designed following interface in .NET in Visual Studio. We have a text box where we can enter YouTube user id. We have a button control “Show Video List”. On the button click event we have written .NET code logic which interacts with YouTubeServices and pulls list of videos uploaded by the specific user. We have a list view control in which we will show the video list return by the YouTubeService feed.

We have added Google Data API DLLs reference in the application. The code snippet on this form is following.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.YouTube;
using Google.YouTube;
using Google.GData.Client;
using Google.GData.Extensions.MediaRss;

namespace YouTubeAPI
{
    public partial class Form1 : Form
    {
        string myDeveloperKey;
            
        public Form1()
        {
            myDeveloperKey = "my Long string dev key";
        
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            listView1.Columns.Add("Video Title");
            listView1.Columns.Add("Uploaded By");
            listView1.Columns.Add("Uploaded Date");
            
            listView1.FullRowSelect = true;

            string youTubeUserID;

            youTubeUserID = textBox1.Text;
            
            YouTubeService myservice = new YouTubeService("aa",myDeveloperKey);

            Uri videoEntryUrl = new Uri("http://gdata.youtube.com/feeds/api/users/" + youTubeUserID + "/uploads");
        
        
            FeedQuery fq=new FeedQuery();
            fq.Uri=videoEntryUrl;
            
            Feed<Video> videoFeed = new Feed<Video>(myservice,fq);
                        foreach (Video entry in videoFeed.Entries)
            {
                listView1.Items.Add((new ListViewItem(new string[] { entry.Title.ToString(), entry.Author.ToString(), entry.Updated.ToLongDateString() })));
            }
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }
    }
}

After setting up the design and code in the visual studio, I connected with the internet and navigate to YouTube website. From the home page I copied a user id as I want to test the program to return the list of videos uploaded by the specific user.

I pasted the user id in the text box and clicked on the “Show Video List” button. The code logic written on the button control works as expected and it returns me the list of 25 videos uploaded by the specific user.

YouTube API with .NET
How to read Single Video statistics from YouTube?

This post is in continuation to the YouTube API with .NET series of articles. In the last post we learn about pulling region or country specific video feed. In this post we will learn to read single video metadata from YouTube.

Each of the video on YouTube has their own statistics such as Video Title, Video descriptions, uploaded by, number of views, comments etc. We will try to fetch these statistics from YouTubeServices.

For demonstration purpose, we have designed following interface in .NET. We have a textbox where you can write or paste the video URL as shown in the web browser. The “Show Statistics” button has code logic to connect with YouTubeServices and request for video metadata that has been entered in the text box. The list view control will show the metadata that YouTubeService has returned in the response.

In the form code we have added Google Data API references and used our Developer Key that we created in the Google API Console. The entire code snippet is following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.YouTube;
using Google.YouTube;
using Google.GData.Client;
using Google.GData.Extensions.MediaRss;

namespace YouTubeAPI
{
    public partial class Form1 : Form
    {
        string myDeveloperKey;
            
        public Form1()
        {
            myDeveloperKey = "my Long string Developer key";
        
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            listView1.Columns.Add("Video Title");
            listView1.Columns.Add("Uploaded By");
            listView1.Columns.Add("Video Desc.");
            
            listView1.FullRowSelect = true;

            string VideoWebLink;
            int startPos;
            int endPos;

            VideoWebLink = textBox1.Text;
            startPos = VideoWebLink.LastIndexOf("=");
            endPos = VideoWebLink.Length - startPos;
            
            VideoWebLink = VideoWebLink.Substring(startPos+1, endPos-1);
            
            YouTubeService myservice = new YouTubeService("aa",myDeveloperKey);

            Uri videoEntryUrl = new Uri("http://gdata.youtube.com/feeds/api/videos/" + VideoWebLink);
        
        
            FeedQuery fq=new FeedQuery();
            fq.Uri=videoEntryUrl;
            
            Feed<Video> videoFeed = new Feed<Video>(myservice,fq);
                        foreach (Video entry in videoFeed.Entries)
            {
                listView1.Items.Add((new ListViewItem(new string[] { entry.Title.ToString(), entry.ViewCount.ToString(), entry.Description.ToString() })));
            }
        }

    }
}

After design and code setup, I connected with the internet and clicked on one of the video. I copied the URL as shown in the web browser and pasted it into form text box. Next, I clicked on the button “Show Statistics” and application code logic connected with the YouTubeService and pulled the vide statistics such as Video Title, Video Descriptions, Number of views etc.

YouTube API with .NET
How to pull region/country specific videos list from YouTube?

This article is next in series of our learning YouTube API with .NET. In the last post we learn about how to read list of top rated videos on YouTube. In this post we will learn to pull region or county specific video list from YouTube using .NET.

To start with we have designed following interface in .NET. We have a list box and a button control on the form. The list box is to show the list of videos pulled from YouTubeService. On the button control we have written .NET code logic to interact with YouTubeServices and pull country or region specific feeds.

In this example we will pull the list of most polular videos from Canada. For each of the country Google has provided a list of Region id that you can use as part of your YouTube service request. For a complete list of Region ID you can visit navigate to Google Developer reference guide.

We have added Google Data API reference in the project. We have used following code snippet on the form.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.YouTube;
using Google.YouTube;
using Google.GData.Client;
using Google.GData.Extensions.MediaRss;

namespace YouTubeAPI
{
    public partial class Form1 : Form
    {
        string myDeveloperKey;
            
        public Form1()
        {
            myDeveloperKey = "my Long string developer key XXXXX";
        
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            listView1.Columns.Add("Video Title");
            listView1.Columns.Add("Uploaded By");
            listView1.FullRowSelect = true;
            
            YouTubeService myservice = new YouTubeService("aa",myDeveloperKey);

            Uri videoEntryUrl = new Uri("http://gdata.youtube.com/feeds/api/standardfeeds/CA/most_popular?v=2"); 
            FeedQuery fq=new FeedQuery();
            fq.Uri=videoEntryUrl;
            
            Feed<Video> videoFeed = new Feed<Video>(myservice,fq);

            foreach (Video entry in videoFeed.Entries)
            {
                listView1.Items.Add((new ListViewItem(new string[] { entry.Title.ToString(), entry.Uploader.ToString()})));
            }
        }
    }
}

Once we are done with the design and writing code on the .NET form, I connected to internet and clicked on button “Pull Videon from You Tube”. The result was as expected, the application connected with YouTubeServices and pulled the most popular video list specific to country Canada.

Thursday, March 28, 2013

YouTube API with .NET
How to pull top rated videos list from YouTube?

This post is second in series of our learning YouTube API with .NET. In the first post we learn some basic facts about YouTube API and different kind of feeds available from YouTubeServices. In this post we will learn how we can retrieve list of top rated videos from YouTube using .NET

As discussed in the first post, there are Public feeds and Private feeds available on YouTube for developers to read from. We will read a public feed in this post.

For demonstration purpose, I have designed following interface in .NET. I have a list box and a button control on the form. The idea is to pull the list of top rated videos and who had uploaded that video from YouTube and show it in the list box. The button "Pull Video from YouTube" has code logic to connect with YouTubeServices and request the top rated video list and show it in the list box.

In the last post we downloaded the Google Data API. We will be adding the references of Google.GData and Google.GData.YouTube dlls in this example. We also need to provide our developer key as part of our request so that YouTubeServices can identify the requestor.

The code snippet on the form is following: For demonstration purpose, I have kept the code to simple and not added error handling and coding best practices

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Google.GData.YouTube;
using Google.YouTube;
using Google.GData.Client;
using Google.GData.Extensions.MediaRss;

namespace YouTubeAPI
{
    public partial class Form1 : Form
    {
        string myDeveloperKey;
            
        public Form1()
        {
            myDeveloperKey = "my Long string developer key XXXXXX";
        
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            listView1.Columns.Add("Video Title");
            listView1.Columns.Add("Uploaded By");
            listView1.FullRowSelect = true;
            
            YouTubeService myservice = new YouTubeService("aa",myDeveloperKey);

            Uri videoEntryUrl = new Uri("https://gdata.youtube.com/feeds/api/standardfeeds/top_rated"); 
            FeedQuery fq=new FeedQuery();
            fq.Uri=videoEntryUrl;
            
            Feed<Video> videoFeed = new Feed<Video>(myservice,fq);

            foreach (Video entry in videoFeed.Entries)
            {
                listView1.Items.Add((new ListViewItem(new string[] { entry.Title.ToString(), entry.Uploader.ToString()})));
            }
        }
    }
}

Once I am done with design and coding I connected to the internet and clicked on the button. The result was as expected, the application code logic interacted with the YouTubeServices and pulled the top rated video list.

Please keep this in mind that we are reading a feed which is periodically updated. So assuming that it will match the current list of top rated videos on YouTube is a wrong assumption.

So we are done with our first example, we will have some more examples on this topic soon.

YouTube API with .NET
How to get started with YouTube API?

One of the most successful and popular service offering from Google is YouTube. YouTube provides a great platform to watch and upload videos. It also provide live stream of events currently happing across the world. As a developer, we love to integrate YouTube videos, statistics and feed to our website or applications. In this series of YouTube articles we will learn how we can read different type of feeds or statistics provided by YouTube and how we can integrate them in our applications.

YouTube provides YouTubeServices which offer different kind of feeds to the developer community. A Feed is nothing but a data format used for providing information and it gets updated frequently. YouTubeServices provides many type of feeds such as Video feeds, user playlist feeds, Video comments feed, user subscription feeds, user contacts feeds etc. All the feeds can be classified mainly into two types of feeds.

       
  1.   Public Feed
  2.   Private Feed

A Public feed is one that is available in the public domain. For example, a list of most popular videos or top rated videos is a public feed. Any video that is available on YouTube and its metadata like number of views, comments on a specific video are example of public feeds. Any application can request for a public feed from YouTubeServices and use the received feed to show it to application users.

A Private feed is one that is related to a specific user and only that specific user can access it. For example a YouTube user can tag some of videos as his or her favorite and mark it as Private. So the private marked favorites are not accessible in public domain. Any user can authorized a request to read his or her private feeds.

YouTube feeds are updated periodically which range from every 30 to 40 minutes to daily or hourly. Some of the feeds are updated on weekly and monthly basis.

How to read YouTubeServices Feeds?

To read YouTubeServices feeds using .NET a developer need to follow below steps:

  1.   Google UserID/Password: To read a YouTubeServices feed a developer need to have a Google user id and password.
       
  2.   Developer Key: A developer has to initiate a request to YouTubeServices. All requests that go to YouTubeServices should be authenticated. To authenticate a request a developer need a developer key which identify the requestor to YouTubeServices. To get a developer key you can navigate to Google API Console. You need to create a new project and click on API Access. You will find the client id and developer key on the screen.
       
  3.   Google Data API: A developer need to download and install Google Data API which provides a set of libraries to interact with different Google services such as SpreadSheet, Picasa, Analytics, YouTube etc. Google Data API is available in different flavors such as Java, .NET, Python etc. To download .NET client library of Google Data API you can navigate here.
       
  4.   Ms-Visual Studio: To read YouTube feeds using .NET a developer need Microsoft Visual studio. You can use Visual Studio 2005/2008/2010 or whatever is available to you.

Once you complete all the above steps you are ready to read YouTube feeds. In the next article we will learn how we can read a public feed like most popular feed.

Reference: Developers Guide: YouTube

Saturday, March 16, 2013

SSIS: How to validate Flat File Row Length?

The use of flat file as a source or destination is very common in SSIS. MSBI has provided Flat File Source and Flat File destination components to deal with Flat files. In case you are dealing with a fixed length flat file and want to ensure you deal with the data that has specific length of character in every row you can apply different solutions into SSIS. This article will focus on a simple way to check the flat file has valid length of character in every row.

For demonstration purpose let us assume we have following flat file. Each of the rows has a fixed length that is 30 character lengths. We want to check all rows have similar character of length i.e. 30. If there are rows where character length is not 30, they are invalid rows. We want to separate the valid rows and invalid rows.

We created a Flat File connection manager. The Format we selected is Ragged Right. We want to read out the length of character in each row in the flat file.

In SSIS Designer we create following SSIS package. We have a flat file source to read the data from. Next we have a Derived column component where we are adding one column that will tell the length of each row. Next we are using Conditional Spilt to separate valid and invalid rows. We have two Row Sampling components to move valid and invalid rows. We have added data viewers to see what rows have moved in each row sampling component.

In Derived column we have added one column Row_Length and applied LEN function.

In the conditional split we have added two outputs with two separate conditions on Row_length column. So all the rows where length is 30 will move to Output 1 (valid) and all rows where row length is not 30 will move to Output 2 (invalid).

We executed the SSIS package and since all rows had similar length all the rows were move to valid output.

Next we change the flat file data and tempered two rows. In one row we have more than 30 character of length and in one row we have less than 30 character of length.

We executed the SSIS package again and this time we have 12 rows moved into valid output and 2 rows moved into invalid output.

So our purpose to validate the flat file length is achieved and solution work as per design.

There could be a different solution like Script component that we can use to solve this but nevertheless the point I wanted to show is to how we can achieve this using Conditional Split and without writing any code.

Thanks for reading this post.

Monday, March 11, 2013

What is Gamification?

I have heard the word Gamificaiton a lot in recent time. This buzzword has been in the air since quite some time and I had this in my mind to explore what is this and what it is all about?

I explore this and writing it here from a very basic point of view.

Before you think about Gamification, read the word carefully – GAMIFICATION. It starts with GAME. All of us have played some sort of game at some point of time. So what are the common things you have noticed while playing games.

The purpose of Games is to engage the users more and more and more. You must have heard about teenagers or might be knowing someone who is crazy about video games or computer games or mobile games. Why? What is in these games that people are so addict about?

The answer is these games encourage players to engage more and more to it. All the games have some kind of point system, rankings, different levels, scores, dashboard which shows the achievement of the user playing the game and at the same time they shows the leader or highest scorer or top ranker of the game. The point to show the winner or highest scorer is to challenge the player to beat the highest scorer or achieve the highest level or get to the highest ranking. So there is competition – a natural instinct of human side which is being used to engage player. Once a player achieve the highest level there is a sense of accomplishment; again a natural instinct of human side.

So in a way games are very much using the natural human instincts. Gamification is very much like games. It is using the game-like thinking and mechanism to engage users more and more. Just like you solve a puzzle or complete a level in the game, in gamification you do learn to solve the problem, learn interactively and when you achieve a task you move to next level and task

On a very high level gamification can be applied to any process or task. What is needed is a plan and presentation. If you are an organization and want to apply gamificaiton to a program or process or to a department, you have to put in place collection of all the task or process that is required in the program. Then you add some points or rewards to successful completion of tasks. You have to add different levels so that once a person achieved the required points; he can be moved to next level. All in all what you are doing is increasing interactivity with the user of the program. that you want people to

Today Gamification is used in many areas such as Sales and Marketing, Art, education, customer retention and Customer loyalty, mobile and many more. There are many examples that are happily shared in the internet where gamification has lead to the success of program or process.

Language Quality Game – An example how Microsoft applied the Gamification for the success of Windows 7.

Dropbox and Linkedln has applied gamification techniques to their success stories.

Friday, March 1, 2013

SSIS: How to read data from Excel nth Row without OpenRowset()?

A lot of organization and individuals store data into Ms-Excel. This is because Ms-Excel is popular and easy to use data storage tool and it provides a lot of functions and formulas to play with the data. Reading data from Ms-Excel using SSIS is something SSIS developers come across many times. In this post, we will learn to read and export data from Ms-Excel using SSIS, the specific thing is we will read data that starts from nth row and goes till nth row into Excel.

When it comes to read data from a specific cell or specific row we often go ahead with OpenRowset attribute that comes with Excel Source into SSIS. In this post we will examine an alternative way to achieve this.

Consider this scenario we have following data into Ms-Excel and we want to read data that starts from 2nd row and goes till 5th row. What it means is we want to read data of productname column that has values Desktop, Coffee, Tea and Mobile only.




Solution approach:

The approach we will take is to dump all the data of Ms-Excel into a Staging table (temporary table).
Add an identity column in the staging table to store the row numbers with each row.
Read data that starts from 2nd row (identity column value is >=2) and ends till 5th row (identity column value is <=5).

I have created two SQL table to store the data.

Staging Table:

CREATE TABLE [dbo].[tmp_tblProducts](
    [Sno] [int] IDENTITY(1,1) NOT NULL,
    [productName] [varchar](100) NULL,
    [quantity] [int] NULL
) ON [PRIMARY]

Product Table:

CREATE TABLE [dbo].[tblProducts](
    [productName] [varchar](100) NULL,
    [quantity] [int] NULL
) ON [PRIMARY]

In SSIS, I have added one Execute SQL task and two Data Flow task.



Execute SQL Task truncates all data from Staging table. Basically it cleans all the data from Staging table and due to Truncate statement it reset the identity seed value to (1, 1).



On first Data Flow task, I have added following components.



Excel Source component read the entire data from Sheet1.



The Data Conversion component converts the Excel Colum data into varchar and eight byte sign int. This is to avoid Unicode truncation issue.



On OLE DB Destination task, we are dumping all data into Staging table.



On second Data Flow task, I have added following two components – OLE DB Source and OLE DB Destination.



On second data flow task I have create two variables startRow and endRow. I have assigned the value 2 and 5 as the requirement is to read data that starts from 2nd row and goes till 5th row.



On OLE DB Source, I have uses SQL Command as Data access mode. The SQL command we are using is following:

SELECT TOP 1000 [Sno] ,[productName] ,[quantity] FROM [SinghVikash].[dbo].[tmp_tblProducts] WHERE Sno>=? and Sno<=?

I have added two place holders for which we need to supply two parameters values.



I have assigned user defined variables as parameter values.



On OLEDB Destination component, I have selected the product table as destination table.



After setting up the SSIS package, I run the package and it executed successfully.



I checked the data into SQL Table and as expected, the data of 2nd row till 5th row was inserted in the table.



Related Articles:

SSIS: How to read nth Row from Flat File?
SSIS: Read and Export Excel data from nth Row

Popular Posts

Real Time Web Analytics