CODEWITHSUNDEEP
c#.netexcelodbc
AbyPhantom
AbyPhantom

Reputation: 111

ODBC Connection to Excel error

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:

additional_info

Also the con.ToString() gives the following:

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

Upvotes: 11

Views: 4377

Answers (3)

Mick
Mick

Reputation: 6864

Make sure you have downloaded and installed the Microsoft Access Database Engine 2010 Redistributable....

And change your connection string to...

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\POS\odabrane_skole novo_mod.xlsx;Extended Properties="Excel 12.0;HDR=YES; IMEX=1;";

Upvotes: 1

Poiter
Poiter

Reputation: 481

I Looks like your application is a x86 (32bit) application and you are looking at the 64 bit ODBC driver. Check if the 32bit ODBC driver is installed....

Upvotes: 1

Chihwei Chang
Chihwei Chang

Reputation: 11

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();

Upvotes: 1

Related Questions