Rakesh Patel: 2019

Thursday, December 5, 2019

Get Table Name with Row Count from SQL Database

Select the database you want to filter the information about the row count and run following SQL Statement

SELECT
T.NAME AS 'TABLE NAME',
P.[ROWS] AS 'ROW COUNT'
FROM SYS.TABLES T
INNER JOIN SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID

This help me to know how many rows are there in table. Hope this will help you too.

Monday, November 18, 2019

Block or Disable Cut, Copy and Paste operation Using jQuery

It is required to block the cut, copy and paste operations on some of the textbox. For example, it will be better if we do not allow users to copy paste the data entered in Email and Confirm Email field in order to make the user to type themselves.

The below jQuery code will help us to do the same using preventDefault() method

Move List Items Up and Down Using jQuery

Sometimes, we require moving/scrolling the list items in the select control up and down to sort manually. This little code snippet will help us to do that using jQuery library.

var nextOption = $("#ListBox1 > option[selected]").next("option");

if ($(nextOption).text() != "") {
      $(selectedOption).remove();

$(nextOption).after($(selectedOption));
    }
  }

function MoveUp() {
    var selectedOption = $("#ListBox1 > option[selected]");

var prevOption = $("#ListBox1 > option[selected]").prev("option");

if ($(prevOption).text() != "") {
      $(selectedOption).remove();

$(prevOption).before($(selectedOption));
    }
  }
</script>

Thursday, November 14, 2019

Convert JSON data to CSV

JSON Data

Output as CSV

Wednesday, October 23, 2019

Sorting by Key in An Array of object with Jquery

Tuesday, October 22, 2019

8 Must Know JavaScript Array Methods

Working with arrays in JavaScript used to be a pain with barely any support for complex array operations. Fast forward to today, though, and there are tons of amazing JavaScript array methods available to us. In this video I will be covering the 8 most important array methods in JavaScript.

const items =[
{ name: 'Bike', price:100},
{ name: 'TV', price:200},
{ name: 'Album', price:10},
{ name: 'Book', price:5},
{ name: 'Phone', price:500},
{ name: 'Computer', price:1000},
{ name: 'Keyboard', price:25}
]

// create another array with filtered items
const filteredItems = items.filter((item) => {
return item.price <= 100
})

console.log(filteredItems);

// get element and create another array
const itemNames = items.map((item) => {
return item.name
});

console.log(itemNames)

//find element in array
const foundItem = items.find((item) =>{
return item.name === 'Album'
})
console.log(foundItem)

//forEach
items.forEach((item) =>{
console.log(item.price)
})

// some -- return true if found
const hasInExpensiveItem = items.some((item) =>{
return item.price <= 100
})
console.log(hasInExpensiveItem)

// calculate with variable initiate as 0
const total = items.reduce((currentTotal, item){
return item.price + currentTotal
},0) // 0 is the initial value of CurrentTotal

console.log(total)

// include -- if item present in array return true.. this work fine with simple array...

const items =[1, 2, 3, 4, 5]
const includesTwo = items.includes(2)
console.log (includesTwo) // return True..

const includesTwo = items.includes(6)
console.log (includesTwo) // return false..

// distinct from array

var distinct = (value, index, self) =>{
return self.indexOf(value) === index;
}

var years = [2016, 2017, 2017, 2020, 2016, 2019]
var distinctYears = years.filter(distinct)

output : [2016, 2017, 2020, 2019]

Thanks to Web Dev Simplified and codeburst

Friday, October 18, 2019

PrintQueue.Purge Method

Code help to Purge Network/Local Print .

Dim ps = New LocalPrintServer(PrintSystemDesiredAccess.AdministrateServer)
Dim pq = New PrintQueue(ps, ps.DefaultPrintQueue.FullName,

PrintSystemDesiredAccess.AdministratePrinter)

If pq.NumberOfJobs > 0 Then
pq.Purge()
End If

Tuesday, August 20, 2019

Convert JSON to CSV Format

This is common thing now a days we have all data in JSON and when we have to sent it as a csv file we need to convert. Following code found stackoverflow.com, and credit goes to developer. I am putting into my blog for easy access and reference to my blog reader.

//logData is the JSON object having json data, rest of the code do the job.

const items = logData
const replacer = (key, value) => value === null ? '' : value
const header = Object.keys(items[0])
let csv = items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
csv.unshift(header.join(','))
csv = csv.join('\r\n')

console.log(csv)

Thursday, August 8, 2019

The Fastest Way to Your Share Location from an iPhone

There are a couple ways to share your location, but this might be the quickest! Here's how to share your location from your iPhone in a text message using predictive text. For this tip, you'll have to have both Predictive Text and Location Services enabled. And if you're not connected to Wi-Fi, you'll have to enable Apple Maps too.

How to Share Directions in Apple & Google Maps

If you're meeting up with someone in a large area like a park or can't talk on the phone to give directions, sharing your location in a text message can really help! To do this:

Open a new or previously opened text chat with the person you want to share your location with.

Type the phrase "I'm at" and press the spacebar after the word "at".
In the predictive text area of the keyboard, tap Current Location.
The other person will be sent your location via Apple Maps.
Notice that only the map will show up in the text chat. You'll need to press the blue arrow to send the text message you've typed.

Reference from site : iphonelife.com

Friday, July 26, 2019

Calculated Birth Date To Age

There are number of way to calculate age in excel, below are the example to do calculation:

	A	B	C
1	8/18/1979
2

example 1:
=INT((today() - A1)/365)

example 2: Use DATEDIF() function
Note: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3, possible in some case this does not work.

Syntex : DATEDIF(start_date,end_date,unit)

unit :
"Y" : The number of complete years in the period.
"M" : The number of complete months in the period.
"D" : The number of days in the period.
"MD": The difference between the days in start_date and end_date. The months and years of the dates are ignored.
Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.
"YM": The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD": The difference between the days of start_date and end_date. The years of the dates are ignored.

Reference Link

=DATEDIF(A1,TODAY(),"Y") & " Years, " & DATEDIF(A1,TODAY(),"YM") & " Months, " & DATEDIF(A1,TODAY(),"MD") & " Days"

example 3: Use YEARFRAC()
SYNTEX : YEARFRAC(start_date, end_date, [basis])

YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.

Basis Day count basis
0 : US (NASD) 30/360
1 : Actual/actual
2 : Actual/360
3 : Actual/365
4 : European 30/360

=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)

Friday, July 12, 2019

Make all textbox on page as readonly using JQuery

This is life saving for the coder, as many time we have to make textbox readyonly, this also work with other controls.

1)single control
$('#TextBoxID').attr('readonly', 'true');

2)all text box with type text
$('input[type="text"]').attr('readonly','true');

3)all select control
$('select').attr('disabled','true');

Friday, July 5, 2019

Get all installed programs list using command prompt

Use Windows Management Instrumentation Command line tool (WMIC) in the Command Prompt window, that will help you to get List of Programs.

Step 1:
Open Command Prompt, Win + R (type "cmd")

Step 2:
type “wmic” and press Enter at command prompt.

You will get prompt as wmic:root\cli>

Type following command that will create text file with the details.
wmic:root\cli> /output:C:\ListOfInstalledPrograms.txt product get name,version

Note: you can use any number of column at the end of command

once the command is executed, it will be back to command prompt, type "exit" to quite from wmic prompt.

Thursday, July 4, 2019

Merge multiple .csv files available in same folder using CMD command

This is a trick which can save you a lot of time when working with a dataset spread across multiple CSV files. Using a simple CMD command it is possible to combine all the CSV’s into a single .CSV file.

Command:

c:\> copy *.csv combine.csv

that's it. Hope this will save your time.

Wednesday, July 3, 2019

Iterate/Loop over a JSON structure

This is an example for looping JSON structure and reading values.

var s = [{key:1,value:"abc"},{key:2,value:"def"},{key:3,value:"xyz"},{key:1,value:"pqr"}]

for(var i in s)
console.log ("Key :" + s[i].key + " - Value :" + s[i].value)

//Output
Key :1 - Value :abc
Key :2 - Value :def
Key :3 - Value :xyz
Key :1 - Value :pqr

Tuesday, June 25, 2019

File Row Count using Batch file in a directory

Copy and Paste following code in a batch file, that will count rows in file (csv, txt, etc.,) available in the directory.

@ECHO OFF
setlocal enabledelayedexpansion
for %%f in (*.csv) do (
set /p val=<%%f
find /v /c "" %%f
)

Row count with DOS "Find Command"

Windows find command :

Using ‘Find’ command, we can search for specific text in a set of files. Find below the syntax of this command with examples. Note that windows find command is different from the Linux find command in functionality. Linux find command is used to search for files that match the given criteria. But the windows find command is useful to search files for the lines that match the given string. The windows command that matches partially with Linux find command’s functionality is dir command.

Syntax and Examples

Find the lines of a file that have the specified string
find "string" filename
Ex:

C:\>find "Windows" C:\boot.ini
---------- C:\BOOT.INI
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect

To print line numbers along with the lines
find /N "string" filename
Ex:

C:\>find /N "Windows" C:\boot.ini
---------- C:\BOOT.INI
[5]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect

To ignore case in searching for the string, we can add /I switch.
find /I /N "string" filename
In the below example, we don’t find any matching lines when we search for ‘windows’. In the next command, I have added /I and it shows the matching line ignoring the case differences.

C:\>find /N "windows" C:\boot.ini

---------- C:\BOOT.INI

C:\>find /N /I "windows" C:\boot.ini

---------- C:\BOOT.INI
[3]default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[5]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect

To find the lines that are not matching with the given string:
We can use /V switch for this case. Please find the command syntax below.

find /V "string" filename
Ex:

C:\>find /N "windows" C:\boot.ini
---------- C:\BOOT.INI

C:\>find /N /I "windows" C:\boot.ini

---------- C:\BOOT.INI
[3]default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[5]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect

Reference Site

Tuesday, June 18, 2019

How to use Filter with JSON Data

// JSON Data

var jd = JSON.parse('[{"status":"Pass","Marks":98,"subject":"English"},{"status":"Pass","Marks":99,"subject":"Math"},{"status":"Fail","Marks":32,"subject":"Science"},{"status":"Fail","Marks":30,"subject":"History"},{"status":"Pass","Marks":95,"subject":"Computer"}]')

// use filter property to get each item with the condition match as Status as PASS (we are using === to validate they type and return if condition get true.)

var fd = jd.filter(function(item){
return (item.status === "Pass");
});

result will be.

fd = [{"status":"Pass","Marks":98,"subject":"English"},{"status":"Pass","Marks":99,"subject":"Math"},{"status":"Pass","Marks":95,"subject":"Computer"}]

Tuesday, June 11, 2019

Integer value with leading zero using JavaScript

prefix_zero(10,4) // output 0010

function prefix_zero(num, size) {
var s = num+"";
while (s.length < size) s = "0" + s;
return s;
}

Tuesday, May 28, 2019

10 Excel Skills and Top 10 Sites

10 Excel Skills that can help you analyze data like a pro:

Sparkline
Power Pivots
Conditional Formatting
Text Formulas
Slicers
Flash Fill
Charts
Vlookup
IF Formulas
Quick Analysis

Top 10 Sites to learn Excel for free:

Thursday, May 16, 2019

SharePoint Designer 2010 - Set Variable [Today]-x Days

To set a DateTime field to [Today] + 5 days using a SharePoint Designer 2010 Workflow, this is what I did:

1) Create a Local Variable (ETA Date) of type Number
2) Use a Do Calculation task in the workflow and set:

value: Workflow Context:Date and Time Last Run (As Double)
operation: plus
value: 432000 (number of seconds in 5 days or 60*60*24*5)
Output to: Variable: ETA Date

3) Finally, set the value of the respective field of the current item to the ETA Date variable

In your case, use the subtract operation and the second value is 2419200 (60*60*24*28).

Note: Compare the Date in Workflow, Current Item:ReminderDate equals (ignoring time) Today

Reference Link

Friday, May 10, 2019

Handel Multiple Click on Elements JQuery

I have experience that user on the page try to click multiple time on the button, in case of page delay, this is a short way to handle such issue.

This will remove all event handlers:

$(element).off().on('click', function() {
// function body
});

To only remove registered 'click' event handlers:

$(element).off('click').on('click', function() {
// function body
});

Wednesday, May 8, 2019

Send E-mail from javascript using REST API - SharePoint

In SharePoint 2013 we can send emails using REST API where we can utilize SP.Utilities.Utility.SendEmail for doing the job.

Note: The recipient is limited to a valid SharePoint user for security reasons.

$(document).ready(function () {

SP.SOD.executeFunc('sp.js', 'SP.ClientContext', SendEmails);

});

function SendEmails() {

var from = 'info@domain.com',
        to = 'toUser@domain.com',
        body = 'Email using REST API',
        subject = 'Email FYI';

sendEmail(from, to, body, subject);
}

Note: You can add 'CC' and 'BCC' property also to send email, property 'results' having array.

Friday, April 5, 2019

Remove a specific class from all child elements, and add new class

$('#parentDiv').find("*").removeClass("old-class").addClass("new-class");

Thursday, March 28, 2019

Useful Windows Command Prompt Commands

1. Command History: You can track down your command history.
c:\> doskey /history

2. Run multiple commands:You just need to put “&&” between each command and save some time.
c:\>winword && notepad && msexcel

3. See PC driver list :You can see all the drivers installed on your computer.
c:\>driverquery

4. Send an output to clipboard: Save the output of a command.
c:\>ipconfig /all | clip

5. Create Wi-Fi hotspot right from the command prompt
Before opening the Command Prompt to execute the commands needed for this, you need to open Control Panel and find Change adapter settings in the Network and Sharing option. There, click on the connection you are using and click on Properties. Now find the sharing tab and check the option “Allow other network users to connect through this computer’s internet connection.”

-Open the Command Prompt with administrative privileges and enter the following command to enabled hotspot:
c:\> netsh wlan set hostednetwork mode=allow ssid=Youthotspotname key=yourpassword

-start the Wi-Fi hotspot
c:\>netsh wlan start hostednetwork

-To stop it, simply enter this command:
c:\>netsh wlan stop hostednetwork

Wednesday, March 20, 2019

Upload Attachment to SharePoint Site List Item

Application Form Window

CODE

----------------------

using System;

using System.Windows.Forms;

using System.IO;

using System.Security;

using spClient = Microsoft.SharePoint.Client;

namespace Upload_Attachment_SP_Online

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void btnBrowse_Click(object sender, EventArgs e)

{

var file = openFileDialog1.ShowDialog();

if (file.ToString() == "OK")

txtFilePath.Text = openFileDialog1.FileName.ToString();

var x = txtFilePath.Text.Split('\\');

txtFileName.Text = x[x.Length - 1];

}

private void btnUpload_Click(object sender, EventArgs e)

{

try

{

using (spClient.ClientContext ctx = new spClient.ClientContext("https://contoso.sharepoint.com/tc/"))

{

SecureString upwd = new System.Net.NetworkCredential("", "password1").SecurePassword;

ctx.Credentials = new spClient.SharePointOnlineCredentials("jon.smith@contoso.com", upwd);

spClient.Web myWeb = ctx.Web;

spClient.List oList = myWeb.Lists.GetByTitle("TestList");

spClient.CamlQuery camlQuery = new spClient.CamlQuery();

camlQuery.ViewXml = "<view><query><where><eq ><fieldref Name='Title'/>" +

"<value Type='Text'>Item1</Value></Eq></Where></Query></View>";

spClient.ListItemCollection collListItem = oList.GetItems(camlQuery);

ctx.Load(collListItem);

ctx.ExecuteQuery();

spClient.AttachmentCreationInformation aci = new spClient.AttachmentCreationInformation();

byte[] bytes = File.ReadAllBytes(txtFilePath.Text);

MemoryStream mStream = new MemoryStream(bytes);

aci.ContentStream = mStream;

aci.FileName = txtFileName.Text;

foreach (spClient.ListItem oListItem in collListItem)

{

spClient.Attachment attachment = oListItem.AttachmentFiles.Add(aci);

oListItem.Update();

}

ctx.ExecuteQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

Replace "Tab Character" in Excel Active Sheet

TAB - horizontal tab Decimal Value is 9

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim lRow As Long

Dim lCol As Long

'Find the last non-blank cell in column A(1)

lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the last non-blank cell in row 1

lCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lRow

For j = 1 To lCol

t = Cells(i, j).Value

If (Len(t) > 0) Then

If (Asc(Left(t, 1)) = 9) Then

str1 = Mid(t, 2, Len(t))

Cells(i, j).Value = str1

End If

End Sub

Thursday, March 14, 2019

Rest API to check field value contains...

[site url]/_api/Web/Lists/GetByTitle('myList')/items/?$filter=substringof('ABC',Title)

Filter Unique Value in Array

var items = ['abc','pqr','abc','xyz','pqr','abc'];
var result = items.filter(UniqueElement);

//result ['abc', 'pqr','xyz']

function UniqueElement(value, index, self) {
return self.indexOf(value) === index;
}

Saturday, March 9, 2019

Simple way to Check if JSON is correct or not..

function IsJsonString(str) {
try {
JSON.parse(str);
} catch (e) {
return false;
}
return true;
}

Remove control characters from a string

function removeCC(s) {

  /* Remove NewLine, Carriage Return and Tab characters from a String  */

  r = "";
  for (i=0; i < s.length; i++) {
    if (s.charAt(i) != '\n' &&
        s.charAt(i) != '\r' &&
        s.charAt(i) != '\t') {
      r += s.charAt(i);
      }
    }
  return r;
  }

Same thing with regular expression

function removeCC(s){ 
  return s.replace(/[\n\r\t]/g," "); 
}

Friday, March 1, 2019

Custom button in DataTable

DataTable Example:

(1)Add custom button

"buttons": [{"text": ' Custom Button',
"action": function () {
alert("Custom Button Clicked");
}
},

]

(2) Button Menu

"buttons":[
{
extend: 'collection',
text: 'Menu',
className: "menuMain",
autoClose: true,
buttons: [
{
text: 'Item 1',
action: function () {
alert("Item 1 Selected");
}
},
{
text: 'Item 2',
action: function () {
alert("Item 2 Selected");
}
}
]
},
]

Monday, February 25, 2019

Get index from a JSON object with value

Monday, January 21, 2019

how to use toLocaleString() and tofixed(2) in javascript

numObj.toLocaleString([locales [, options]])

toLocaleString takes 2 arguments. The first is the locale, the second are the options. As for the options, you are looking for:

minimumFractionDigits

The minimum number of fraction digits to use. Possible values are from 0 to 20; the default for plain number and percent formatting is 0; the default for currency formatting is the number of minor unit digits provided by the ISO 4217 currency code list (2 if the list doesn't provide that information).

To be able to set the options without setting the locale, you can pass undefined as first argument:

var num = 2046430;
num.toLocaleString(undefined, {minimumFractionDigits: 2}) // 2,046,430.00

However this also allows the fraction to be longer than 2 digits. So we need to look for one more option called maximumFractionDigits. (Also on that MDN page)

var num = 2046430.123;
num.toLocaleString(undefined, {
minimumFractionDigits: 2,
maximumFractionDigits: 2
}) // 2,046,430.12

Tuesday, January 1, 2019

Get Height and Width of a Cell in Excel

Private Sub Worksheet_Activate()
Dim getHeight As Single
Dim getWidth As Single

For i = 1 To 10
getHeight = Range("A" + Trim(Str(i))).Height
getWidth = Range(Chr(65 + i) + "1").Width

Me.UsedRange(i, 1) = "Row cm : " + Str(Round((getHeight / 72) * 2.54, 2))
Me.UsedRange(1, i + 1) = Chr(65 + i) + "1 :" + Str(Round((getWidth / 72) * 2.54, 2))

Next
End Sub