c# OleDbConnection csv to excel skips first line from csv

Question

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;
                    }
                }

score 1 · Accepted Answer · answered May 27 '14 at 19:08

1

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.

answered May 27 '14 at 19:08

Andy G

19,232
5
47
69

I have used this technique in this answer with much success in the past. In addition, this website has been very helpful to me in the past for getting my connection strings up and running: http://www.connectionstrings.com/. Here is a link to their connection strings on the Jet OLE DB Provider: http://www.connectionstrings.com/microsoft-jet-ole-db-4-0/ – xDaevax May 27 '14 at 19:12
I forgot that I had a schemes.ini file, which took precedence. – longlostbro May 27 '14 at 23:57

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

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.

edited May 23 '17 at 10:26

Community

1
1

answered May 27 '14 at 19:04

Michael Paulukonis

9,020
5
48
68

score 0 · Answer 3 · answered May 28 '14 at 20:25

0

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

answered May 28 '14 at 20:25

longlostbro

528
2
7
24

c# OleDbConnection csv to excel skips first line from csv

3 Answers3