C# loop through data table rows - skips first row

I am creating a datatable to write from an Excel spreadsheet to a csv file.

The code skips the first data row, the column headers. The column headers do not get written to the csv.  Sample spreadsheet attached.

The code:
---------------------------
convertExcelToCSV(@"c:\keys\book2.xls", "Sheet1", @"c:\keys\target.csv")

---------------------------
static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            OleDbConnection conn = null;
            StreamWriter wrtr = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter da = null;
           
            try
            {
                conn = new OleDbConnection(strConn);
                conn.Open();
                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
                cmd.CommandType = CommandType.Text;
                wrtr = new StreamWriter(targetFile);
                da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    string st = dt.Rows[x].ToString();
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        if (y > 0)
                        {
                            rowString += ",";
                        }
                        {
                            rowString += "\"" + dt.Rows[x][y].ToString() + "\"";
                           
                        }
                    }
                    wrtr.WriteLine(rowString);
                }
                MessageBox.Show("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                    conn.Dispose();
                    cmd.Dispose();
                    da.Dispose();
                    wrtr.Close();

                wrtr.Dispose();

            }

        }
Book2.xls
Delta7428Asked:
Who is Participating?
 
anarki_jimbelConnect With a Mentor Commented:
This piece of code prints column names:

            DataTable dt = new DataTable();
            Type typeInt32 = Type.GetType("System.Int32");
            Type typeString = Type.GetType("System.String");
            DataColumn dc1 = new DataColumn("A",typeInt32);
            DataColumn dc2 = new DataColumn("B",typeString);
            DataColumn dc3 = new DataColumn("C", typeString);
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);

            string rowString = "";

            for (int y = 0; y < dt.Columns.Count; y++)
            {
                if (y > 0)
                {
                    rowString += ",";
                }
                rowString += dt.Columns[y].ColumnName;


            }
                System.Diagnostics.Debug.WriteLine(rowString);

Open in new window



Output:
A,B,C

Open in new window

0
 
Delta7428Author Commented:
Thanks.  I will test your code in the morning.

But thinking ... Maybe the data table wasn't the best way to go here.   Maybe I have a spreadsheet that doesn't have column names and the the first row is a data row, not column names.    A datatable always assumes the first row is column names ... makes sense, but may not be what I need for other spreadsheet formats.

Seems like a lot of hoopla to get that first row.
0
 
Delta7428Author Commented:
Thanks anarki.  I got pulled off on another assignment.  I will get back to you in a day or 2.
0
 
Delta7428Author Commented:
I posted a follow up question .. copied and pasted and submitted it by mistake...  Decided to take another look at resolving followup question on my own.  Looks like I can't delete a comment so I wiped it out and replaced it with this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.