Tuesday, May 3, 2016

Read CSV file using VB Script

Function CSVArray(CSVFile)

  Dim comma, quote
  comma = ","
  quote = Chr(34)

  Dim charPos, charVal

  Dim cellPos, colMax, colNum
  colMax  = -1

  Dim cellArray(), cellComplete, cellQuoted, csvRecord

  Dim inCsvSys, inCsv, inRow(), rowCount
  rowCount     = -1
  Set inCsvSys = CreateObject("Scripting.FileSystemObject")
  Set inCsv    = inCsvSys.OpenTextFile(CSVFile,"1",True)
  Do While Not inCsv.AtEndOfStream
    rowCount = rowCount + 1
    Redim Preserve inRow(rowCount)
    inRow(rowCount) = inCsv.ReadLine

  For r = 0 to rowCount

    csvRecord = inRow(r)
    colNum = -1
    charPos = 0
    cellComplete = True
    Do While charPos < Len(csvRecord)

      If (cellComplete = True) Then
        colNum       = colNum + 1
        cellPos      = 0
        cellQuoted   = False
        cellComplete = False
        If colNum > colMax Then
          colMax = colNum
          Redim Preserve cellArray(rowCount,colMax)
        End If            
      End If

      charPos = charPos + 1
      cellPos = cellPos + 1
      charVal = Mid(csvRecord, charPos, 1)
      If (charVal = quote) Then
        If (cellPos = 1) Then
          cellQuoted = True
          charVal    = ""
          Select Case Mid(csvRecord, charPos+1, 1)
          Case quote
            charPos = charPos + 1
          Case comma
            charPos = charPos + 1
            cellComplete = True
          End Select
        End If
      ElseIf (charVal = comma) And (cellQuoted = False) Then
        cellComplete = True
      End If
      If (cellComplete = False) Then
        cellArray(r,colNum) = cellArray(r,colNum)&charVal

      End If


  CSVArray = cellArray

End Function

How to use the function?

Dim csv
csv = CSVArray(D:\Sample.csv)
For r = 0 to UBound(csv,1)

sText = csv(r,0) 'Read first column value
sNum = csv(r,1) 'Read second column value


Method to read EXCEL file in VB Script

Function ReadExcel( myXlsFile, mySheet, blnHeader,sqlquery )
' Function :  ReadExcel
' Version  :  3.00
' This function reads data from an Excel sheet without using MS-Office
' Arguments:
' myXlsFile   [string]   The path and file name of the Excel file
' mySheet     [string]   The name of the worksheet used (e.g. "Sheet1")
' my1stCell   [string]   The index of the first cell to be read (e.g. "A1")
' myLastCell  [string]   The index of the last cell to be read (e.g. "D100")
' blnHeader   [boolean]  True if the first row in the sheet is a header
' Returns:
' The values read from the Excel sheet are returned in a two-dimensional
' array; the first dimension holds the columns, the second dimension holds
' the rows read from the Excel sheet.
' Written by Rob van der Woude
' http://www.robvanderwoude.com
    Dim arrData( ), i, j
    Dim objExcel, objRS
    Dim strHeader, strRange

    Const adOpenForwardOnly = 0
    Const adOpenKeyset      = 1
    Const adOpenDynamic     = 2
    Const adOpenStatic      = 3

    ' Define header parameter string for Excel object
    If blnHeader Then
        strHeader = "HDR=YES;"
        strHeader = "HDR=NO;"
    End If

    ' Open the object for the Excel file
    Set objExcel = CreateObject( "ADODB.Connection" )
    ' IMEX=1 includes cell content of any format; tip by Thomas Willig.
    ' Connection string updated by Marcel NiĆ«nkemper to open Excel 2007 (.xslx) files.
    objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                  myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
                  strHeader & """"

    ' Open a recordset object for the sheet and range
    Set objRS = CreateObject( "ADODB.Recordset" )
    strRange = mySheet
    objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic

Do Until objRS.EOF
  'WScript.Echo objRS.Fields(0) & " " & objRS.Fields(1)

Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
Set myCommand.ActiveConnection = myConn
sText = Replace(objRS.Fields(0), "'", "''")
myCommand.CommandText = sqlquery &  " values ('" & sText & "','" & objRS.Fields(1) & "') "


    ' Read the data from the Excel sheet
    i = 0

    ' Close the file and release the objects
    Set objRS    = Nothing
    Set objExcel = Nothing

    ' Return the results
    ReadExcel = arrData
End Function

Update, Insert, Delete Record in SQL Server using VB Script

' DB connection string

Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = " [your sql query goes here - Insert, update, delete] "

Thursday, March 13, 2014

Get page name from URL, using Javascript

You can use following function to retrieve the current page name from the URL.

 function getCurentFileName() {
             var pagePathName = window.location.pathname;
             return pagePathName.substring(pagePathName.lastIndexOf("/") + 1);

Thursday, January 30, 2014

Redirect to .aspx webform within ASP.NET MVC application

public class PivotalController : Controller
        public RedirectResult Index()
            return Redirect("/pages/PivotalWebForm.aspx");


Tuesday, January 28, 2014

How to solve XMLHttpRequest cannot load. Origin is not allowed by Access-Control-Allow-Origin error in ASP.NET MVC 4

This error may occur when you try to communicate between different domain using AJAX calls. To solve the issue we need set up the WEB API to return correct headers.

Create a class called CrossDomainActionFilter inherited by ActionFilterAttribute.

public class CrossDomainActionFilter : ActionFilterAttribute
        public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
            bool needCrossDomain = true;

            if (needCrossDomain)
                actionExecutedContext.Response.Headers.Add("Access-Control-Allow-Origin", "*");


[AcceptVerbs("GET", "POST")]
    public object GetTest()
         rep = new ChatRepository();
        chatBoxCLS box = rep.Chatrequest(chatRequestLevel.Parent, null);

        System.Web.Mvc.JsonResult jsonResult = new System.Web.Mvc.JsonResult
            Data = box,
            JsonRequestBehavior = System.Web.Mvc.JsonRequestBehavior.AllowGet

        return jsonResult.Data;

Tuesday, January 21, 2014

How to validate dd/MM/yyyy date format in ASP.NET MVC client validations.

The problem is JQUERY validation does not consider the culture when performing the validation. So that, it will show you "Invalid date format" error message whenever you try to submit a form with a date field, which has the format of dd/MM/yyyy.

To fix the issue we can override the default validation behavior by including JQUERY Globalization plugin.

Right click on the application on in Visual Studio and select Manage NuGet Packages.

Then type globalize on search text box and install the package, which appears on the search results.

Finally, add the below script before the body closing tag on your page.

    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.js"></script>
    <script src="~/Scripts/globalize/globalize.js"></script>
    <script src="~/Scripts/globalize/cultures/globalize.culture.en-GB.js"></script>

        $(document).ready(function () {
            $.culture = Globalize.culture("en-GB");
            $.validator.methods.date = function (value, element) {
                //This is not ideal but Chrome passes dates through in ISO1901 format regardless of locale
                //and despite displaying in the specified format.

                return this.optional(element)
                    || Globalize.parseDate(value, "dd/MM/yyyy", "en-GB")
                    || Globalize.parseDate(value, "yyyy-mm-dd");

Sunday, January 19, 2014

Allow only decimal or integer numbers in a textbox using JQUERY


<input type="text" name="numeric" class='allownumericwithdecimal'>
<div>Numeric values only allowed  (With Decimal Point) </div>  
    <br/>   <br/>   <br/>
<input type="text" name="numeric" class='allownumericwithoutdecimal'>
<div>Numeric values only allowed  (Without Decimal Point) </div>

Below code will allow user to enter only decimal numbers:

 $(".allownumericwithdecimal").on("keypress keyup blur",function (event) {
            //this.value = this.value.replace(/[^0-9\.]/g,'');
            if ((event.which != 46 || $(this).val().indexOf('.') != -1) && (event.which < 48 || event.which > 57)) {

Below code will allow user to enter only integer numbers:

$(".allownumericwithoutdecimal").on("keypress keyup blur",function (event) {  
           $(this).val($(this).val().replace(/[^\d].+/, ""));
            if ((event.which < 48 || event.which > 57)) {

Tuesday, December 31, 2013

How to enable Client Side Validation for Kendo UI

Recently I was struggling with using Jquery validation in ASP.NET MVC 4 application which used Kendo UI. Except the kendo DropDownLists in a particular view, every other control fired client validations. So it had to be a problem with Kendo libraries. Finally, I added below code snippet and it started working.

Kendo DropDown:

@(Html.Kendo().DropDownListFor(m => m.SiteID)
                            .OptionLabel("Select Below...")
                            .DataSource(ds =>
                                ds.Read("ToolbarTemplate_Categories", "EnterNewDocumentVendor");

Jquery Code:


    $(document).ready(function () {

            ignore: ""


Following link would be useful for newer versions of Jquery.


Tuesday, November 19, 2013

Telerik Reports for ASP.NET MVC 4 Caching issue.

I recently used Telerik reports, and i got into a problem where report viewer cached the previous versions other than refreshing the report.

I followed the sample in below link from telerik in order to generate my report.


below is the code from my report viewer.

        .ReportSource(new TypeReportSource() { TypeName = "MvcReportApplication.MyReport.Report5, MvcReportApplication" })

To disable the report viewer caching just change PersistSession(true) to PersistSession(false) and you are good to go.