1 GIS Instructions: Import Excel Table into MS Access and Export as DBF v1, Feb 2010 (with special thanks to Mr. John Whitford of New Jersey City University) Discussion – With the introduction of MS Office 2007, Microsoft removed the capability of MS Excel to “Save As” DBF. These instructions provide a workaround with MS Access. Step 1 - Create Input Address Table in Excel - Open MS Excel, create column headers, and input the following information. Don’t forget to format the zipcode column as Text, or it may not geocode properly - Scroll to your folder, File > Save As > Input_Address_Table. Close Excel. Step 2 - Import Input Address Table into MS Access Discussion – You must create a new, blank database before you import Excel data - From your computer’s main screen, select Start > All programs >Microsoft Office > Microsoft Access. The following blank Access screen should appear: - Select File> New. Select Blank database:2- A File New Database window appears. Use the dropdown menu button to browse to your GIS folder, save the new database as Input_Address_table.mdb, click Create. The new Input_Address_Table now appears in MS Access. - Select File> Get External Data > Import.3 An Import window will appear. Change Files of Type to Microsoft Excel, then dbl-click on input_address_table.xls: The Import spreadsheet wizard appears; if necessary, select Show Worksheets; press Next: Double-click4The next window appears, asking if it can use your column headings as field names (aka “column headings”); checkmark the box and press Next: Another window appears asking where you want to store data; select new table; press Next:5Another window appears asking if you want to modify field information; press Next: Another window appears asking about Primary Keys. A primary key is not necessary here, but it is OK if you let Access add a Primary Key field (ID) for you; press Next:6In the final window that appears in this wizard, re-name the Table that you will import the data into from Sheet1 to Input_Address_Table; press Finish. You should see a window confirming that the Import operation was successful; press OK: The Input_Address_Table now appears in the main MS Access window:7Step 3 – Export the Input_Address_Table as a DBF file - From the main Access window, select File > Export: An “Export Table..” window appears (below). Scroll to your GIS folder. Change the Save as type to dBaseIV; the new file name (Input_Address_Table) will appear; press Export: NOTE: MS Access is still DOS 8.3 compliant; it may only save the FIRST 8 Characters of the file name. In my case, Input_Address_Table appeared in my GIS folder as INPUT_AD.DBF:8Step 4 – Confirm successful export in DBF format - From your GIS folder, rt-click on Input_Ad.DBF > Open with > MS Excel. When the DBF opens, the columns are a HUGE (below). Select the uppermost LH small box: All columns should become highlighted: - Select Format > Columns > Width; type 25; press OK. The table should appear normal-looking (below); confirm that all values and formats are OK: The DBF table is now ready to be used in your GIS application. If you have trouble or further questions, both MS Access Help and the Microsoft website are very good