How to prevent OleDbDataReader.ExecuteReader from auto-appending integers to duplicate column values

I'm trying to import data from an Excel (xls) spreadsheet. I need to throw an error if duplicate values appear in the header row. I'm using an OleDbDataReader to ingest the worksheet data. The problem I'm having is that .Net is appending a unique integer to the end of the duplicate columns so that all of the values are unique.

The header values will be be unknown at runtime. If duplicate header values are found, processing of the file should be stopped.

For example, a spreadsheet with the following headers: Make, Model, Year, Make Will appear as: Make, Model, Y开发者_开发技巧ear, Make1

Here is some code:

           string selectString = "Select * from $Sheet1";
           xlConnection = new OleDbConnection(connectionString);
           OleDbCommand cmd = new OleDbCommand(selectString, xlConnection); 
           OleDbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
                {
                    string columnValue = reader.GetName(fieldIndex);
                }
            }

GetName returns Make, Model, Year, Make1

Is there a way to prevent this behavior?

---

Consider using:

  string selectString = "Select Make, Model, Year from $Sheet1";

instead of:

  string selectString = "Select * from $Sheet1";

---

I had this same requirement and faced the same challenge.

I solved it by modifying the connection string to set HDR=NO. Then selected the first row and looped through the values in each column to check for uniqueness. If I found a match I threw DuplicatNameException to be consistent with DataTable.

Hope this helps.