Database from web form emailed to me

  geewhizz06 20:06 23 Jul 2006
Locked

I have a web form with data that is emailed to me.
I want to set this up in a database (free) or Excel preferably.
How can I do it easily? If I paste it into Excel it just fills the first column and I want it to go across as cells in one row as I have 500 of these.

Data looks like this and I have compleeted and empty firelds but they all are completed and emailed to me -

Title: Mr.

Family Name: Mason

Initials: G

First Name: George

Name on Badge: George Mason

Mailing Address: Office

Institute: John Radcliffe Hospital

Dept: Learning & Development

No:

Suite Apt House Name:

Address 1: Level 3

Address 2:

Town City: Oxford

Country : UK

Postal Zip Code: OX4 1LX

Email: geemm@ukhq.co.uk

IMSS WMS Membership: IMSS Member




--

  VoG II 23:01 23 Jul 2006

Does a record always end in --

If so it is possible to write a macro to convert the initial Excel sheet.

  ade.h 23:06 23 Jul 2006

Paste the fields into a text file, arranging them with each field seperated by commas or tabs and one line of text per message. Save it as .csv file, then import it into Excel. Bob's your mum's brother.

  ade.h 23:07 23 Jul 2006

Oh, and don't forget to cut out the field types each time and just keep the data itself, so that your example would read:

Mr,Mason,G,George,George Mason,Office, and so on.

  Main Access 23:18 23 Jul 2006

For 500 address's it is porbably easiest to use cut and paste.
You could always alter the website to submit the form as an xml document then there would be no problem importing this into a database.

PS Excel is not a database.

  Main Access 23:20 23 Jul 2006

Would work until someone puts a , in the answers to the form

  VoG II 23:28 23 Jul 2006

Excel can be used as a database although admittedly it is not a relational database like Access.

I have a macro waiting to be adapted to solve exactly this problem. It just needs something unique to define the end of a record.

  geewhizz06 08:34 24 Jul 2006

I suppose I could put a hidden field in the form that is always submitted at the end of the form, say xxx in white text in a white box....then a macro (if I could write one) would work!

  VoG II 09:09 24 Jul 2006

In your first post you indicated that -- is at the end of a record. Did you mean that or is IMSS WMS Membership: the end of a record?

  geewhizz06 10:06 24 Jul 2006

-- is from my AVG scan and I get these on every email but someone else should be receiving these emails so it is best that it is something else and in the form.
IMSS WMS Membership is a drop down choice and I can make it the last field of the record.
I could also strip out and have no field names sent back to me but if anyone needs to pick this up and understand it it is much easier with these.

  VoG II 10:17 24 Jul 2006

With IMSS WMS Membership as an end of record marker try this:

Sub fmt()
Dim InSht As Worksheet, Outsht As Worksheet
Dim LastRow, iRow, jRow, jCol
Application.ScreenUpdating = False
Set InSht = ActiveSheet
LastRow = InSht.Cells(Rows.Count, 1).End(xlUp).Row
Sheets.Add after:=InSht
Set Outsht = ActiveSheet
jRow = 1
jCol = 2
For iRow = 1 To LastRow
Application.StatusBar = Int(100 * iRow / LastRow) & "% done..."
If InSht.Cells(iRow, 1).Value <> "" Then
Outsht.Cells(jRow, jCol).Value = Trim(InSht.Cells(iRow, 1).Value)
If Left(InSht.Cells(iRow, 1).Value, 4) = "IMSS" Then
jRow = jRow + 1
jCol = 2
Else
jCol = jCol + 1
End If
End If
End If
Next iRow
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub


Open your worksheet and press ALT _ F11 to open the Visual Basic Editor. Insert > Module then paste in the above code. X to close the VBE. On the 'unformatted' sheet Tools > Macro > Macros, Click on fmt and click the Run button.

Please post back if this works!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?