It's a provider limit for the sheet - see here:
Import data from Excel File into a dataTable: column limit?[
^]
Quote:
Yes I have tried to merge two datasets but its merging not as columns.
The Merge method doesn't add columns: it adds table rows together where the table schema is the same. That isn't the case for you: you need to add columns.
Try this:
public static DataTable MergeTables(DataTable t1, DataTable t2)
{
DataTable merged = t1.Clone();
foreach (DataColumn col in t2.Columns)
{
string newColumnName = col.ColumnName;
merged.Columns.Add(newColumnName, col.DataType);
}
var mergedRows = t1.AsEnumerable().Zip(t2.AsEnumerable(), (r1, r2) => r1.ItemArray.Concat(r2.ItemArray).ToArray());
foreach (object[] rowFields in mergedRows)
{
merged.Rows.Add(rowFields);
}
return merged;
}
It's pretty obvious how to use it:
DataTable dt1 = new DataTable();
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Rows.Add(1, "Mike");
dt1.Rows.Add(2, "Susan");
var dt2 = new DataTable();
dt2.Columns.Add("Country", typeof(string));
dt2.Rows.Add("UK");
dt2.Rows.Add("France");
DataTable dtMerged = MergeTables(dt1, dt2);