Database Connection in ArcCatalog: Connect to an Excel table

Forum to discuss about issues like tutorials, programming and other topics related to GIS.
Post Reply
Admin
Site Admin
Posts: 317
Joined: Tue Sep 25, 2007 12:41 pm

Database Connection in ArcCatalog: Connect to an Excel table

Post by Admin » Tue Dec 29, 2009 10:43 pm

It’s a three-step process:

1. Setting up of the Excel spreadsheet,
2. Creating an Open Database Connectivity (ODBC) data source, and
3. Connecting to the Excel file from ArcCatalog through OLE DB Provider (Object Linking and Embedding Database Provider).

First step: Open your Excel file. Select the rows and columns including the first row which should contain the column headings. Now go to Insert > Name > Define. Type any name for the selected cells. Save the file. Now you can close your Excel file.

For the 2nd part:

Go to your Control Panel by clicking on Start > Settings > Control Panel.

Click on Administrative Tools, then on Data Sources (ODBC). Select the User DSN tab and now click on the Add button. Here you have to select a driver. Select the ‘Microsoft Excel Driver (*.xls)’ and click Finish.

Now in the ‘ODBC Microsoft Excel Setup’ window type any name in the cell asking for ‘Data Source Name:’. Now click on ‘Select Workbook...’ and browse for your Excel file.

Now click on OK and close the windows you opened for the data source connections.

3rd and the final step:

Now start ArcCatalog. On the Catalog tree, you will find ‘Database Connections’ option. Double click to open it. You have two options there: ‘Add OLE DB Connection’ and ‘Add Spatial Database Connection’.

Double click on ‘Add OLE DB Connection’. OLE DB providers communicate and retrieve data from a database.

Now a new window named ‘Data Link Properties’ will open. Under ‘Provider’ tab select ‘Microsoft OLE DB Provider for ODBC Drivers’. Click on ‘Next’.

Now you have to specify the source of your data. Under ‘Use data source name’ select your Excel file from the drop down list. Now click on ‘Test Connection’ button to see if everything is working just fine. Click ‘OK’.

Now you will see a new entry under ‘Database Connections’. By default it’s named ‘OLE DB Connection.odc’. You can rename it to your choice.

Have fun. :)

Post Reply