CODEWITHSUNDEEP
c#excelcsvoledb
longlostbro
longlostbro

Reputation: 564

c# OleDbConnection csv to excel skips first line from csv

When using this code for some reason it skips the first line of the csv file, which are the headers. What am I doing wrong?

string strFileName = path;
                OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text\"");
                conn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(strFileName), conn);
                DataSet ds = new DataSet("Temp");
                adapter.Fill(ds);
                DataTable tb = ds.Tables[0];
                string data = null;
                for (int j = 0; j <= tb.Rows.Count - 1; j++)
                {
                    for (int k = 0; k <= tb.Columns.Count - 1; k++)
                    {

                        data = tb.Rows[j].ItemArray[k].ToString();
                        SaturnAddIn.getInstance().Application.ActiveWorkbook.ActiveSheet.Cells[j + 1, k + 1] = data;
                    }
                }

Upvotes: 0

Views: 1368

Answers (3)

longlostbro
longlostbro

Reputation: 564

Also found that when HDR=YES you can get the first column using the table.Columns[0].ColumnName and using some sort of loop.

Upvotes: 0

Andy G
Andy G

Reputation: 19367

It will skip the first row of headers, unless you use:

Extended Properties=Text;HDR=No;

But in this case it will treat the first row as a data-row which will probably (at some stage) cause data-type errors.

Normally you would skip the first row, and create the headers in Excel manually.

Upvotes: 1

Michael Paulukonis
Michael Paulukonis

Reputation: 9100

This comment notes the same behavior when the FULL PATH is passed into the SELECT statement. Since the directory of the file is provided in the OleDbConnection it does not need to be provided a second time.

There are some similar notes at this answer (to a different question) that indicate that the path should be in the connection, as well.

It also recommends using a "real" CSV parser.

Upvotes: 0

Related Questions