Dynamically create data table based on incoming Excel data in C#?

Question

I'm trying to import an excel sheet into a data table in C#. I keep getting an out of bounds exception because the columns value is being hard coded by me.

for (int c = 0; c <40; c++) { ... }

So rather than hard coding this and having a bunch of empty columns listed on my data table, is there a way to count all the columns being passed in before I create the table? Thereby allowing me to create the table dynamically around the incoming data?

Below is my code.

 if (fileUpload.HasFile)
      {
        DataTable dt = new DataTable();
        for (int c = 0; c <40; c++) { dt.Columns.Add(c.ToString(), typeof(string)); }
        using (Stream fileStream = fileUpload.PostedFile.InputStream)
        using (StreamReader sr = new StreamReader(fileStream))
        {
          string a = null;
          while ((a = sr.ReadLine()) != null)
          {
            dt.Rows.Add(a.Split(new char[] { ',' }).ToArray());
          }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();

gmiley · Accepted Answer · 2016-06-10 23:44:23Z

1

Try using something like this instead:

        DataTable dt;
        using (Stream fileStream = fileUpload.PostedFile.InputStream)
        using (StreamReader sr = new StreamReader(fileStream))
        {
            string a = null;
            while ((a = sr.ReadLine()) != null)
            {
                string[] columns = a.Split(',');
                if (dt == null)
                {
                    dt = new DataTable();
                    for (int i = 0; i < columns.Count(); i++)
                        dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
                }
                dt.Rows.Add(columns);
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();

So long as the first row contains the maximum number of columns that the rest of the data has, you should be all set.

If the first row is column names, you could modify the code to skip the dt.Rows.Add(columns) on the first pass, and replace the i.ToString() with columns[i].ToString() to name the DataColumn properly.

If you columns/values are also surrounded by quotation marks (") then you could modify the while statement above to look like this:

while ((a = sr.ReadLine()) != null)
{
    a = a.Substring(1, a.Length - 2);
    string[] columns = a.Split(new string[] { "\",\"" }, StringSplitOptions.None);
    if (dt == null)
    {
        dt = new DataTable();
        for (int i = 0; i < columns.Count(); i++)
            dt.Columns.Add(new DataColumn(i.ToString(), typeof(string)));
    }
    dt.Rows.Add(a.Split(new char[] { ',' }).ToArray());
}

That will strip the first and last " from the overall string, then split on ",". You might want to play around with it some though, and add code to figure out how your fields are delimited, or have some input fields that you can specify. That is, if your input is variable in how it is defined.

edited Jun 10, 2016 at 23:44

answered Jun 10, 2016 at 23:12

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

heb-NR Over a year ago

Trying it out now. I got an error on the first run. "Input array is longer than the number of columns in this table."

gmiley Over a year ago

If you are getting that, then try setting a breakpoint and looking at your data. Do you have some rows that have more columns than your first row? You shouldn't normally have a problem like that, unless the file is being created in some strange manner.

heb-NR Over a year ago

Yeah, I'm stepping through now. the comma delimiting is creating issues since some of the data is names in "Last, First" format.

gmiley Over a year ago

Are column names/values surrounded with quotation marks as column identifiers? If that is the case, you will need to do some more work to split the columns properly.

gmiley Over a year ago

You could start by stripping the first and last " from the overall string, then do the split using string[] columns = a.Split(new string[] { "\",\"" }, StringSplitOptions.None); instead of the split implemented above.

|

Collectives™ on Stack Overflow

Dynamically create data table based on incoming Excel data in C#?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related