MS Excel guru's needed fast!

Vegetarian · 4 Nov 2009 at 00:06

I need to convert some contacts - name telephone and email address of this site (and some others) http://www.food.gov.uk/aboutus/contactus/ to an Microsoft excel speadsheet as soon as possible - how do I do it quickly? Is there a "rip from web" option?

Fegruson · 4 Nov 2009 at 00:08

You could copy it all, right-click Paste Special - then choose HTML.

But your not going be able to get it set out like the following columns;

Name - Num - Email - Etc

Vegetarian · 4 Nov 2009 at 00:19

what about just email address's?

whitecrook · 4 Nov 2009 at 00:34

if i copy paste it,, it goes into excel fine. Do you mean in such a way that there is a column for each piece of data?

anyway - I'd do a little bit of scripting..

if you look you can see a pattern which you can use to extract the data, and then generate a csv, which could be put into excel.

e.g.
Bread and flour
Labelling Regulations and composition:
Richard Wood
tel: 020 7276 8154
email: [email protected]

Each 'record' is separated by 2 carriage returns
The first line of each record is field 1
grep each record for a line begining with email (some have 2 emails) and extract the email
grep each record for tel: and extract the tel number
the line immediately before the tel/email lines is the person name
and the remaining lines are description.

gawk should do it and indeed i am looking in to it now.

PMKeates · 4 Nov 2009 at 01:07

Errm, is this what you want?

http://www.elacole.com/private/pmkeates/stuff.xls

whitecrook · 4 Nov 2009 at 02:01

here's a quick output:
sourcefile : http://homepage.ntlworld.com/k.malone97/ocuk/tmp.txt
gawk script :http://homepage.ntlworld.com/k.malone97/ocuk/script
output:http://homepage.ntlworld.com/k.malone97/ocuk/test.csv

First gawk program I ever wrote . could be improved by getting it to put all the stuff in the same column e.g. al emails in a column, etc..

run the script with:

gawk -f script tmp.txt > test.csv

Obviously you need to install gawk. google for gawk win32

PMKeates · 4 Nov 2009 at 21:36

I just wrote this really cheap piece of VBA

Sub Transposer()

Dim lLoop As Long

lLoop = 1

Do Until Cells(lLoop, 1) = ""

If Cells(lLoop, 1).Font.Bold = True Then GoTo 100

If Cells(lLoop, 1).Font.Bold = False Then

Cells(lLoop - 1, Application.WorksheetFunction.CountA(Rows(lLoop - 1)) + 1).Value = Cells(lLoop, 1).Value

Rows(lLoop).Delete xlUp

lLoop = lLoop - 1

End If

100:
lLoop = lLoop + 1

Loop

End Sub