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,Lon g.
DataTable 1
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,Lon
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;
}
DataTable2public 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();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We could create a couple of extension methods to ease this process (basically what Pawan has recommended but compacted a little) -
Extension methods -
Example usage -
-saige-
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);
}
}
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);
}
}
}
}
Produces the following CSV file --saige-
ASKER
That is Exactly what i needed and tried it out and it worked, thanks a lot buddy.
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