Link to home
Start Free TrialLog in
Avatar of Aravind Ranganathan
Aravind Ranganathan

asked on

Combine two datatables into one based on a value from second datatable

i have 2 datatables

1st Datatable
CustomerID, Name, City, State,Zip

2nd Datatable (from a CSV)
Zip, Lat, Long

Now i need to do a foreach value in Datatable 1 i need to pass in the Zip from Second table and if a match is found i need to get the Lat and Long Values and insert into the 1st DataTable.

The End Result will be a CSV File With Header like such CustomerID, Name,City,Sate,Zip,Lat,Long.

DataTable 1
 public static DataTable GetGroup1Data()
        {
            string threeyearsago = (DateTime.Now.Year - 2).ToString();
            OdbcConnection MyCon = new OdbcConnection();
            MyCon.ConnectionString = ConfigurationManager.ConnectionStrings["DBLive"].ConnectionString;
            MyCon.Open();

            OdbcCommand mycmd = new OdbcCommand();
            mycmd.Connection = MyCon;
            mycmd.CommandType = CommandType.Text;
            mycmd.CommandText = "{SET ISOLATION TO DIRTY READ} SELECT LPad(LTRIM(T_CUNO),6,'0') as CUSTOMER, REPLACE(T_NAMA,',','') as NAME,RTrim(LEFT(T_NAME,charindex(',',T_NAME)-1)) as CITY,SUBSTR(T_NAME,CHARINDEX(',',T_NAME)+1,4) AS State,CASE WHEN LENGTH(T_NAMF) ='5' THEN RPAD(RTRIM(T_NAMF),9,'0') WHEN LENGTH(T_NAMF) ='10' THEN REPLACE(T_NAMF,'-','') ELSE T_NAMF END as ZIPCODE From Customer;;
            DataSet custmaster = new DataSet();
            OdbcDataAdapter myadapter = new OdbcDataAdapter();
            myadapter.SelectCommand = mycmd;
            DataTable dt = new DataTable();
            dt.Load(mycmd.ExecuteReader());
            mycmd.Dispose();
            MyCon.Close();
            return dt;
        }

Open in new window

DataTable2

public static void ToCSV(this DataTable dtDataTable, string strFilePath)
        {

            StreamWriter sw = new StreamWriter(strFilePath, false);

            //headers  
            for (int i = 0; i < dtDataTable.Columns.Count; i++)
            {
                sw.Write(dtDataTable.Columns[i].ToString().ToUpper());
                if (i < dtDataTable.Columns.Count - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
            foreach (DataRow dr in dtDataTable.Rows)
            {
                for (int i = 0; i < dtDataTable.Columns.Count; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        string value = dr[i].ToString();
                        if (value.Contains(','))
                        {
                            value = String.Format("\"{0}\"", value.ToString().Trim());
                            sw.Write(value);
                        }
                        else
                        {
                            sw.Write(dr[i].ToString().Trim());
                        }
                    }
                    if (i < dtDataTable.Columns.Count - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }

Open in new window

Avatar of Mike McCracken
Mike McCracken

Is there a reason you can't do this with joined tables and just SQL?

Seems something like

SELECT D1.CustomerID, D1.Name, D1.City, D1.State, D1.Zip, D2.Lat, D2.Long
FROM Datatable1 D1 INNER JOIN Datatable2 D2 ON D1.Zip = D2.Zip

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We could create a couple of extension methods to ease this process (basically what Pawan has recommended but compacted a little) -

Extension methods -
public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source)
{
	DataTable table = new DataTable();
	var properties = TypeDescriptor.GetProperties(typeof(T));
	foreach (PropertyDescriptor property in properties)
	{
		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 DataTableToCSV(this DataTable source, string filename)
{
	var results = new List<string>();
	results.Add(string.Join(",", (from DataColumn column in source.Columns select column.ColumnName)));
	results.AddRange((from DataRow row in source.AsEnumerable() select (string)row.ItemArray.Aggregate((nc, c) => string.Format("{0}, {1}", nc, c.ToString().Contains(",") ? string.Format("\"{0}\"", c) : c.ToString()))));
	using (var stream = new FileStream(filename, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
	using (var writer = new StreamWriter(stream))
	{
		foreach (var item in results)
			writer.WriteLine(item);
	}
}

Open in new window


Example usage -
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;

namespace EE_Q28991022
{
	class Program
	{
		static DataTable customers;
		static DataTable zipcodes;

		static void Main(string[] args)
		{
			FillDataTables();
			var output = (from customer in customers.AsEnumerable()
					    join zipcode in zipcodes.AsEnumerable() 
					    on customer.Field<string>("Zip") equals zipcode.Field<string>("Zip")
					    select new
					    {
						    ID = customer.Field<int>("ID"),
						    Name = customer.Field<string>("Name"),
						    City = customer.Field<string>("City"),
						    State = customer.Field<string>("State"),
						    Zip = zipcode.Field<string>("Zip") /* you could also use customer.Field<string>("Zip") */,
						    Lat = zipcode.Field<double>("Lat"),
						    Long = zipcode.Field<double>("Long")
					    }).ConvertToDataTable();
			output.DataTableToCSV("sample.csv");
		}

		static void FillDataTables()
		{
			customers = new List<Customer>() 
			{
				new Customer() { ID = 1, Name = "Paul", City = "Bostom", State = "MA", Zip = "02127" },
				new Customer() { ID = 2, Name = "Larry", City = "Coeur d'Alene", State = "ID", Zip = "83814" },
				new Customer() { ID = 3, Name = "Nancy", City = "Anchorage", State = "AK", Zip = "99504" },
				new Customer() { ID = 4, Name = "Carl", City = "Miami", State = "FL", Zip = "33131" },
				new Customer() { ID = 5, Name = "Perry", City = "Boise", State = "ID", Zip = "83704" },
				new Customer() { ID = 6, Name = "Lucy", City = "Bostom", State = "MA", Zip = "02108" }
			}.ConvertToDataTable();

			zipcodes = new List<GeoZip>()
			{
				new GeoZip() { Zip = "02090", Lat = 42.219645, Long = -71.216769 },
				new GeoZip() { Zip = "02093", Lat = 42.052671, Long = -71.356858 },
				new GeoZip() { Zip = "02108", Lat = 42.357768, Long = -71.064858 },
				new GeoZip() { Zip = "02109", Lat = 42.367032, Long = -71.050493 },
				new GeoZip() { Zip = "02110", Lat = 42.361962, Long = -71.047846 },
				new GeoZip() { Zip = "02125", Lat = 42.315682, Long = -71.055555 },
				new GeoZip() { Zip = "02126", Lat = 42.274227, Long = -71.097423 },
				new GeoZip() { Zip = "02127", Lat = 42.334992, Long = -71.039093 },
				new GeoZip() { Zip = "02128", Lat = 42.361129, Long = -71.006975 },
				new GeoZip() { Zip = "02129", Lat = 42.379657, Long = -71.061487 },
				new GeoZip() { Zip = "33129", Lat = 25.750167, Long = -80.190028 },
				new GeoZip() { Zip = "33130", Lat = 25.768524, Long = -80.203359 },
				new GeoZip() { Zip = "33131", Lat = 25.766206, Long = -80.182897 },
				new GeoZip() { Zip = "33132", Lat = 25.777890, Long = -80.176165 },
				new GeoZip() { Zip = "33133", Lat = 25.728662, Long = -80.239996 },
				new GeoZip() { Zip = "83702", Lat = 43.680472, Long = -116.160137 },
				new GeoZip() { Zip = "83703", Lat = 43.665568, Long = -116.243033 },
				new GeoZip() { Zip = "83704", Lat = 43.627161, Long = -116.287653 },
				new GeoZip() { Zip = "83705", Lat = 43.561558, Long = -116.214363 },
				new GeoZip() { Zip = "83706", Lat = 43.591442, Long = -116.194224 },
				new GeoZip() { Zip = "83812", Lat = 47.023228, Long = -116.233587 },
				new GeoZip() { Zip = "83813", Lat = 48.085042, Long = -116.672772 },
				new GeoZip() { Zip = "83814", Lat = 47.685687, Long = -116.627768 },
				new GeoZip() { Zip = "83815", Lat = 47.726282, Long = -116.789948 },
				new GeoZip() { Zip = "83821", Lat = 48.538977, Long = -116.850647 },
				new GeoZip() { Zip = "99502", Lat = 61.163643, Long = -149.996609 },
				new GeoZip() { Zip = "99503", Lat = 62.043951, Long = -158.175667 },
				new GeoZip() { Zip = "99504", Lat = 61.204600, Long = -149.746095 },
				new GeoZip() { Zip = "99505", Lat = 61.256851, Long = -149.602697 },
				new GeoZip() { Zip = "99506", Lat = 61.253463, Long = -149.810757 }
			}.ConvertToDataTable();
		}
	}

	class Customer
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public string City { get; set; }
		public string State { get; set; }
		public string Zip { get; set; }
	}

	class GeoZip
	{
		public string Zip { get; set; }
		public double Lat { get; set; }
		public double Long { get; set; }
	}

	static class Extensions
	{
		public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source)
		{
			DataTable table = new DataTable();
			var properties = TypeDescriptor.GetProperties(typeof(T));
			foreach (PropertyDescriptor property in properties)
			{
				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 DataTableToCSV(this DataTable source, string filename)
		{
			var results = new List<string>();
			results.Add(string.Join(",", (from DataColumn column in source.Columns select column.ColumnName)));
			results.AddRange((from DataRow row in source.AsEnumerable() select (string)row.ItemArray.Aggregate((nc, c) => string.Format("{0}, {1}", nc, c.ToString().Contains(",") ? string.Format("\"{0}\"", c) : c.ToString()))));
			using (var stream = new FileStream(filename, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
			using (var writer = new StreamWriter(stream))
			{
				foreach (var item in results)
					writer.WriteLine(item);
			}
		}
	}
}

Open in new window

Produces the following CSV file -User generated image
-saige-
Avatar of Aravind Ranganathan

ASKER

That is Exactly what i needed and tried it out and it worked, thanks a lot buddy.