Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

C#  loop through data table rows - skips first row

Posted on 2013-06-30
5
2,219 Views
Last Modified: 2013-07-05
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
0
Comment
Question by:Delta7428
  • 3
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 39288569
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
 

Author Comment

by:Delta7428
ID: 39288609
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
 
LVL 29

Expert Comment

by:anarki_jimbel
ID: 39288679
0
 

Author Comment

by:Delta7428
ID: 39292712
Thanks anarki.  I got pulled off on another assignment.  I will get back to you in a day or 2.
0
 

Author Comment

by:Delta7428
ID: 39300612
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question