Intel Coffee Lake 8th-gen Core processors release date rumours
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 -
Family Name: Mason
First Name: George
Name on Badge: George Mason
Mailing Address: Office
Institute: John Radcliffe Hospital
Dept: Learning & Development
Suite Apt House Name:
Address 1: Level 3
Town City: Oxford
Country : UK
Postal Zip Code: OX4 1LX
Email: [email protected]
IMSS WMS Membership: IMSS Member
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.
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.
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.
Would work until someone puts a , in the answers to the form
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.
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!
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?
-- 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.
With IMSS WMS Membership as an end of record marker try this:
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
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
jCol = jCol + 1
Application.StatusBar = False
Application.ScreenUpdating = True
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.