C# Or LINQ - how to merge two data sheets with different columns in c#

Hi,

I want to merge two data sheets with different columns in c#..

For example,

Based on my sheets and column mention, it should merge the data and display in final output as datatable.

Sheet1 ==> columnA data and columnC data

sheet3 ==> columnD data and Column F data

datatable result ==> merge of ColumnA & ColumnC as one column

                               merge of ColumnD & ColumnF as another column

my input string is sheet and column.

for example,

inPut string Sheets ==> "Sheet1" and "Sheet3"

Input string Columns ==> ColumnA & ColumnC(Sheet1)

                                       ColumnD & ColumnF(Sheet3)...

How to form code in either LINQ or C#..?
Ganesh STech Lead cum developerAsked:
Who is Participating?
 
it_saigeDeveloperCommented:
Like this:
var people = (from s1 in sheet1
              from s2 in sheet2
              where s1.CommonField.Equals(s2.CommonField)
              select new { ColumnA = s1.Field1, ColumnB = s1.Field2, ColumnC = s2.Field1, ColumnD = s2.Field2 }).ConvertToDataTable();

Open in new window

Proof of concept -
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EE_Q29074272
{
    class Program
    {
        static void Main(string[] args)
        {
            var names = (from i in Enumerable.Range(0, 25) select new { ID = i, FirstName = $"FirstName{i}", LastName = $"LastName{i}" });
            var demographics = (from i in Enumerable.Range(0, 25) select new { ID = i, Birthdate = DateTime.Now.AddDays(-(i * 9)).ToString("MM/dd/yyyy"), IsWorking = (i & 1) == 1 });
            var people = (from n in names
                          from d in demographics
                          where n.ID.Equals(d.ID)
                          select new { n.ID, n.FirstName, n.LastName, d.Birthdate, d.IsWorking }).ConvertToDataTable();

            people.PrintToConsole();
            Console.ReadLine();
        }
    }

    static class Extensions
    {
        public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
            var property = default(PropertyDescriptor);

            for (int i = 0; i < properties.Count; i++)
            {
                property = properties[i];
                if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable)))
                    table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
                else
                    table.Columns.Add(property.Name, property.PropertyType);
            }

            object[] values = new object[properties.Count];
            foreach (var item in source)
            {
                for (int i = 0; i < properties.Count; i++)
                    values[i] = properties[i].GetValue(item);
                table.Rows.Add(values);
            }

            return table;
        }

        public static void PrintToConsole(this DataTable table)
        {
            int width = (25 * table.Columns.Count) + table.Columns.Count;
            Console.WriteLine("Table Name: ", table.TableName);
            width.DrawHorizontalSeperator('=');
            Console.WriteLine("|{0}|", string.Join("|", table.Columns.Cast<DataColumn>().Select(x => string.Format("  {0}  ", x.ColumnName).PadRight(25))));
            width.DrawHorizontalSeperator('=');
            foreach (DataRow row in table.Rows)
                Console.WriteLine("|{0}|", string.Join("|", row.ItemArray.Select(x => string.Format("  {0}  ", x.ToString()).PadRight(25))));
            width.DrawHorizontalSeperator('-');
        }

        static void DrawHorizontalSeperator(this int width, char seperator)
        {
            Console.WriteLine(new string(seperator, width));
        }
    }
}

Open in new window

Which produces the following output -Capture.PNG
-saige-
0
 
Ganesh STech Lead cum developerAuthor Commented:
working...
0
 
it_saigeDeveloperCommented:
Just for completeness, you could also perform a Method Based Join:
var people = names.Join(demographics, 
                        name => name.ID, 
                        demographic => demographic.ID, 
                        (name, demographic) => new { name.ID, name.FirstName, name.LastName, demographic.Birthdate, demographic.IsWorking }).ConvertToDataTable();

Open in new window

Or an Expression Based Join:
var people = (from name in names
              join demographic in demographics
              on name.ID equals demographic.ID
              select new { name.ID, name.FirstName, name.LastName, demographic.Birthdate, demographic.IsWorking }).ConvertToDataTable();

Open in new window


-saige-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.