OleDbDataReader skips first record

Question

I have the following code :

OleDbDataReader xlsReader =
           new OleDbCommand("Select * from [" +spreadSheetName + "]", xlsFileConnection).
           ExecuteReader();

In the spreadSheetName parameter i keep my file name.

The connection string for xlsFileConnection was set as

"Provider=Microsoft.Jet.OLEDB.4.0; 
 Data Source='<directory path>';
 Extended Properties='text; HDR=No; FMT=Delimited'"

When i start to execute while (xlsReader.Read()) i take a row #2 but not #1 from data source.

The first suggestion was that HDR parameter has invalid value but it seems it's ok.

Is your file an Excel spreadsheet or a CSV file? Does it actually have a header row? Why are you using Microsoft.Jet.OLEDB.4.0 rather than Microsoft.ACE.OLEDB.12.0? — Ciarán, Sep 03 '15 at 09:37

score 0 · Answer 1 · answered Sep 01 '15 at 09:19

There are better and easier ways to reading xlsx files, if I were you I would grab closedXML from nuget and this code to read your excel file into a data table

public void ProcessExcel(string fileName)
    {
        _dt = ImportSheet(fileName);
        dgContacts.ItemsSource = _dt.DefaultView;
    }

public static DataTable ImportSheet(string fileName)
    {
        var datatable = new DataTable();
        var workbook = new XLWorkbook(fileName);
        var xlWorksheet = workbook.Worksheet(1);
        var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());

        var col = range.ColumnCount();
        var row = range.RowCount();

        datatable.Clear();
        for (var i = 1; i <= col; i++)
        {
            var column = xlWorksheet.Cell(1, i);
            datatable.Columns.Add(column.Value.ToString());
        }

        var firstHeadRow = 0;
        foreach (var item in range.Rows())
        {
            if (firstHeadRow != 0)
            {
                var array = new object[col];
                for (var y = 1; y <= col; y++)
                {
                    array[y - 1] = item.Cell(y).Value;
                }

                datatable.Rows.Add(array);
            }
            firstHeadRow++;
        }
        return datatable;
    }

The grab the data out of your datatable as you need.

This is live and working code, so you just need to copy and paste

The OP asked about OleDb and not ClosedXML so while interesting this doesn't really answer the question. — Ciarán, Sep 03 '15 at 09:38
fully aware of that, but this is a much cleaner execution of getting the data and an alternative method of how to get the data in without having to use the jet engine — Simon Price, Sep 03 '15 at 09:47

OleDbDataReader skips first record

1 Answers1