C#: Read from a spreadsheet/Excel

In my previous post i have mentioned about reading from CSV. In this i'm going to share the method i use to read from excel.

Reference:

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

Method:

        public void ReadExcel(string excelPath)
        {
        private static Microsoft.Office.Interop.Excel.Application appExcel;
        Microsoft.Office.Interop.Excel.Workbook theWorkbook;
        Microsoft.Office.Interop.Excel.Sheets objSheets;
        Microsoft.Office.Interop.Excel.Worksheet objWorkSheet;
        Microsoft.Office.Interop.Excel.Range range;
        string[] strArray;

            try
            {
                appExcel = new Microsoft.Office.Interop.Excel.Application();
                if (appExcel != null)
                {
                    theWorkbook = appExcel.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    objSheets = theWorkbook.Worksheets;
                    objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(1);
                    for (int i = 2; i <= 11; i++)
                    {
                        range = objWorkSheet.get_Range("A" + i.ToString(), "I" + i.ToString());
                        System.Array myValues = (System.Array)range.Cells.get_Value(Type.Missing);
                        strArray = myValues.OfType<object>().Select(o => o.ToString()).ToArray();

                        StartSearching(strArray);
                    }
                }
                else
                {
                    //clean up stuffs
                    theWorkbook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(theWorkbook);

                    appExcel.Quit();
                    Marshal.FinalReleaseComObject(appExcel);
                }
            }
            catch (Exception ex)
            {
                _log.ErrorFormat("There was an error reported Read Excel! {0} \n\n", ex.Message, ex.StackTrace);
            }
            _log.DebugFormat("ReadExcel Completed");
        }

C#: Read from CSV

I'm writing this post thinking how easy it is to read and write to a file for a developer, however if you happen to write a piece of code to read from CSV i bet you take little longer. This is what happened to me. So i thought it's a good move to add this to my blog so i can refer this method anytime. 

If you think this helps you, make use of it whenever you want it.

Reference:

using System.IO;

Method to read from CSV: 

 public string[,] readCSV(string filePath)

        {

            try

            {

                string fileData = System.IO.File.ReadAllText(filePath);

                // Split into lines.

                fileData = fileData.Replace('\n', '\r');

                string[] lines = fileData.Split(new Char[] { '\r' }, StringSplitOptions.RemoveEmptyEntries);

                // See how many rows and columns there are.

                int totalRows = lines.Length;

                int totalCols = lines[0].Split(',').Length;

                // Allocate the data array.

                string[,] resultVals = new string[totalRows, totalCols];

                //populate the array with data

                for (int row = 0; row < totalRows; row++)

                {

                    string[] line_r = lines[row].Split(',');

                    for (int col = 0; col < totalCols; col++)

                    {

                        resultVals[row, col] = line_r[col];

                    }

                }

                return resultVals;

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

                return null;

            }

        }

Method that use the string array:

 private void StartSearching(string[,] strArray)

        {

            try

            {

                for (int i = 1; i < strArray.GetLength(0); i++)

                {

 

                        Narnum = strArray[i,0].ToString().TrimEnd().ToLower();

                        EmpName = strArray[i,1].ToString().Trim();

                        PreferredName = strArray[i,2].ToString().TrimEnd();

                        Firstname = strArray[i,3].ToString().TrimEnd();

                        Surname = strArray[i,4].ToString().TrimEnd();

                        Position = strArray[i,5].ToString().TrimEnd();

                        Department = strArray[i,6].ToString().TrimEnd();

                        Manager = strArray[i,7].ToString().TrimEnd();

                        Division = strArray[i,8].ToString().TrimEnd();

     Validate(Narnum, EmpName, Position, PreferredName, Surname, Firstname, Manager, Division);

                }

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

            }

        }

C#: Convert names to proper case

I have come across situations where some names or surnames have a character and managing casing is the important task. So i developed a simple method to convert the names to proper case in exceptional cases.

Ex: O'Brien , Tucky-Knight, Van Van, Nra (Nanette)

My method below identify the special character and make the next letter to upper case.


public string ConvertToProperNameCase(string input)
{
    char[] chars = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(input.ToLower()).ToCharArray();

             for (int i = 0; i + 1 < chars.Length; i++)
             {
                 if ((chars[i].Equals('\'')) || (chars[i].Equals('-')) || (chars[i].Equals('(')) || (chars[i].Equals(' ')))
                 {
                     chars[i + 1] = Char.ToUpper(chars[i + 1]);
                 }
             }
    return new string(chars);
}

Visualize work in progress(WIP) at any given point of time in Power BI

It is a very common requirement to visualize the WIP or sales at any given point of time in Power BI. This task sound really simple but it needs extensive DAX effort to summaries this details.

A simple example.

Incident No	Incident Open Date	Incident Closed Date	2018 Jan	2018 Feb	2018 Mar
1ABC	01/01/2018	02/02/2018	1	1	0
2ABC	01/01/2018	30/01/2018	1	0	0
3ABC	15/01/2018	15/03/2018	1	1	1
4ABC	06/02/2018		0	1	1
Total			3	3	2

To do this task, first create the DATE table which has Year and Month columns.

In your data table you use to measure the WIP create a measure like below:

Measure =

 CALCULATE(

DISTINCTCOUNT(Workload[INCIDENTNO]),

GENERATE(VALUES(Dates[TIMELINE_DATE]),

FILTER(Workload,CONTAINS(

    DATESBETWEEN(Dates[TIMELINE_DATE],Workload[CREATED_DATE],Workload[CLOSED_DATE]),

                Dates[TIMELINE_DATE],Dates[TIMELINE_DATE])

)

)

)

****Most importantly this measure works only when your Workload table has NO relationship with Dates table. ****

DAX commands used in above measure are: 

CALCULATE(<expression>,<filter1>,<filter2>…)

DISTINCTCOUNT(<column>)

GENERATE(<table1>, <table2>)

VALUES(<TableNameOrColumnName>)

FILTER(<table>,<filter>)

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

DATESBETWEEN(<dates>,<start_date>,<end_date>)

Add/Remove SQL DB instance in SQL Server

There may be situations that you need to add a new DB instance to one of your production DB's or remove an instance with the purpose of relocation DB's.

Adding and instance or removing an instance is very straight forward if you follow the installation set up wizard. Also you can use below links to refer for knowledge.

Remove:
https://docs.microsoft.com/en-us/sql/sql-server/install/uninstall-an-existing-instance-of-sql-server-setup?view=sql-server-2017

Add feature:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/add-features-to-an-instance-of-sql-server-setup?view=sql-server-2017

However most expensive question is whether the DB server need a restart after adding or removing an instance. What is the knocked on effect on production DB server. I have done this many times in my life and you can add or remove an instance without a DB restart. There is no impact to the other instances on the SQL server. However i would recommend taking a snapshot before performing any action to mitigate any risks.

Hope this answers!

Dateadd command in SQL

This is very much straight forward but easily forgettable. "dateadd" command is a very useful command in queries. That's why i thought of adding this to my blog.

select dateadd(week,-3,getdate());

select dateadd(day,-21,getdate());

Ex:

select  * from dbo.tblMembership

where ValidTill >dateadd(week,-3,getdate());

How to find which tables have specific column names in SQL Server

There are times you are required to find a specific column name and what tables are linked up with similar column names. This is easier with third party tools available however if your organisation does not support any third party tool you might think this is more like a manual work. Of course not! Below is the query to get you there. Try and see :)

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%avail%'
ORDER BY schema_name, table_name;