ASP.Net MVC Error: Input array is longer than the number of columns in this table

Question

I'm uploading a .txt file using ASP.Net MVC. I used the codes from http://www.dotnetcurry.com/showarticle.aspx?ID=323. When I'm uploading a .txt file which has more than 30,000 rows I'm getting this error: Input array is longer than the number of columns in this table.

--

Table TMPUPLOAD columns:

vdate | time | ampm | empno

--

sample data of my .txt file:

08/01/12,05:09:09 AM,200441,,North Lobby1,358709,VERIFY_OK

08/01/12,05:09:09 AM,200441,,North Lobby1,358709,VERIFY_OK

08/22/12,12:13:36 PM,228079,Lim,Benedict Drew,South Lobby2,123903,VERIFY_OK

--

and the codes:

public ActionResult ChronologFiles()
        {
            return View();
        }

        [HttpPost]
        public ActionResult ChronologFiles(HttpPostedFileBase FileUpload)
        {
           DataTable dt = new DataTable();
            string line = string.Empty;
            int i = 0;

            //check we have a file
            if (FileUpload.ContentLength > 0)
            {
                //Workout our file path
                string fileName = Path.GetFileName(FileUpload.FileName);
                string path = Path.Combine(Server.MapPath("~/App_Data/UploadedFiles"), fileName);

                //Try and upload
                try
                {
                    FileUpload.SaveAs(path);
                    //Process the CSV file and capture the results to our DataTable place holder
                    dt = ProcessCSV(path);

                    //Process the DataTable and capture the results to our SQL Bulk copy
                    ViewData["Feedback"] = ProcessBulkCopy(dt);

                    using (StreamReader sr = new StreamReader(path, true))
                    {
                        while ((line = sr.ReadLine()) != null)
                        {
                            string[] data = line.Split(',', ' ', '\n');
                            if (data.Length > 0)
                            {
                                if (i == 0)
                                {
                                    foreach (var item in data)
                                    {
                                        dt.Columns.Add(new DataColumn());
                                    }
                                    i++;
                                }
                                DataRow row = dt.NewRow();
                                row.ItemArray = data;
                                dt.Rows.Add(row);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //Catch errors
                    ViewData["Feedback"] = ex.Message;
                }
            }
            else
            {
                //Catch errors
                ViewData["Feedback"] = "Please select a file";
            }

            //Tidy up
            dt.Dispose();


            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ALEMSDB"].ConnectionString))
            {
                con.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(con))
                {
                    copy.ColumnMappings.Add(0, "vdate");
                    copy.ColumnMappings.Add(1, "time");
                    copy.ColumnMappings.Add(2, "ampm");
                    copy.ColumnMappings.Add(3, "empno");
                    copy.DestinationTableName = "TMPUPLOAD";
                    copy.WriteToServer(dt);
                }

                using (SqlCommand cmd2 = new SqlCommand("DELETE FROM TMPUPLOAD WHERE empno = ''", con))
                {

                    int rows = cmd2.ExecuteNonQuery();

                    //rows number of record got inserted
                }

                using (SqlCommand cmd2 = new SqlCommand(@"INSERT INTO UPLOADING(vdate, [time], ampm, empno)
                                                            SELECT vdate, [time], ampm, empno
                                                            FROM TMPUPLOAD", con))
                {

                    int rows = cmd2.ExecuteNonQuery();

                    //rows number of record got inserted
                }

                using (SqlCommand cmd3 = new SqlCommand(@"UPDATE UPLOADING
                                                            SET inout = 'I'
                                                            WHERE [time] BETWEEN '04:00:00' AND '11:59:59' OR [time] BETWEEN '12:00:00' AND '16:00:00' 
                                                                OR [time] BETWEEN '18:00:00' AND '23:59:59' AND ampm = 'AM'", con))
                {

                    int rows = cmd3.ExecuteNonQuery();

                    //rows number of record got inserted
                }

                using (SqlCommand cmd4 = new SqlCommand(@"UPDATE UPLOADING
                                                            SET inout = 'O'
                                                            WHERE [time] BETWEEN '12:00:00' AND '12:59:59' OR [time] BETWEEN '13:00:00' AND '17:59:59'
                                                                OR [time] BETWEEN '18:00:00' AND '23:59:59' AND ampm = 'PM'", con))
                {

                    int rows = cmd4.ExecuteNonQuery();

                    //rows number of record got inserted
                }
            }



            return View();
        }

I only need the 08/01/12,05:09:09 AM,200441 that's why I only have four columns in my table and I just really want to know why I am getting this error.. Thanks in advance! :)

Answer 1

string[] data = line.Split(',', ' ', '\n');

this line of code will split

05:09:09 AM North Lobby1

to 2 column due to the space.

Answer 2

So you are doing:

//Process the CSV file and capture the results to our DataTable place holder
dt = ProcessCSV(path);

Followed by:

if (i == 0)
{
    // Weren't the columns already added by the ProcessCSV method?
    foreach (var item in data)
    {
        dt.Columns.Add(new DataColumn());
    }
    i++;
}

If I understand correctly, you already added the data to your DataTable in the ProcessCSV method. A DataTable will not allow you to add any columns if it already has data in it.

Parsing CSV usually requires more than just a split method because of different formatting requirements for certain fields. Rather than trying to reinvent the wheel for parsing CSV, you should use the built-in parser described here. Once you have the data into separate CSV fields, you can then do a split on your date field.