how can i convert all rows into txt file?

Question

i already have the code for convertion from .xls to .txt. but the problem is, it only exports the one row and it stops. what do i need to add for the code to read all rows and convert it into txt file?

here is my code below:

using (OleDbConnection cn = new OleDbConnection())
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"C:\Users\jediablaza\Documents\EDIExcel\EDIExcel.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
                cmd.Connection = cn;
                cmd.CommandText = "SELECT * from [Sheet1$]";
                using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    using (StreamWriter wr = new StreamWriter(@"C:\Users\jediablaza\Documents\EDIExcel\EDIExcel.txt"))
                    {
                        foreach (DataRow row in dt.Rows)
                        {

                                wr.WriteLine(row[0] + "\t" + row[1] + "\t" + row[2] + "\t" + row[3] + "\t" + row[4]);

                        }
                    }
                }
            }
        }

the output of the text is shows below

SHARPDTL    TESTDTL1    TESTDTL2    TESTDTL3    TESTDTL4

but the excel file content is

SHARPHDR1   TESTDHR1    TESTHDR2    TESTHDR3    TESTHDR4
SHARPDTL    TESTDTL1    TESTDTL2    TESTDTL3    TESTDTL4

Answer 1

I think problem is in your connection property: HDR=Yes.

So the first line would be the header of file. Change it to HDR=No

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

Reference link: excel connection