It is currently 28 Mar 2024, 23:41

Help required, Excel for Mac 2011, data from web page.

Hang up your Chisels and Plane blades and take a load off with a recently turned goblet of your favourite poison, in the lounge of our Gentlemen's (and ladies) Club.

Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 10 Mar 2015, 14:09

Can anyone help me to extract the Woodhaven Members list into Excel for Mac 2011? Such that the column data as seen on line is replicated in Excel

In Windows and Excel 2002 Bob is able to copy from the webpage and paste into excel with acceptable results. When I try this in Excel for Mac 2011 most of the data ends up in the first column.

I have tried pasting into the Mac Text Editor and Word for Mac without success. And the Get External Data function does not include lifting XML from the web.

TIA
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby StevieB » 10 Mar 2015, 15:21

Not sure on a Mac, but when we have odd format data at work I tend to copy it to a flat file (notepad or similar text editor) save that as a file and then 'import data' (DATA TAB -> FROM TEXT OPTION, then select the file you made) with excel, which lets you either specify a delimter or make fields a set width. That takes care of most situations.

Steve
StevieB
Nordic Pine
 
Posts: 886
Joined: 21 Jul 2014, 21:47
Location: Chatteris, Cambridgeshire
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Deejay » 10 Mar 2015, 15:51

Afternoon Andy

I've just tried it in Open Office on Win7.

The Username and Rank appear in the same column, but everything else looks OK.

You get options to save as various file types, one of which is CSV.

If you don't get the solution for a Mac, Open Office is free and there is a Mac version

PM me an address and I'll copy it and send it over if you want to try it.

Cheers

Dave
User avatar
Deejay
Sapling
 
Posts: 428
Joined: 22 Jul 2014, 09:36
Location: Wiltshire
Name: Dave

Re: Help required, Excel for Mac 2011, data from web page.

Postby Rod » 10 Mar 2015, 16:25

I use Open Office on my Mac, though I've never used the Spreadsheet function only Word Processing.
Downloads are free.

Rod

Rod
User avatar
Rod
Old Oak
 
Posts: 4471
Joined: 21 Jul 2014, 21:34
Location: Winchester, Hampshire
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 10 Mar 2015, 18:06

StevieB wrote:Not sure on a Mac, but when we have odd format data at work I tend to copy it to a flat file (notepad or similar text editor) save that as a file and then 'import data' (DATA TAB -> FROM TEXT OPTION, then select the file you made) with excel, which lets you either specify a delimter or make fields a set width. That takes care of most situations.

Steve


Steve I know that approach will work with Windoze but I have tried with the text editor and word to no avail - so far.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 10 Mar 2015, 18:12

Deejay wrote:Afternoon Andy

I've just tried it in Open Office on Win7.

The Username and Rank appear in the same column, but everything else looks OK.

You get options to save as various file types, one of which is CSV.

If you don't get the solution for a Mac, Open Office is free and there is a Mac version

PM me an address and I'll copy it and send it over if you want to try it.

Cheers

Dave


Dave, thanks for trying. I'd rather not resort to yet more software. I've used Open Office in the past and did not like the compatibility problems with Windows documents.

Here is an example of what happens when copy and pasting into Excel for Mac no amount of faffing about with the Import Wizard seems to help.

Image

I fair no better pasting into the text editor then opening in Excel. The text editor file options .rtf, .odt etc will not open in Excel.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby RogerS » 10 Mar 2015, 18:52

Andy

How are you grabbing the data off the webpage in the first place?

I just tried highlighting the first page and then copy and pasting into textEdit. However, the Mac has some non-ASCII hex characters there which also get cut'n'pasted. Excel displays them as a weird A
If opportunity doesn't knock, build a door.
User avatar
RogerS
Petrified Pine
 
Posts: 13291
Joined: 21 Jul 2014, 21:07
Location: Nearly finished. OK OK...call me Pinocchio.
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 10 Mar 2015, 19:21

Roger,
I've just been placing the cursor to the left of the first name then left mouse drag down to bottom right of table, which highlights the data. RIght Click, Copy, then Paste in Excel.

I am totally baffled as to why so much ends up in one column and I've yet to find a way of converting rows to columns.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby RogerS » 10 Mar 2015, 22:59

The copy and paste from the webpage embeds strange control characters in the data.

I have managed to edit those out and replace with spaces using BB Edit.

That then leaves us with a text file but with fields delimited by carriage returns.

I can use Word to replace the carriage returns with commas which can be used as a delimiter in Excel.

The only problem then is that there isn't as far as I can see an end of record marker.

At the moment, I have all the text in cells along one row!

The only other way might be to scan it in using OCR...
If opportunity doesn't knock, build a door.
User avatar
RogerS
Petrified Pine
 
Posts: 13291
Joined: 21 Jul 2014, 21:07
Location: Nearly finished. OK OK...call me Pinocchio.
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby RogerS » 10 Mar 2015, 23:05

No end of record delimiter.

BUT

try this out https://eagereyes.org/data/scrape-table ... oogle-docs
If opportunity doesn't knock, build a door.
User avatar
RogerS
Petrified Pine
 
Posts: 13291
Joined: 21 Jul 2014, 21:07
Location: Nearly finished. OK OK...call me Pinocchio.
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 11 Mar 2015, 08:47

I thik I have found a better way without selling even more of my soul to goggle. Have to rush will report back later
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help Excel for Mac 2011, data from web page. Solved

Postby Andyp » 11 Mar 2015, 10:12

This is my preferred solution.

A simple Firefox add-on

https://addons.mozilla.org/en-US/firefo ... clipboard/

Simply highlight the data in the table then a right click brings up an option "Table 2 Clipboard" Select.

Paste special in excel as text ( to remove the hyperlinks )

I then have to find and replace the Ranks and manually remove any website address which I will do until our technical guru has worked out how to place them into separate columns ;)

thanks all.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby 9fingers » 11 Mar 2015, 10:27

Thanks for that Andy!
:text-bravo:
It seems even easier to use than you suggest.
With the source window open and nothing selected, simply go to the edit menu where there is a new entry "copy all tables" or (cntrl shift F2 in windows)
then switch to excel and past special as text as per your posting.

Works well with SeaMonkey browser too - which I think Dave might be using?

Bob

Note that removing ranks is fairly easy. Sort the excel sheet on column A which will put the ranks in order making it a fairly painless but repetitive edit/delete sequence. You could possibly teach it a macro to do it , one for each rank maybe - it is a long time since I wrote excel macros.
Information on induction motors here
https://drive.google.com/file/d/1dBTVXx ... sp=sharing
Email:motors@minchin.org.uk
User avatar
9fingers
Petrified Pine
 
Posts: 10038
Joined: 21 Jul 2014, 20:22
Location: Romsey Hampshire between Southampton and the New Forest
Name: Bob

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 11 Mar 2015, 10:34

Brillant Bob, Teamwork eh?

Anyone care to try and explain why the Rank which is on the right of the first column in the web page is transposed to the left when pasted into excel?

Takes be about 5 mins to delete the ranks and the web addresses so that I can easily reconcile with the members map which is no big deal but it is a shame that they are not in their own columns.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby Deejay » 11 Mar 2015, 10:53

Morning Andy

Can you Edit and then find all [rank] and replace with [blank]?

Cheers

Dave
User avatar
Deejay
Sapling
 
Posts: 428
Joined: 22 Jul 2014, 09:36
Location: Wiltshire
Name: Dave

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 11 Mar 2015, 11:48

Dave,
yep that is what I do individually for each of the 6 ranks. Separating the website address from the location though is a bit more labour intensive.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby RogerS » 11 Mar 2015, 12:00

I enjoyed the stimulation for my braincells in trying to solve this which took me into the late hours of the night and way way past my bedtime! I didn't get there in the end but pleased you've found a solution!
If opportunity doesn't knock, build a door.
User avatar
RogerS
Petrified Pine
 
Posts: 13291
Joined: 21 Jul 2014, 21:07
Location: Nearly finished. OK OK...call me Pinocchio.
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 11 Mar 2015, 12:56

Oops Sorry about that Roger.

Although I was not burning the midnight oil, I am a bit like a dog with a bone with this type of thing. I used Excel a lot at work and always believed it could do just about anything you wanted it to which is why I am somewhat surprised and disappointed that Excel for Mac does not have all the same functions as the Windows version.
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy

Re: Help required, Excel for Mac 2011, data from web page.

Postby RogerS » 11 Mar 2015, 13:12

No need to apologise as I enjoyed the mental stimulation. I did come across a very neat thing under the Data tab where you can run a query that does exactly what you are trying to achieve. ie suck out data from a table on a webpage. There are some pre-written ones in Microsoft Office > Office > Queries and the suggestion is that you replace the URL in the example with your own. Well, I did try it and maybe it's something to do with our webpage being .php maybe or perhaps some of the other parameters needed tweaking, but it didn't work sadly. Worked fine with their examples!

I did wonder if the fact that after editing the text file was no longer in Simple Text format (.ttxt) but did manage to save an amended version with that extension. No cigar.
If opportunity doesn't knock, build a door.
User avatar
RogerS
Petrified Pine
 
Posts: 13291
Joined: 21 Jul 2014, 21:07
Location: Nearly finished. OK OK...call me Pinocchio.
Name:

Re: Help required, Excel for Mac 2011, data from web page.

Postby Andyp » 11 Mar 2015, 15:32

I've now automated the removal of the website address too.

Here's how.

Open Firefox
Sort the members table by location so that the first 3 pages contain the location data
Edit, Copy Table once for each, page
Paste NB not paste special, into Excel
Remove the duplicated column headers
Remove the Posts Count and date joined columns
Highlight Website Location column, Edit, Replace, Find What, http*, Replace with blank, Replace All
Edit Replace with blank each of the Ranks
Copy the remaining two columns then Paste Special, Values Only into a new sheet.

This leaves a few blank cells
Highlight the two columns, F5 (Go To Pop Up appears) select Special, Blanks, OK
Edit, delete, shift cells up. Voila
I do not think therefore I do not am.

cheers
Andy
User avatar
Andyp
Petrified Pine
 
Posts: 11718
Joined: 22 Jul 2014, 07:05
Location: 14860 Normandy, France
Name: Andy


Return to The Woodmangler's Retreat

Who is online

Users browsing this forum: Bing [Bot] and 15 guests