Connecting to an Excel file using the ADO DSC

by

On our site we have a few articles about how to connect to SQL databases using ODBC connections, but have you ever wanted to use ODBC to read an XLS file? Not Everyone has access to SQL so using Excel to store data for perhaps a CAPI survey is often a viable option. In this article we will show you how to build the connection string required to connect.Just so that it is clear, this article only talks about the connection string to connect to an excel file. If you would like to see the other code that would be required as well then you can read these two articles ( Post 1 & Post 2 ).

Ok so let’s assume you have you main code and you are now ready to connect to the excel file. First off where to we place the connection string. Well , your code should have something like this in it.

Dim oConnection, sPath
Set oConnection = CreateObject("ADODB.Connection")
sPath = "[Location of Files]"
oConnection.ConnectionString  = "[Connection String Goes Here]"

Clearly we have to place something in the connection string, but what does an ADO connection string to excel look like, well here is an example.

oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;" + _
   "Data Source=mrADODsc;MR Init MDSC=mrADODsc;" + _
   "Initial Catalog=" + sPath + "XLS2003.adoinfo"

Hopefully from this connection string you can see we need two things. Fist off the Path to the location of the files we are going to use. So in our case that will be

sPath = "d:DATASDLibraryReadXLS"

And in this folder we need out XLS file and also a file called “XLS2003” The content of this file should be as follows.


    
        DSN=Excel Files;
        DBQ=MySample.xls;
        DriverId=790;
        MaxBufferSize=2048;
        PageTimeout=5
    
    
        Sheet1$
   

As you can see this is an XML file and it holds two things the connection to the XL file and also the name of the sheet that we want to read. You will notice that we have the name of the sheet as “Sheet1$” Our sheet name is just Sheet1 , but to get this connection string to work we must add the $ onto the end. The use of the $ does depend on what version of Excel you have so if with the $ does not work , try without.

Oh and one last thing don’t forget to make sure that the first line in your excel file has the field names in it , and make sure that the names are sensible, ( avoid spaces if possible ). So if you get all that working then you should be able to pull out the information in the xls file using the examples provided in the other articles.

2 thoughts on “Connecting to an Excel file using the ADO DSC”

  1. This is cool, but whenever I tried to use the ADODSC to connect to excel sheets a weird issue is preventing me from using the full functionality. The variable types seem to be controlled by a factor which I couldn’t figure.

    Eg. In an Excel sheet which contains numbers only some of the variables in the metadata are decided as texts and other as numeric once interpreted by the data model… Played around with different properties in excel (incl. formatting) and it does not make a difference. Do you have any idea what’s the rule on which the actual variable type depends?

    Many thanks,
    Ivelin

  2. Hi Ivelin, we have checked in the DDL and there is nothing about field type so we suspect that this is driven by the microsoft driver its self. The way we have worked with this before is to have in the headings a letter that represents type. For example if we had a field holding names it would be called tName ( t for text ) and then when we read the field in we can work out the type by checking the first letter of each field.

    please add further comments or send us an email at SmarterDimensions@live.com.au if you have any further issues.

Leave a Comment