Receiving user-submitted data as tab-delimited text

What's related > How to create a questionnaire in FrontPage 2000

As of now, you also use the mailform script to receive the data that the user submits via a form as an email that along with the normal paragraphs, contains tab-delimited text that you can then copy and paste into a spreadsheet or database application. 

You can further specify to receive only tab-delimited data, without the normal message body paragraphs. If you store your questionnaire messages in a separate folder in Microsoft Outlook, you can export all the messages' data to a single file that can be imported in your spreadsheet or database in one operation.

How to receive user-submitted data as paragraphs and tab-delimited text

You may be using the mailform script to receive feedback messages from visitors to your site but you might also be using it to collect other data, such as requests to join a mailing list. Receiving the form data as a tab-delimited text means you can easily paste it into a spreadsheet or database package.

Another use of the tab-delimited text facility is expected to be the collection of questionnaire-style information. There is a separate document explaining how to create an example questionnaire form.

Here's how to configure an existing form to include the user-submitted data as tab-delimited at the end of the email you receive:

  1. Open your form page in FrontPage and right-click anywhere within the form and select 'Form Properties'
  2. Click 'Advanced' and remove any 'tab_format' entries already there
  3. Click 'Add' and enter 'tab_format' as the Name and 'yes' as the Value
  4. Click 'OK' to close the 'Name/Value Pair' window
  5. Click 'OK' to close the 'Advanced Form Properties' window
  6. Click 'OK' to close the 'Form Properties' window
  7. Save the page and publish it to the Web server

Now when you receive an email from the form, there will be an addition line that contains each of the form values separated by a tab character. This is known as delimited text and when you come to paste the text into a spreadsheet or database, the application will know to move to a new column every time it encounters a tab.

Here's how to paste the text into Microsoft Excel 97 using Outlook as the email client:

  1. When the email arrives in, open the message and select the entire line of text below 'The following is the email data as tab-separated text:'

    Screenshot showing an example email message with the user data as both paragraphs and a single line of tab-delimited text

    Text description
    Screenshot showing an example email message with the user data as both paragraphs and a single line of tab-delimited text
  2. Select 'Edit', 'Copy'
  3. Open Excel and the spreadsheet in which you want to paste the text
  4. Right-click in the first cell of the first free line and select 'Paste'
  5. The data should now flow into the spreadsheet columns:

    Screenshot of the tab-delimited text pasted into Excel columns

    Text description
    Screenshot of the tab-delimited text pasted into Excel columns

The tips section of 'How to create a questionnaire in FrontPage 2000' explains how to check the integrity of the data.

Here's how to paste the text into Microsoft Access 97 using Outlook as the email client:

  1. When the email arrives in, open the message and select the entire line of text below 'The following is the email data as tab-separated text:'

    Screenshot showing an example email message with the user data as both paragraphs and a single line of tab-delimited text

    Text description
    Screenshot showing an example email message with the user data as both paragraphs and a single line of tab-delimited text
  2. Select 'Edit', 'Copy'
  3. Open Access and the database table in which you want to paste the text
  4. Right-click the arrow tab beside the first cell of the first free line and select 'Paste'
  5. The data should now flow into the database table:

    Screenshot of the tab-delimited text pasted into Access table fields

    Text description
    Screenshot of the tab-delimited text pasted into Access table fields

The Tips section of 'How to create a questionnaire in FrontPage 2000' explains how to check the integrity of the data.

How to receive the user-submitted data as only tab-delimited text

Normally, when you receive data back from the Web server in an email, the message will contain the name and value of each form element in paragraph format. The information above describes how to additionally have the values included as tab-delimited text at the end of the email message. You can alternatively opt to receive only the tab-delimited text.

The benefit of receiving only the tab-delimited text is that if you store all the emails that you receive from the form in an Outlook folder, you can then export all the individual message into just the one file. This file can then be imported into a spreadsheet or database in one operation. You will likely find that tab-delimited-only is most applicable when you are conducting an on-line questionnaire over a set time or if you only want to update a spreadsheet or database on regular occasions.    

NOTE: You will need have the additional Outlook message converters installed to be able to export to a file. These can be found on the Office 97 CD via 'Explore the Office 97 ValuPack', 'Microsoft Outlook import and export converters'. LSE web editors should contact their cluster support team about getting the converters installed.

Here's how to configure an existing form to include the user-submitted data as only tab-delimited text:

  1. Open your form page in FrontPage and right-click anywhere within the form and select 'Form Properties'
  2. Click 'Advanced' and remove any 'tab_format' entries already there
  3. Click 'Add' and enter 'tab_format_only' as the Name and 'yes' as the Value
  4. Click 'OK' to close the 'Name/Value Pair' window
  5. Click 'OK' to close the 'Advanced Form Properties' window
  6. Click 'OK' to close the 'Form Properties' window
  7. Save the page and publish it to the Web server

Here's how to export multiple Outlook email messages into one text file:

  1. Create a sub-folder in Outlook by right-clicking your Inbox and selecting 'Create Subfolder'
  2. Name the folder as required, e.g. 'web-questionnaire'
  3. Most likely you won't want a shortcut on your Outlook Bar, so select 'No' to this choice
  4. When you receive questionnaire message, simply drag them into the new sub-folder
  5. When you want to export the messages, select the sub-folder and then select 'File', 'Import and Export'
  6. Select 'Export to a file' and click 'Next'
  7. Your sub-folder should already be high-lighted so click 'Next' again
  8. Select 'Tab Separated Values (Windows)' and click 'Next'
  9. Click 'Browse' and browse to the location where you want to export the file and enter a name for the new text file
  10. Click 'Next' and click the 'Map Custom Fields' button. If the 'Map Custom Fields' option is not click-able, check the check box beside 'Export "Email Messages"...'.
  11. In the 'Map Custom Fields' window, click the 'Clear Map' button and drag only the 'body' value across to the right-hand 'To' area.
  12. Click 'OK' and then 'Finish'. Outlook will then export all the folder messages into a single text file.

Here's how to import the text into Microsoft Excel 97:

  1. Open Excel and select 'File', 'Open'
  2. Change 'Files of type' to 'Text Files' and locate file you exported from Outlook
  3. Click 'Open' and the Text Import Wizard will start
  4. Set the 'Original data type' as 'Delimited' and 'Start import at row' as '2' to avoid the line which just contains the word 'Body'
  5. Click 'Next'
  6. Set the 'Delimiters' values to 'Tab' and 'Other' and enter the 'other' value as a double-quotation mark - ". This is necessary because Outlook quotes the email body when it exports.
  7. Set the 'Text Qualifier' as '{none}'
  8. You should now see your data in separate columns. Click 'Next'
  9. The first column should be blank and can be deleted so select 'Do not import column (Skip)' under 'Column data format'
  10. Change the other column data types if required and click 'Finish'.

Your data will now be inserted into a spreadsheet. The only task remaining is to delete the blank rows.

Here's how to import the text into Microsoft Access 97:

The cleanest way of importing all the user data into Access is to first import the data into Excel and then into Access.

  1. Follow the above steps for importing the file into Excel and save the spreadsheet workbook as an Excel file (.xls)
  2. Open Access and create a new, blank database. Don't bother creating any tables
  3. Select 'File', 'Get External Data', 'Import'
  4. Change 'Files of type' to 'Microsoft Excel' and locate the Excel file
  5. Click 'Import' and the Import Text Wizard will start
  6. Click 'Finish'

The data will have been imported into a new table.

^ Back to top

LSE