How to extract date from a SWR form and include it in a spreadsheet.

By MartynB

 

This is what you sent me: -

Time Submitted: Wed, 18 Mar 09 10:07:03 +0000
Referer: http://www.foxhillers.com/loca/contact.html
From IP: 81.138.200.68

PHONE =
NAME = Martyn
SURNAME = Bellamy
BUSINESS =
EMAIL = martynb@imsl.biz
COMMENT = No Comment

Step 1 - Copy everything from PHONE down and paste it into Notepad
Step 2 - replace " = " (space=space) with a colon as below
PHONE:
NAME:Martyn
SURNAME:Bellamy
BUSINESS:
EMAIL:martynb@imsl.biz
COMMENT:No Comment

Step 3 - Save file as temp.txt (the Desktop is as good a place as any).
Step 4 - Open Excel
Step 5 - in Excel File Open temp.txt - this kicks the Text Import Wizard into life.
Step 6 - Step 1 of the wizard should have the "Delimited" radio button selected and you'll see the data at the bottom of the dialog. Click Next to move to step 2 of the wizard.
Step 7 - In the Delimiters deselect Tab and type a colon ( onto the field next to Other. Other gets selected automatically when you do this. At the bottom you'll see your data labels separated from your data by a line - the colon has been removed.
Step 8 - Click Finish - you don't need to do the third step of the wizard.
Step 9 - You will now see the field names in column a and the corresponding data in column B. Select all the content and copy it.
Step 10 - Select a blank cell in column a and do a Paste Transpose (Imagine you have 100 fields - it's much more impressive). You now have the start of a data table with field names and a row of data.
Step 11 - remove the original data so that A1 contain the first field name and a2 the data for that field.
Step 12 - Important step - Save AS an Excel workbook.

For all future data repeat steps 1 through 9 - just keep replacing temp.txt on your desktop. Have your database workbook open and instead of copying the field names and the data just copy the data making sure you copy all the fields. Then Paste Transpose onto the next empty row of your database.

Once you've done it a few time the process will take around ten to twenty seconds.

 

Serif Forum link - missing fields in SWR-routed forms.