C# .NET - error while converting the CSV to datatable

Asked By muthuraman alexander on 05-Jan-12 01:39 AM
hi All

i got error "Input array is longer than the number of columns in this table"
while i convert the CSV file to a datatable

my code is   c#
-----------------

protected void import_Click(object sender,EventArgs e)
{
 try
      {
        if (flImportfilename != null && flImportfilename.HasFile)
        {  
          string strfilename = flImportfilename.PostedFile.FileName.ToString();
          DataTable dt = GetDataTable(strfilename);    

        //here is the process to insert into database
       }
     }
      catch (Exception)
      {
      }
 }

 public DataTable GetDataTable(string strFileName)
    {
     //CSVReader is class to read the CSV file
      CSVReader reader = new CSVReader(flImportfilename.PostedFile.InputStream);
      string[] headers = reader.GetCSVLine();
      DataTable dt = new DataTable();
      foreach (string strHeader in headers)
        dt.Columns.Add(strHeader);

      string[] data;
      while ((data = reader.GetCSVLine()) != null)      -------------> im getting error in this method
        dt.Rows.Add(data);
      return dt;
    }


please advice me

thanks in advance
 muthu
Riley K replied to muthuraman alexander on 05-Jan-12 01:52 AM


Here is the simplified version convert csv to datatable

Regards
Reena Jain replied to muthuraman alexander on 05-Jan-12 01:57 AM
Hi,

try this and let me know plz

private void button1_Click(object sender, EventArgs e)
 
{
 
  
 
string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
 
OleDbConnection con = new OleDbConnection(strProvier);
 
OleDbCommand cmd = new OleDbCommand();
 
int effectedRow = 0;
 
int lineCounter = 0;
 
con.Open();
 
  
 
if ((con.State.ToString() == "Open"))
 
{
 
StreamReader stReader =
 
new StreamReader("Employees.csv");
 
string[] strRowData = null;
 
while (stReader.Peek() >= 0)
 
{
 
lineCounter = lineCounter + 1;
 
strRowData = stReader.ReadLine().Split(
 
",");
 
try
 
{
 
cmd.CommandText =
 
"INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" + strRowData(1) + "','" + strRowData(2) + "')";
 
cmd.Connection = con;
 
effectedRow = cmd.ExecuteNonQuery();
 
  
 
if ((effectedRow == -1))
 
{
 
  
 
// Messagebox.Show("Line: " + lineCounter + " Error");
 
}
 
  
 
else
 
{
 
  
 
// Messagebox.Show("Line: " + lineCounter + " Executed Successfully");
 
}
 
}
 
 
catch (OleDbException er)
 
{
 
Messagebox.Show(
 
"Line: " + lineCounter + " Error: " + er.Message);
 
}
 
}
 
stReader.Close();
 
con.Close();
 
}
 
  
 
else
 
{
 
Messagebox.Show(
 
"Not Connected To Database");
 
}
 
}
Jitendra Faye replied to muthuraman alexander on 05-Jan-12 04:38 AM
Here is the working code 

string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
 OleDbConnection con = new OleDbConnection(strProvier);
 OleDbCommand cmd = new OleDbCommand();
 int effectedRow = 0;
 int lineCounter = 0;
 
 con.Open();
 
 if ((con.State.ToString() == "Open"))
{
   
   StreamReader stReader = new StreamReader("Employees.csv");
   string[] strRowData = null;
   
   while (stReader.Peek() >= 0)
   {
   lineCounter = lineCounter + 1;
   strRowData = stReader.ReadLine().Split(",");
   
   try {
   cmd.CommandText = "INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" +       strRowData(1) + "','" + strRowData(2) + "')";
   cmd.Connection = con;
   effectedRow = cmd.ExecuteNonQuery();
   
   if ((effectedRow == -1)) {
   //  Messagebox.Show("Line: " + lineCounter + " Error"); 
   }
   else {
  // Messagebox.Show("Line: " + lineCounter + " Executed Successfully");
   
   }
   }
   catch (OleDbException er) {
   Messagebox.Show("Line: " + lineCounter + " Error: " + er.Message);
   
   }
   }
   stReader.Close();
   con.Close();
 }
 else {
   Messagebox.Show("Not Connected To Database");
 }


Try this and let em know.
kalpana aparnathi replied to muthuraman alexander on 05-Jan-12 04:47 AM
hi,

ImportRow only imports a single row at a time and therefore I think it would hardly do anything to speed up the performance.

Thanks,