/// <summary> /// Save the table to the given stream, using a CSV format. The first line will be the headers, and then each subsequent line will be a row. /// This will escape characters as needed. /// </summary> /// <param name="output">textwrite to write out to.</param> public virtual void SaveToStream(TextWriter output) { using (var writer = new CsvWriter(output, this.ColumnNames)) { foreach (var row in this.Rows) { writer.WriteRow(row); } } }
// $$$ Clarify - multiple joins (inner, outer, etc) /// <summary> /// Performs a full outer join on two in-memory tables and returns a new table. /// The number of rows in the resulting table is the sum of rows from each source table. /// The number of columns in teh new table is the sum of columns in the the source tables minus 1 /// (since the join column is redundant) /// </summary> /// <param name="d1"></param> /// <param name="d2"></param> /// <param name="columnName">column name to join on. Both tables must have this column name.</param> /// <returns>a new table</returns> public static MutableDataTable Join(MutableDataTable d1, MutableDataTable d2, string columnName) { Column c1 = d1.GetColumn(columnName); if (c1 == null) { throw new InvalidOperationException("Missing column"); } Column c2 = d2.GetColumn(columnName); if (c2 == null) { throw new InvalidOperationException("Missing column"); } // Place d1 in first set of columns, and d2 in second set. int kColumn = d1.Columns.Length; int kTotalColumns = kColumn + d2.Columns.Length; // Indices into new table where join columns are. int joinColumn1 = Utility.GetColumnIndexFromName(d1.ColumnNames, columnName); int joinColumn2 = Utility.GetColumnIndexFromName(d2.ColumnNames, columnName) + kColumn; // $$$ could really optimize. Sort both on column and then zip. Dictionary <string, int> m1 = GetRowIndex(c1); Dictionary <string, int> m2 = GetRowIndex(c2); // $$$ column names may not be unique. //string[] headers = d1.ColumnNames.Union(d2.ColumnNames).ToArray(); string[] headers = new string[kTotalColumns]; Array.Copy(d1.ColumnNames.ToArray(), 0, headers, 0, kColumn); Array.Copy(d2.ColumnNames.ToArray(), 0, headers, kColumn, kTotalColumns - kColumn); string[] values = new string[headers.Length]; string path = GetTempFileName(); using (CsvWriter tw = new CsvWriter(path, headers)) { foreach (var kv in m1) { Clear(values); string key = kv.Key; // join column int r1 = kv.Value; int r2; if (m2.TryGetValue(key, out r2)) { // In both. write out CopyRowIntoArray(values, kColumn, d2, r2); m2.Remove(key); } else { // Only in M1. } CopyRowIntoArray(values, 0, d1, r1); values[joinColumn1] = values[joinColumn2] = key; tw.WriteRow(values); } // We remove all of M1's items from m2, so M2 is just unique items now. (possibly 0). // Tag those onto the end. foreach (var kv in m2) { int r2 = kv.Value; Clear(values); CopyRowIntoArray(values, kColumn, d2, r2); values[joinColumn1] = values[joinColumn2] = kv.Key; tw.WriteRow(values); } } // close tw MutableDataTable t = Reader.ReadCSV(path); DeleteLocalFile(path); // Remove duplicate columns. t.DeleteColumn(joinColumn2); return(t); }
// $$$ Clarify - multiple joins (inner, outer, etc) /// <summary> /// Performs a full outer join on two in-memory tables and returns a new table. /// The number of rows in the resulting table is the sum of rows from each source table. /// The number of columns in teh new table is the sum of columns in the the source tables minus 1 /// (since the join column is redundant) /// </summary> /// <param name="d1"></param> /// <param name="d2"></param> /// <param name="columnName">column name to join on. Both tables must have this column name.</param> /// <returns>a new table</returns> public static MutableDataTable Join(MutableDataTable d1, MutableDataTable d2, string columnName) { Column c1 = d1.GetColumn(columnName); if (c1 == null) { throw new InvalidOperationException("Missing column"); } Column c2 = d2.GetColumn(columnName); if (c2 == null) { throw new InvalidOperationException("Missing column"); } // Place d1 in first set of columns, and d2 in second set. int kColumn = d1.Columns.Length; int kTotalColumns = kColumn + d2.Columns.Length; // Indices into new table where join columns are. int joinColumn1 = Utility.GetColumnIndexFromName(d1.ColumnNames, columnName); int joinColumn2 = Utility.GetColumnIndexFromName(d2.ColumnNames, columnName) + kColumn; // $$$ could really optimize. Sort both on column and then zip. Dictionary<string, int> m1 = GetRowIndex(c1); Dictionary<string, int> m2 = GetRowIndex(c2); // $$$ column names may not be unique. //string[] headers = d1.ColumnNames.Union(d2.ColumnNames).ToArray(); string[] headers = new string[kTotalColumns]; Array.Copy(d1.ColumnNames.ToArray(), 0, headers, 0, kColumn); Array.Copy(d2.ColumnNames.ToArray(), 0, headers, kColumn, kTotalColumns - kColumn); string[] values = new string[headers.Length]; string path = GetTempFileName(); using (CsvWriter tw = new CsvWriter(path, headers)) { foreach (var kv in m1) { Clear(values); string key = kv.Key; // join column int r1 = kv.Value; int r2; if (m2.TryGetValue(key, out r2)) { // In both. write out CopyRowIntoArray(values, kColumn, d2, r2); m2.Remove(key); } else { // Only in M1. } CopyRowIntoArray(values, 0, d1, r1); values[joinColumn1] = values[joinColumn2] = key; tw.WriteRow(values); } // We remove all of M1's items from m2, so M2 is just unique items now. (possibly 0). // Tag those onto the end. foreach (var kv in m2) { int r2 = kv.Value; Clear(values); CopyRowIntoArray(values, kColumn, d2, r2); values[joinColumn1] = values[joinColumn2] = kv.Key; tw.WriteRow(values); } } // close tw MutableDataTable t = Reader.ReadCSV(path); DeleteLocalFile(path); // Remove duplicate columns. t.DeleteColumn(joinColumn2); return t; }
/// <summary> /// Save the table to the given stream, using a CSV format. The first line will be the headers, and then each subsequent line will be a row. /// This will escape characters as needed. /// </summary> /// <param name="output">textwrite to write out to.</param> public virtual void SaveToStream(TextWriter output) { using (var writer = new CsvWriter(output, this.ColumnNames)) { foreach (var row in this.Rows) { writer.WriteRow(row); } } }