• Hi all and welcome to TheWoodHaven2 brought into the 21st Century, kicking and screaming! We all have Alasdair to thank for the vast bulk of the heavy lifting to get us here, no more so than me because he's taken away a huge burden of responsibility from my shoulders and brought us to this new shiny home, with all your previous content (hopefully) still intact! Please peruse and feed back. There is still plenty to do, like changing the colour scheme, adding the banner graphic, tweaking the odd setting here and there so I have added a new thread in the 'Technical Issues, Bugs and Feature Requests' forum for you to add any issues you find, any missing settings or just anything you'd like to see added/removed from the feature set that Xenforo offers. We will get to everything over the coming weeks so please be patient, but add anything at all to the thread I mention above and we promise to get to them over the next few days/weeks/months. In the meantime, please enjoy!

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

AndyP

AndyP

Moderator
Staff member
Joined
Jul 22, 2014
Messages
14,203
Reaction score
1,750
Location
14860 Normandy, France
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
 
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
 
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
 
I use Open Office on my Mac, though I've never used the Spreadsheet function only Word Processing.
Downloads are free.

Rod

Rod
 
StevieB":2zbr4y4l said:
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
Click to expand...

Steve I know that approach will work with Windoze but I have tried with the text editor and word to no avail - so far.
 
Deejay":14vyqyc4 said:
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
Click to expand...

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.



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.
 
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
 
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.
 
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...
 
I thik I have found a better way without selling even more of my soul to goggle. Have to rush will report back later
 
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.
 
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.
 
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.
 
Morning Andy

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

Cheers

Dave
 
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 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!
 
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.
 
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.
 
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
 
You must log in or register to reply here.
Back
Top