How to Import data from Web in Excel

I was exploring something by which I could get the frequently changing data from web and create my own dashboard or use that data. I found one solution of it in Excel.

Excel provides wonderful feature to import web site’s data into Excel sheet and one can refresh that data when needed.

So here are the steps for it:

1. Open Excel, go to Data tab
2. Click on From Web

   

3. Enter the URL of the web site, from which you want to extract the data
4. Now traverse through the site and enter credentials if required
5. Select the yellow box area to Import data
6. Click on Import
7. Excel will start importing the data from web site mentioned by you
8. It will ask you for the starting cell location to copy the text data from site
9. You will find your data is here.
10. You can edit the URL or settings by selection Edit Query option in Context Menu

    

11.  Refresh the data, by clicking on the starting cell on which data is imported from web and then click on Refresh All button on the top menu.
12. Get dynamic data from web in your excel and create updated documents. no need to keep track of the updated data for your calculations in excel 🙂

Get list of files in File System using SQL Server

This article is to get list of file exists in some file location in File System. Some time back I found one problem, some images were getting uploaded from my web application. To cross check if the number of files uploaded matches with data entries, I need to have list of files uploaded in my SQL Server Database, So that I could have comparison between the numbers. We can do this by getting the list using command prompt and saving it in txt file and then importing it in SQL Server or by directly calling shell command in SQL Server.
CodeProject
1. First configure database to execute shell command:

2. Create table to insert the File Names:

3. Execute the following command to insert the file names in the table or you can execute the command directly without INSERT command to get the result in query window itself:

Here, ‘DIR‘ is the command to list down the files and folders ‘ E:\‘ is the path ‘ /A-D‘ is the attribute to filter the list.

Now you can compare myFileList table with the actual file names and count uploaded. You can have a look on various other attribute options available along with this shell command.

 

Reference for shell commands:

http://msdn.microsoft.com/en-us/library/foo18935cf4-b320-4954-b6c1-e007fcefe358.aspx

How to call Server side function with JavaScript

In this article you will find how we can call Server side static function using JavaScript and get value from it. Many times developers find such requirement of fetching some data from Server side. Here is the code, where page will not follow Page life cycle and no data will be posted on the Server. hence code will be executed directly and rendered using Client side code.
CodeProject
Here is the aspx code:

We can use this code to bind dropdown or to show data in any control, in which we can display text data. The EnablePageMethods property of the ScriptManager Control must be set to true, it indicates whether public static page methods in an ASP.NET page can be called from a client script. Put “return false;“, if you are using web control instead of html control. This is will avoid postback on calling an event.

Script: keep this in head tag:

In the above code, On_Success and On_Failure are client side functions called after executing Server side function (GetNameServer). One of the best things which I like in AJAX is PageMethod. PageMethod by which one can expose server side page’s method in java script. This brings so many opportunities we can perform lots of operations without using slow and annoying post backs.

Server side code:

“[WebMethod]” attribute to a server-side method is to make the method callable from remote Web clients. This is the “static” function that will be called directly from JavaScript and will return a string value.  It cannot interact with the instance properties and methods of Page class, but can do so with other static properties and methods of the Page class.

We have used Server side method with no input parameter; we can pass input parameters as follows:

Client side code:

Server side code:

 Script:

Above code will allow to pass input parameters and pass it to the Server side method.

 

Try this J

Get Least Sequence Count

Very commonly asked question, how to find least sequence count, one can try this with 2 for loops also. I did it with recursive program…CodeProject

using System;

public class CandidateCode{

//get Input array of integer type from Console/UI window

public static int longestSeq(int[] input1)

{

int Count = input1.Length;

int[] input1new = new int[Count];

for (int i = 0; i < Count; i++)

{

input1new[i] = input1[i];

}

return CandidateCode.longestSeq(input1new, input1new[0], 1, 1);

}

public static int longestSeq(int[] input1, int max, int item, int count)

{

if (item == input1.Length)

{

return count;

}

int length1 = longestSeq(input1, max, item + 1, count);

int length2 = 0;

if (input1[item] > max)

{

length2 = longestSeq(input1, input1[item], item + 1, count + 1);

}

return MaxValue(length1, length2);

}

 

static int MaxValue(int aVal, int a1Val)

{

if (aVal > a1Val)

return aVal;

else

return a1Val;

}

}