You must Sign In to post a response.
  • Category: General

    Not all rows exported in csv file from excel file?

    I am trying to make small program which reads excel file and write single column data of excel file to csv file. All things going on perfectly , dgv in form also shows all rows, but when i check csv file I found that not all rows copied. There are nearly 6000 rows in excel file but when data copied in csv file there are only 5706 rows last some rows are not copied why it happend , needs sugessions .

    thank you!

    here is the code for read data from excel file,

    public void excel()
    {
    StreamReader sr = new StreamReader("C:\\Users\\Priya\\Desktop\\k.txt");
    string s = sr.ReadLine();
    OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source='"+s+"'"
    + ";Extended Properties=Excel 8.0;");

    StringBuilder stbQuery = new StringBuilder();
    stbQuery.Append("SELECT Product FROM [Sheet1$]");
    OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

    DataSet dsXLS = new DataSet();
    adp.Fill(dsXLS);

    DataView dvEmp = new DataView(dsXLS.Tables[0]);

    dataGridView1.DataSource = dvEmp;
    this.Hide();
    }

    here is the method for export data,

    public void writeCSV(DataGridView gridIn, string outputFile)
    {
    try
    {
    //test to see if the DataGridView has any rows
    if (gridIn.RowCount > 0)
    {
    //string value = "";
    DataGridViewRow dr = new DataGridViewRow();
    StreamWriter swOut = new StreamWriter(outputFile);

    //write header rows to csv
    for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
    {
    if (i > 0)
    {
    swOut.Write(",");
    }
    swOut.Write(gridIn.Columns[i].HeaderText);
    }

    swOut.WriteLine();

    //write DataGridView rows to csv
    for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
    {
    if (j > 0)
    {
    swOut.WriteLine();
    }

    dr = gridIn.Rows[j];

    for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
    {
    if (i > 0)
    {
    swOut.Write(",");
    }
    string value = " ";
    value = dr.Cells[i].Value.ToString();
    //replace comma's with spaces
    value = value.Replace(',', ' ');
    //replace embedded newlines with spaces
    value = value.Replace(Environment.NewLine, " ");

    swOut.Write(value);
    }
    }
    swOut.Close();
    }
    }
    catch { }
    }
  • #759233

    Hello Priyanka,

    Refer the below code:

    try
    {
    System.IO.StreamWriter fileWriter = new StreamWriter("D:\\Test.csv", false);
    string columnHeaderText = "";

    //Writing DataGridView Header in File
    int countColumn = dataGridView1.ColumnCount -1;

    if(countColumn >= 0)
    {
    columnHeaderText = dataGridView1.Columns[0].HeaderText;
    }

    for (int i = 1; i <=countColumn; i++)
    {
    columnHeaderText = columnHeaderText + "," + dataGridView1.Columns[i].HeaderText;
    }

    fileWriter.WriteLine(columnHeaderText);

    //Writing Data in File
    foreach (DataGridViewRow dataRowObject in dataGridView1.Rows)
    {
    if (!dataRowObject.IsNewRow)
    {
    string dataFromGrid = "";

    dataFromGrid = dataRowObject.Cells[0].Value.ToString();

    for (int i = 1; i <= countColumn; i++)
    {
    dataFromGrid = dataFromGrid + "," + dataRowObject.Cells[i].Value.ToString();
    }

    fileWriter.WriteLine(dataFromGrid);
    }
    }

    MessageBox.Show("Data is successfully saved in File");

    fileWriter.Flush();
    fileWriter.Close();
    }
    catch (Exception exceptionObject)
    {
    MessageBox.Show(exceptionObject.ToString());
    }

    Hope this will help you.
    Mark the answer if it helped you.


    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #759236
    There may exist some exception and program lost control of CSV file, check what type of data exist after 5706 rows, OR try to copy same data In all rows and check if it works for you.
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #759240
    Hey thanks i solved it. by replacing single line
    " value = (dr.Cells[i].Value ?? "").ToString();".

  • #759256
    Hello Priyanka,

    Your most welcome.
    We experts are here to help you and we glad to help to to others.

    Happy coding.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"


  • Sign In to post your comments