Importing data into Microsoft Access and cleaning the postcodes.
The following section describes how you can import your own data into the database created in the previous section “Creating the OAC classification database“. Furthermore, as discussed previously, these steps will also be helpful when you import a Postcode to Output Area lookup file.
Using the database you downloaded in the previous step the following instructions show you how to import the data you want to analyse from an Excel file.
1) Importing your data.
From the “File” menu, click “Get External Data”, then click the submenu “Import…”. On the dialog box click the “Files of type:” dropdown menu and select “Microsoft Excel (*.xls)”. Using the “Look in” drop down menu, browse for your data, i.e. the Excel file onto which you wish to append OAC. Once you have selected the Excel file, click import. The “Import Spreadsheet Wizard” will open. In the sheet selection box at the top of the dialog box, all the Worksheets within the Excel Workbook you selected are listed. Click on the Worksheet where your data is stored. The first few lines of the data file should now be visible in the bottom box. Click “Next >”. If the first line of your data contains a series of names that you would like as column headings in the database table you are creating, tick the box “First Row Contains Column Headings”. Click “Next >”again. Select the option “In a New Table” and click the “Finish” button, then “Ok” on the popup box that opens. The name of your new table will have been created from the file name of the Worksheet you just imported. You should right the new table and click “Rename”. Type in a new name of “User”.
2) Cleaning your postcodes.
Postcodes can come in many different formats. In order to join OAC onto your data a single postcode format is required. The simplest format, and one which we will implement here is to extract all the spaces from the postcode.
In the database window, click the “Queries” tab. In the box to the right, double click the “Create query in Design View” option which opens up the query interface (background) and the Show Table dialog box (foreground). The table you imported in the previous steps should be listed in the Show Table dialog box. Click on your table and then and then click the “Add” button to add your data to the query interface. Then click the “Close” button to close the dialog box. The Query Design window should be visible and will have “Select Query” at the top of it. Right click in the large grey area in the top half of the Query Design window, select the option “Query Type” and then “Update Query” from the sub menu. The query design window will now say “Update Query” at the top of it.
In the drop down box to the right of the word “Field:”, select the column which contains the postcodes you want to update. In the box below this and next to the word “Update to:” copy and paste the following code:
Replace([variable], ” “, “”)
In this code, replace the word variable with the name of the column that your postcodes are located, i.e. the one you have just selected in the “Field:” box. Click the red cross in the top right of the Query Design window and a dialog will ask you to save the query. Click “Yes”, and then a dialog will ask you to give the query a name. Call it “Update”, which you should write in the “Query Name:” box and click “Ok”. In the database window you should now see your query called “Update”. Double click this query to run it and click “Yes” twice to permanently remove the spaces from your postcode variable. If you now click back on “Tables” in the database window and then double click your data table which is in the window on the right you will see that all the spaces are now removed from the postcode variable.
3) Your Postcode to Output Area Lookup file
If you have not already done so, you now need to import your Postcode to Output Area lookup file into the database and repeat the steps above to remove spaces from the postcode on this newly imported table.
Last modified:
