ODBC Connection to Excel error

Question

I'm having a problem with the ODBC connection that is supposed to connect to an Excel table and do stuff with it. I've already read a lot stuff on the internet about it, but none of the solutions helped me (including stackoverflow).

So basically I am at a point where I'm trying to open a connection to a table.

private static SortedList<string, School> generateSchoolListExcel(string listFilePath)
{
    StringBuilder con = new StringBuilder();

    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Data Source", listFilePath);
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "HDR", "yes");
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Format", "xlsx");
    OdbcConnectionStringBuilder.AppendKeyValuePair(con, "Driver", "{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}");
   //I have tried to specify driver without parentheses {} but it's still the same

    List<School> schoolList = new List<School>();

    using (OdbcConnection excel = new OdbcConnection(con.ToString()))
    {
        excel.Open();
        //doing actuall stuff
    }

        return schoolList;
}

When I call the excel.Open() method, I get OdbcException with message:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", which is odd because I have those specified in the string named con.

It's also worth to mention that in the ODBC Data Source Administrator, I can clearly see that I have those drivers installed and running.

There is also another odd part. When I call the following method I found on stackoverflow it returns me the following list of drivers:

• "Driver da Microsoft para arquivos texto (*.txt; *.csv)"
• "Driver do Microsoft Access (*.mdb)"
• "Driver do Microsoft dBase (*.dbf)"
• "Driver do Microsoft Excel(*.xls)"
• "Driver do Microsoft Paradox (*.db )"
• "Microsoft Access Driver (*.mdb)"
• "Microsoft Access-Treiber (*.mdb)"
• "Microsoft dBase Driver (*.dbf)"
• "Microsoft dBase-Treiber (*.dbf)"
• "Microsoft Excel Driver (*.xls)"
• "Microsoft Excel-Treiber (*.xls)"
• "Microsoft ODBC for Oracle"
• "Microsoft Paradox Driver (*.db )"
• "Microsoft Paradox-Treiber (*.db )"
• "Microsoft Text Driver (.txt;.csv)"
• "Microsoft Text-Treiber (*.txt; *.csv)"
• "SQL Server"
• "SQL Server Native Client 11.0"

None of those have the "*.xlsx" in them, which is the format of a file I'm trying to read.

The method is the following:

public static List<String> GetSystemDriverList()
{
    List<string> names = new List<string>();
    // get system dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBCINST.INI");
            if (reg != null)
            {

                reg = reg.OpenSubKey("ODBC Drivers");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        names.Add(sName);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    return names;
}

It should be noted that when I actually go to the regedit and find those values I clearly see:

"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)".

Am I missing something obvious? Please help me :)

By the way I'm pretty new to this side of .NET so please keep your answers dumbed-down so I could actually understand what is going on. Thank you!

EDIT: A friend pointed out I should give more information, so here is the screenshot of the regedit, ODBC Data Source Administrator and the proof that the ACEODBC.DLL actually exists on my hard drive:

Also the con.ToString() gives the following:

Data Source="G:\POS\odabrane_skole novo_mod.xlsx";Driver="{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"

Answer 1

How about using OleDbConnection, and you have to install Microsoft Access Database Engine 2010 at first.

string path = @"c:\sample.xlsx";
string strCon = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + path + ";Extended Properties='Excel 12.0;'";
OleDbConnection objConn = new OleDbConnection(strCon);

string strCom = " SELECT * FROM [a$] ";
objConn.Open();