UNIVERSITY OF DENVER
COLLECTING DATA ON THE WEB: FROM AN ONLINE FORM TO MICROSOFT EXCEL

The instructions below illustrate how to use a standard html form and off-the-shelf cgi script to create a simple database on the DU server. You will also learn how to move that data over into Excel for further manipulation. The process will not work as described on other servers. It is assumed that you have already created your html form and assigned field names to each of your components on the form. (Remember: it is best to use all lowercase characters and eliminate all spaces when naming fields.)

LINKING YOUR HTML FORM TO THE .TXT FILE

After creating the html form, you must create an action statement which will link your form to the text file (which you will create in the next step.)

Go to the html coding of your html form and type the following statement immediately following the <form method="POST"> tag.

Type it as follows:

<form method=POST
ACTION=http://www.du.edu/cgi-bin/cgifile/path_of_file/your_file.txt>

CREATING THE .TXT FILE

The .txt file contains the field names representing the data you will download into Excel. Each field within your html and .txt files will correspond to a separate column in Excel after the download.

To create the .txt file:

Open a text editor such as Notepad to create the file.

Enter each field name inside square brackets [ ]. The field name should be entered exactly as it appears in the original html form.

Separate each field name with quotation marks. Each comma entered within a text box by the end user on the published Web form is interpreted as a delimiter by Excel. This means that a new column will automatically be created in your Excel file each time a comma is input in any text box within the form. To resolve this problem, quotation marks must be entered around each field name as illustrated below.

Each field name should be separated by a tab.

After entering all field names, press the Enter key at the end of the line. This will ensure that when the file is downloaded into Excel, a new row will be created for each new record.

Below is an example of a .txt file:

[firstname] [lastname] [phone] [department]

Save the file with a .txt file extension.

PUBLISHING THE HTML FORM AND THE .TXT FILE

After creating the html form and the .txt file you must publish both files to the Agora server.

CREATING THE INCOMING.TXT FILE

Open a Secure Shell (SSH) terminal window to your agora account.

Navigate to the folder containing your html form and .txt file. At the Unix prompt, type the following:

cd ~http/htdocs/your_directory_name

Create the incoming.txt file using a custom script available on the DU system. At the UNIX prompt type the following:

save-incoming

Then press Enter on your keyboard. If nothing happened but a return to the Unix prompt, the process was a success. If you get an error message asking you to enter the name of the directory, use the full path as follows: /local/lib/httpd/htdocs/your_directory_name

Check that the file was created by viewing all files in the directory. At the Unix prompt type:

ls -l

Then press Enter. You should see incoming.txt in the list. Example:

-rw------- 1 thoyer webmaster 0 Aug 14 11:21 incoming.txt

TESTING THE FORM

In the browser, submit your form at least once to create test data and ensure the form works as expected. Remember, you must have the incoming.txt file and the action .txt file in place BEFORE you test. And both .txt files must reside in the same directory.

SAVING AND DOWNLOADING DATA

Open a SSH connection to agora. Navigate to the directory where your form and .txt files reside. At the Unix prompt type:

save-incoming

List all files in the directory and check that the data file was saved.

At the Unix prompt type:

ls

In the list shown, you should see a file with todays date, beginning with the year. Example:

2002-08-23_incoming.txt

The doubtful or curious may want to view the data at this point. Type the following at the Unix prompt:

more name_of_incoming.txt_file

Example: more 2002-08-23_incoming.txt

Download the saved data file to your desktop.

OPENING DATA IN EXCEL

In Excel, select Open on the toolbar and navigate to the directory on your desktop where you downloaded the dated_incoming.txt file. You wont see your file in the list because it isnt an Excel file type. To make the file visible, change Files of Type at the bottom of the dialog box to All Files or Text Files. Then select the file from the list, and press the Open button. Rather than opening the file, Excel should automatically display the Text Import Wizard dialogue box.

Import, Step 1: Select Delimited as the type of data, and leave the other options untouched. Then press Next >.

Import, Step 2: Set the following, if not set as default automatically:

Delimiters = Tab
Text Qualifier =

Ignore other options. Press Next>

Import, Step 3: Set the following, if not set as default automatically:

Column data format = General

Ignore other options. Press Finish.

Excel should have returned to the worksheet with data aligned in columns. Add your own column headings to make sense of the data for tabulating and sorting.

When its time to save the file, go to File on the menu bar and choose Save As and change the file type to Excel.

BATCHING YOUR DATA

Most of the time, youll need to review your data on a periodic basis, which requires downloading more than once. To prevent duplicating data each time you download, programmer Bob Stocker wrote the save-incoming script to remove all data in the original incoming.txt file when it creates the dated file. This allows you to rerun the save-incoming script and retrieve only new submissions since the last time you saved data.

Each time you need to retrieve your data, follow the same steps as outlined in Saving and Downloading Data. Then, you have two choices when it comes time to import the data into Excel. You can either:

1) Create a new Excel file following the instructions above, in which case your data will reside in two files;

2) Import the new data into the previously saved Excel worksheet, combining all data.

COMBINING BATCHED DATA IN EXCEL

Instructions given here are for Microsoft Excel XP and 2000 versions.

Open your existing .xls data file (the one previously saved in Excel format).

Identify where the new data should import into the worksheet as follows. Click in the first empty cell in the first blank row of the worksheet.

From the main menu, pull down the options under Data and select Import External Data, then Import Data or Import Text Data depending on your software version.

Navigate to and select the new .txt file you downloaded.

When you press Open, you should see the now familiar Text Import Wizard. Follow the Import Steps 1 -3 above under Opening Data in Excel.

After pressing Finish, Excel asks where you want the data. Select Existing Worksheet as the location and press OK. You should see your new data in rows below the previously saved data. If the new data joined the file somewhere else, exit without saving and start the import procedure again from the beginning. Do not save the .xls file if the columns did not align properly.