Skip Navigation U.S. Department of Health and Human Services www.hhs.gov
Agency for Healthcare Research Quality www.ahrq.gov
Archive print banner
Administrator's Guide

Public Health Emergency Preparedness

This resource was part of AHRQ's Public Health Emergency Preparedness program, which was discontinued on June 30, 2011, in a realignment of Federal efforts.

This information is for reference purposes only. It was current when produced and may now be outdated. Archive material is no longer maintained, and some links may not work. Persons with disabilities having difficulty accessing this information should contact us at: https://info.ahrq.gov. Let us know the nature of the problem, the Web address of what you want, and your contact information.

Please go to www.ahrq.gov for current information.

2.3.2. Downloading and Importing Data into MS Access

As described in the previous section, the large volume of data will not import into one Access file from one .csv data file. For that reason, as with MS Excel, the data is divided into two .csv files. To use MS Access for data analysis, simply select "Download Data Part 1," and save the .csv file to your hard drive. This file will contain data for the unique ID number, all the demographic questions, and questionnaire questions 1 through 20. Next, select "Download Data Part 2," and save this .csv file to your hard drive. This file will contain data including the unique ID number and questions 21 through 43.

A screen shot shows the Survey Response Summary Report screen. The screen displays the Site Menu on the far left with the 4th and 5th menu options circled; the first one is 'Download Data Part 1,' which contains demographics and survey questions 1-20, and the second option is 'Download Data Part 2' which contains survey questions 21-43 and survey ID.

These .csv data files can be easily be imported into MS Access for analysis by using the MS Access Wizard function. The following sections provide instructions for importing the .csv data from these .csv data files.

Getting Ready. The .csv data files downloaded above and saved to your hard disk must be altered slightly before importing into MS Access for analysis. Before importing these data files, open each file in Notepad and remove all quotes surrounding the data. This can be done by selecting "Edit" and "Replace" from the menu. Search for a quote in the "Find What" text box, and "Replace With" nothing, as seen below. Select the "Replace All" button. Then, resave the files without the quotes and close the file.

A screen shot shows a fictitious .csv data file named 'SubmittedSurveyData.txt' opened in Notepad. The 'Replace' menu option has been opened, showing how to remove all quotes surrounding the data. A quotation mark is entered into the 'Find What' text box, and the 'Replace With' text box is left blank.

Next, open the MS Access program. Select "File" and then "New." The program will then prompt you to specify the type of new database you would like to create. Choose "Blank Database," and name and save this new database as desired using the standard Windows dialog prompt.

Starting the Wizard. In order to start the importing wizard, select "File," then "Get External Data," and finally "Import" from the menu. The standard Windows open dialog window will prompt you for the file to import. 

You should first narrow down your file selection view by choosing "Text Files" from the "Files of Type:" field at the bottom of the screen. This will narrow down your search to show you the appropriate data files in each directory. When you find the first data file to import, select it and click the "Import" button.  

A screen shot shows the Import Wizard opening screen. The 'Files of type' option on the bottom of the screen has the option 'Text files (*,.txt,.*.csv,*.tab,*.asc)' selected. A fictitious file in the SPSS folder named SubmittedSurveyData.csv has been selected and is highlighted under the Look in: text box to show that the file is in the SPSS folder.

When you click the "Import" button, you will begin the Wizard.

Import Text Wizard—Step 1. Once the Import Text Wizard has started, you will see the following window. Make sure the "Delimited" box is selected and click the "Next" button.

A screen shot shows Step 1 of the Import Text Wizard.  A text box is displayed showing fictitious sample data from the file that was selected. The user is to select the Delimited box, which is the first radio button on the top and then select the 'Next' button on the bottom right. The other possible option to select is 'Fixed Width.'

Import Text Wizard—Step 2. Then, you will be prompted to define the file format in the following window. Make sure that "Comma" is selected as the delimiter that separates your fields, the "First Row Contains Field Names" is selected (or contains a check mark), and the "Text Qualifier" is set to {none}. 

At this point, you should take a preliminary look at the data fields shown. If the data contains faulty characters, is not lining up appropriately, or is otherwise showing poorly, your data file may have been corrupted. If you suspect that your data file may be corrupted, resave the .csv file from the application to your hard drive by repeating the data download steps above. If the data looks appropriate, then click the "Next" button.

A screen shot shows Step 2 of the Import Text Wizard. The screen displays the question 'What delimiter separates your fields? Select the appropriate delimiter and see how your text is affected in the preview below.'  The 'Comma' delimiter option is selected, which is the middle option in a row of radio buttons. Other options include 'Tab,' 'Semicolon,' 'Space,' and 'Other.' Below the radio buttons is an option to select 'First Row Contains Field Names,' which has been checked. The 'Text Qualifier' pull-down menu to the right of the previous option is set to {none}. Four rows of fictitious data fields that are being imported are displayed in a text box underneath. Attributes shown are 'SurveyID,' 'BeginTime,' and 'EndTime.'  A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

Import Text Wizard—Step 3. The Wizard will now prompt you to specify a table into which the text file data should be imported within this database. Select "In a New Table" which will allow MS Access to create a new table within the database with the imported data. Select the "Next" button.

A screen shot shows Step 3 of the Import Text Wizard. The first sentence on the screen reads, 'You can store your data in a new table or in an existing one.' The question is then asked, 'Where would you like to store your data?' Two options are shown. The first one is 'In a New Table', and the other option is 'In an Existing Table'. The second option has a pull-down text box so you can find the table file. Four rows of fictitious data fields that are being imported are displayed in a text box underneath. Attributes shown are 'SurveyID,' 'BeginTime,' and 'EndTime.'  A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

Import Text Wizard—Step 4. Next, the data must be formatted for file importing. Note in the window below that the data columns are selectable. Select the "BeginTime" column, and set the "DataType:" to "Text." Do the same for the "EndTime" column. These columns default to the date/time data format, but MS Access will not import these dates appropriately. By selecting text, the fields will be imported without errors. Select the "Next" button.

A screen shot shows Step 4 of the Import Text Wizard. The first sentence reads, 'You can specify information about each of the fields you are importing. Select fields in the area below. You can then modify field information in the 'Field Options' area.' A fictitious field name of 'EndTime' is shown in the Field Name text box. The 'Data Type' option to the right displays the selection of Text from a pull-down menu. Beneath the Field name text box is an option for Indexed, which has a selection of 'No'.  There is also a field and blank checkbox which reads, 'Do not import field (Skip)'. Four rows of fictitious data fields that are being imported are displayed in a text box underneath. Attributes shown are 'SurveyID,' 'BeginTime,' and 'EndTime.'  A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed. The EndTime fictitious data has been highlighted.

Import Text Wizard—Step 5. MS Access can add a primary key to your data upon insert. This is not necessary at this point and can also be added in later, after the data has been imported. At this time, select "No primary key." Select the "Next" button.

 A screen shot shows Step 5 of the Import Text Wizard. This step discusses the option of adding a primary key to the data. The first paragraph reads, 'Microsoft Access recommends that you define a primary key for your new table. A primary key is used to unique identify each record in your table. It allows you to retrieve data more quickly.' Three radio buttons with the options 'Let Access add primary key,' 'Choose my own primary key,' and 'No primary key' are displayed underneath.  The last option is shown as selected. To the left of this paragraph is a picture showing a housekey on top of a chart with a column of numbers--1, 2, 3, 2, 4--underneath and  'pretend' fields with X's in them to the right of the numbers. Four rows of fictitious data fields that are being imported are displayed in a text box underneath. Attributes shown are 'SurveyID,' 'BeginTime,' and 'EndTime.'  A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

Import Text Wizard—Step 6. At this time, MS Access will prompt you for a table name. Type in the desired table name, and click the "Finish" button. You should receive a prompt stating that the Wizard has finished importing the file to the indicated table. If you receive an error at this point, MS Access will create an error table and list the importing errors in that location. 

A screen shot shows Step 6 of the Import Text Wizard. It displays a prompt screen for a table name.  The first sentence reads, 'That's all the information the wizard needs to import your data.' On the left side is a picture of a checkered racing flag.  There is an Import to Table box with the fictitious table name of SubmittedSurveyData typed in it.  Below that are two options with blank boxes. The first option reads, 'I would like a wizard to analyze my table after importing the data.' The second option reads, 'Display Help after the wizard is finished.'

Importing the Second Data File. Repeat all of the above steps to import the second data file into MS Access using the Import Text Wizard. The second data file does not contain a BeginTime or EndTime data field, so you do not need to alter any column types within step 4.

Viewing your Table. You will see the newly created table within the "Table" tab of the Access database display window below. To view the data, double select the desired table.

A screen shot displays a fictitious table titled SubmittedSurveyData in MS Access. The top of the screen reads 'SubmittedSurveyDB: Database (Access 2000 file format).'  Underneath is a menu bar, with the options of 'Open,' 'Design,' 'New,' an 'X' for Delete, and other Display options. A left sidebar is titled 'Objects'; benneath are the selections 'Tables,' 'Queries,' 'Forms,' 'Reports,' 'Pages,' 'Macros,' 'Modules,' then 'Groups, Favorites.' The center text box has the following selections: 'Create table in Design view,' 'Create table using wizard,' 'Create table by entering data,' and the fictitious table file SubmittedSurveyData, which is highlighted.

Once you have double clicked on the Access table, you will see the following view of the table and its data. Simply use the scroll bars to view the data in its entirety. 

A screen shot displays a fictitious Access table called SubmittedSurveyData. Field names in the table are 'SurveyID,' 'BeginTime,' 'EndTime,' and 'D01 through D07.' There are four rows of fictitious data with some of the fields filled out. A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

There are several ways to alter the table's view to better view the data. MS Access will open the table with the default column width shown above. In order to alter the column width, simply place the mouse in the title row, highlighted in gray, over the column line to be moved. When the mouse changes to a cross hair, you can then hold the left mouse button down and adjust the column width.

Since there are too many columns to show in one view, you can highlight columns to "freeze" or continue to show as the scroll bar is used to move through the data. To do this, simply highlight the column to freeze by clicking on its name in the title bar, as shown below. Then, right click to show the menu options, and select "Freeze Columns." You will notice that a dark black line will be drawn to the right of this column to show that it is frozen. To unfreeze the column, simply right select the title bar again and select "Unfreeze All Columns," also shown below.

A screen shot displays a fictitious MS Access data table called SubmittedSurveyData. This screen shot shows how to keep a column on the screen while scrolling to the right by 'freezing' it. The desired column is right-clicked in the title bar and a text box with menu options that open up are displayed on top of the table. One of the options is 'Freeze Columns.' Field names in the table are 'SurveyID,' 'BeginTime,' 'EndTime,' and 'D01 through D07.' There are four rows of fictitious data with some of the fields filled out. A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

If you freeze the SurveyID column, as shown above, then you can see how the SurveyID will remain visible as you scroll through the data, as seen below.

A screen shot displays the fictitious MS Access data table called SubmittedSurveyData, with the SurveyID column frozen so that it remains visible if you scroll right in the table to see more fields. Data is shown in four of the rows, and the table is scrolled over to the attributes Q02-Q05B. A horizontal scroll bar at the bottom of the text box indicates that there are many other attributes not being displayed.

Return to Contents
Proceed to Next Section

The information on this page is archived and provided for reference purposes only.

 

AHRQ Advancing Excellence in Health Care