Concatenate strings in MS-Excel

Here we will learn how we can easily concatenate string in MS-Excel which could be useful in many situations. I am going to describe a situation which I encountered once.

Let us assume a situation, we have a huge list of numbers (say Roll number of students in a university) saved in MS-Excel sheet and we want to write a SQL query that updates the section of all the students in that huge list. This we could do easily with a update SQL query using “IN” operator but problem is that we need to have our Student’s Roll number list in a format like ‘RollNum1’, ‘RollNum2’, ‘RollNum3’….. and in Excel sheet, we have a just a list of Roll numbers. Just for sake of understanding the scenario, a sample update query is given below:

UPDATE               TSTUDENTS

SET                         STUDNT_SECTN  =  ‘C2’

WHERE                  ROLL_NUM IN (a comma separated list in which each element is apostrophe enclosed)

So what’s next? Let’s go for the concatenation of strings in MS-Excel. We could do concatenation in Excel by following ways:

(1)    Suppose we have a list in column-A

(2)    On cell B-1, type “,”

(3)    Then Fill the entire column –B using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-B

(4)    Now we could apply our concatenation in column-D. Got to Cell D1.

(5)    Concatenate the string either using string built-in concatenate function                

=CONCATENATE("'", A1, "'",B1)  

Or we could use “&” symbol for concatenation as   ="'" & A1 & "'" & B1

(6)    Then Fill the entire column –D using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-D.

(7)    That’s it, we are done!

 

  A                     B             C                 D

B12 9	,		'B12 9',
B13 0	,		'B13 0',
B13 8	,		'B13 8',
B13 9	,		'B13 9',
B14  6	,		'B14  6',
B14 4	,		'B14 4',
B14 5	,		'B14 5',
B45 0	,		'B45 0',
B45 9	,		'B45 9',
B46 1	,		'B46 1',
B46 2	,		'B46 2',
B46 3	,		'B46 3',
B47 5	,		'B47 5',
B47 6	,		'B47 6',
B48 7	,		'B48 7',
B49 5	,		'B49 5',
B49 6	,		'B49 6',
B5 4	,		'B5 4',
B5 5	,		'B5 5',
B5 6	,		'B5 6',
B5 7	,		'B5 7',
B50 4	,		'B50 4',
B6 4	,		'B6 4',
B6 5	,		'B6 5',
B6 6	,		'B6 6',

 Thanks,

Hemant

 

C# Code to deserialize a Byte[] Array into DataTable

In WCF, sometimes we get a ByteArray as an output data contract and we want to deserialize that ByteArray into a DataTable.
Here we could get an idea how we can deserialize a ByteArray into DataTable.  DataTable becomes useful int the situations when we want to save data into database using DataAdapter by passing a DataSet.
In this code, we create a MemoryStream object by passing byte array and then using BinaryFormatter, we can directly deserialize MemoryStream object into DataTable.

 
 private DataSet DeserailizeByteArrayToDataSet(byte[] byteArrayData)  
     {  
       DataSet tempDataSet = new DataSet();  
       DataTable dt;  
       // Deserializing into datatable    
       using (MemoryStream stream = new MemoryStream(byteArrayData))  
       {  
         BinaryFormatter bformatter = new BinaryFormatter();  
         dt = (DataTable)bformatter.Deserialize(stream);  
         if (dt != null)  
         {  
           foreach (DataRow row in dt.Rows)  
           {  
             Console.WriteLine("----------------------");  
             Console.WriteLine("PostalCode:" + row["POSTAL_CODE"]);  
             Console.WriteLine("CountryCode:" + row["COUNTRY_CODE"]);  
             Console.WriteLine("ProcessNumber:" + row["PROCESS_NUMBER"]);  
             Console.WriteLine("ProcessStatus:" + row["PROCESS_STATUS"]);  
             Console.WriteLine("TimeStamp:" + row["TIMESTAMP"]);  
             Console.WriteLine("----------------------");  
           }  
         }  
       }  
       // Adding DataTable into DataSet    
       tempDataSet.Tables.Add(dt);  
       return tempDataSet;  
     }  

Thanks,
Hemant