Page 1 of 2 12 LastLast
Results 1 to 25 of 26

Thread: [Resolved] Excel website data import

  1. #1
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208

    [Resolved] Excel website data import

    Reference: http://www.worldcommunitygrid.org/te...rdsPerPage=100

    I'm looking for someone smarter than me. That's the easy part. I'm using Microsoft Excel 2003, because I can't afford the newer versions. I am trying to do a Data > Import External Data > from the referenced link.
    The data field starts with Member Name. I am importing the data into an empty worksheet named WCG. At most, the worksheet will have yesterday's manually download of data in it.

    Here is the catch. It doesn't work, automatically. I am trying to get the spreadsheet to import the daily updated data every time I open the .xls file. When I do get something to import it puts unwanted data into the beginning columns before the wanted data is brought in/imported. With this unwanted data being brought in, I can't check to see if the wanted data is being used correctly, because all the wanted data is columns to the right of where the other worksheets are looking for the data to be.

    Until this is corrected, I can't really determine of the import is occurring correctly each time I open the file. I suppose I could adjust the programming on the other sheets to compensate for the right shift in the wanted data location on the worksheet, but that feels like cheating. Besides, I'd like to know why Excel in importing the unrequested columns of data.

    Any and all assistance in solving this exasperating issue is much appreciated.

    Gandalf

    P.S. I can manually import the data successfully via copy and paste techniques.
    It's the automatic technique that I am sorely failing at.
    Last edited by Gandalf; 02-12-2013 at 09:32 AM.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  2. #2
    Xtreme Enthusiast
    Join Date
    Oct 2007
    Location
    Mid UK. Lift a few rocks, eventually you will find me.
    Posts
    666
    I had a look and Open Office does the same.

    I also checked out the source code of the web page.

    The web page content is organised into separate html tables for the title area and the content of the WCG results data.
    It seems that the import inserts part of the first table the title area into the first column and the second table, the actual results, into the second and so on columns.

    Without parsing the page through a script to dump the data into one html table or a csv file I cannot see how this can be automated unless WCG change the html coding of the page.

    Are there any of your buddies doing WCG who can help.

    I could write a php script to do this but you would then need to run a webserver in order to run the php script.

    I would have to relearn .net or Windows command line scripting to create something that would automate the process in windows.
    It has been many years since I did any windows coding

    Sorry I cannot help further.
    Fun Box: Asus P8Z68-V GEN3++Corsair AX850++i5 2500k@4.5Ghz-1.272v++Corsair A50++2x8Gb Corsair Vengeance++MSI R7970 Lightning++Audigy2 Plat-EX++TBS 6280 DVB-T2 tuner++256Gb OCZ Vertex 4.500Gb Caviar Black.500Gb Seagate Barracuda++Sony AD7240s++Lian-Li PC-60++Linux Mint/Win 7++Asus P238Q

    Work Box: Gigabyte H61MA-DV3++Corsair HX620++i5 3450@stock++2x8Gb Corsair Vengeance++120Gb OCZ Agility 3++Linux Mint

    Quantum theory in a nutshell: It's so small we don't know where it is, it could be here, it could be there.

    Just 'cos it's legal don't make it right.

  3. #3
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    This is my posting to WCG as suggested...
    http://www.worldcommunitygrid.org/fo...age,yes#411651


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  4. #4
    Registered User
    Join Date
    Apr 2010
    Location
    NC
    Posts
    389
    I created a new excel file in Excel 2010 and it seems to work without moving columns around. Maybe using a file created in Excel 2010 will help??
    www.wcgdaws.com/releases/testbook1.zip

  5. #5
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by pirogue View Post
    I created a new excel file in Excel 2010 and it seems to work without moving columns around. Maybe using a file created in Excel 2010 will help??
    www.wcgdaws.com/releases/testbook1.zip
    Great! Now all you need to do is give me one of your Excel 2010 install licenses.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  6. #6
    Registered User
    Join Date
    Apr 2010
    Location
    NC
    Posts
    389
    I saved it in .xls (97-2003) format, so you should be able to open it.

  7. #7
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by pirogue View Post
    I saved it in .xls (97-2003) format, so you should be able to open it.
    I appreciate your forethought. Yes, I was able to open it. As you indicated, it looks good. How I'm going to beg, barrow or steal the 2010 version of Microsoft Excel is another question. The conclusion is, is there a 2003 patch that fixes the issue that obviously the 2010 version fixed.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  8. #8
    Registered User
    Join Date
    Apr 2010
    Location
    NC
    Posts
    389
    I was hoping that maybe using a file created in 2010 would solve the problem. Oh well.

  9. #9
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by pirogue View Post
    I was hoping that maybe using a file created in 2010 would solve the problem. Oh well.
    Pirogue,

    You solved one problem and created another. Not your fault. Another MS money maker.
    What's new. Now, if I can just find where I hid my piggy bank.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  10. #10
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.

    Open a new workbook, open Visual Basic, insert a Module in the new workbook and add the following macro:

    '---Start Macro---
    Public Sub CapTeamStats()


    Dim boSvWarn As Boolean 'Save Application Warning Value
    Const coTSURL As String = "http://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?sort=points&teamId=TW5XW7P WT1&pageNum=1&numRecordsPerPage=100&xml=true"


    Cells(1, 1).Select 'Select First Cell
    boSvWarn = Application.DisplayAlerts 'Save Warning Setting
    Application.DisplayAlerts = False 'Suppress Warnings
    ActiveWorkbook.XmlImport URL:=coTSURL, ImportMap:=Nothing, _
    Overwrite:=True, Destination:=Range("$A$1") 'Import XML File
    Application.DisplayAlerts = boSvWarn 'Restore Warning Setting

    End Sub
    '---End Macro---

    Then flip back to the first sheet in the new work book and run the Macro you just added.

    The data I believe you want will be in the following columns:

    Name2=WCG Member Name
    MemberId=WCG Member ID
    JoinDate=Date Joined Team (m/d/y)
    RunTime=Team Runtime in seconds
    Points=WCG Points Awarded
    Results=WCG WUs Completed
    Url3=URL to Member Stats
    Description4=WCG Member Name Repeated
    RetireDate=Date Left Team

    As for importing the daily updated data every time you open the .xls file you have to setup the workbook to autoexec the macro on open.

    Good luck and let me know if this gets what you're looking for.

  11. #11
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.
    ...

    As for importing the daily updated data every time you open the .xls file you have to setup the workbook to autoexec the macro on open.

    Good luck and let me know if this gets what you're looking for.
    wplachy - Thank you for your recommendation. However, upon applying your macro, the following resulted...





    Since I copy and Pasted your macro, I know it's not a typo on my part.
    Last edited by Gandalf; 02-08-2013 at 09:50 AM.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  12. #12
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    Quote Originally Posted by Gandalf View Post
    wplachy - Thank you for your recommendation. However, upon applying your macro, the following resulted...





    Since I copy and Pasted your macro, I know it's not a typo on my part.
    There is a space in the URL string for teamID= remove it and it works fine.

    You have:

    ---------------------------------------------------------------------------------------------------------------------------V--------------------------------------------------
    Const coTSURL As String = "http://www.worldcommunitygrid.org/team/viewTeamMemberDetail.do?sort=points&teamId=TW5XW7P WT1&pageNum=1&numRecordsPerPage=100&xml=true"
    Remove the space between teamID=TW5Xw7P WT

    It should read teamID=TW5XW7PWT1 (no space between P and W)

    It looks like the forum software is inserting it. Just change it in the Const= string and try again.

    Bill P

  13. #13
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    There is a space in the URL string for teamID= remove it and it works fine. Bill P
    To begin with, thank you Bill for your support.
    I never was a good programmer. Engineering is much easier and wizardry just comes naturally to an engineer.

    Rather than try to explain in words the results I saw, I think a picture of what I need will explain things far better.



    How do we fix the macro to provide this result?


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  14. #14
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    Gandalf, I'm confused as to what you are looking for. The speadsheet that pirogue provided (www.wcgdaws.com/releases/testbook1.zip) gives you the data you are looking for in the format you want with the 1 minor exception of splitting the heading into 2 rows. The macro I provided pulls the data in a different format but provides the same information.

    pirogue's spreadsheet works fine in Excel 2003 and you do not need 2010 to use it. Everytime you open the spreadsheet file it will refresh the data. The only thing you would need 2010 for is to build a new/different import query, as that doesn't seem to work correctly in 2003.

    Can you help me understand the problem you have with using the data imported by piroque's spreadsheet or my macro?

    Bill P

  15. #15
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    Gandalf, I'm confused as to what you are looking for. The speadsheet that pirogue provided (www.wcgdaws.com/releases/testbook1.zip) gives you the data you are looking for in the format you want with the 1 minor exception of splitting the heading into 2 rows. The macro I provided pulls the data in a different format but provides the same information.

    pirogue's spreadsheet works fine in Excel 2003 and you do not need 2010 to use it. Everytime you open the spreadsheet file it will refresh the data. The only thing you would need 2010 for is to build a new/different import query, as that doesn't seem to work correctly in 2003.

    Can you help me understand the problem you have with using the data imported by piroque's spreadsheet or my macro?

    Bill P
    Sorry. The data is there, I just didn't expect all that other import. I can reprogram from here. Thanks.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  16. #16
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    There is another way to pull this using Excel 2003 that I've found works well and doesn't require Excel 2010.

    Good luck and let me know if this gets what you're looking for.
    wplachy - Have incurred a Run-time error 1004.
    The operation cannot be completed because the XML list is bound to a different XML map.

    If you want, I'll attach a .zip of the .xls file.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  17. #17
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    Quote Originally Posted by Gandalf View Post
    wplachy - Have incurred a Run-time error 1004.
    The operation cannot be completed because the XML list is bound to a different XML map.

    If you want, I'll attach a .zip of the .xls file.
    Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back

    Bill P

  18. #18
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back

    Bill P
    When the Macro query pops up, if I say NO, no error message.
    When I say YES, error message.

    Is someone modifying your File Upload Manager?
    It says Click Add Files, but there is no Add Files button.


    Talk about making a simple thing difficult.
    Will upload my .xls file as soon as I can get the up loader to work.
    That deleting of the upload within an hour kind of stinks.
    Last edited by Gandalf; 02-09-2013 at 11:48 AM.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  19. #19
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    Quote Originally Posted by Gandalf View Post
    Talk about making a simple thing difficult.
    Will upload my .xls file as soon as I can get the up loader to work.
    That deleting of the upload within an hour kind of stinks.
    No need to upload the file as I have identified the problem. I have a fix and will in addition supply you with the code to format the data the way you want to see it. Give me a few minutes to finsh writing and testing it and then I'll post the macros here.

    Bill P

  20. #20
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    The first Members in the "thisweek" worksheet now calls up =IF(WCG!N87<>"",WCG!K87,"").
    The data row starts at WCG!A2. Row 85 is the last Members data row in the WCG imported data.
    Therefore, all the data transfer in the "thisweek" worksheet is blank.

    =IF(WCG!N87<>"",WCG!K87,"") should read =IF(WCG!N2<>"",WCG!K2,"")

    "The second is a new macro that will add a new sheet and build a formatted team stats list ..."
    No new sheet is created. I'm assuming the new Sub was meant to be added to Module1 just after the original Sub.

    "If you want to you can set CapTeamStats() to call this new one after it completes the XML load"
    I do not know where/how to do this. I think you are assuming I am smarter than I am. What I have seen in other scripts is a line of programming put in it's proper place but commented out so the user can activated the line simply by removing the comment marks. That I can do!


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  21. #21
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by wplachy View Post
    Yes, attach a copy of the .xls with the macro(s) intact. While I have never run into that problem I suspect Excel thinks you are trying to re-map a XML map. Also, please describe what steps you take to introduce the error so I may duplicate it. I'll be out this afternoon and will look at it this evening or tomorrow AM and post back. Bill P
    This should be the .zip file. It's my first try, so I hope it works.
    http://www.mediafire.com/download.php?hnp6348i3jhmeb4


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  22. #22
    Xtreme Addict
    Join Date
    Nov 2010
    Location
    Tinley Park, IL
    Posts
    1,005
    The problem is that when Excel updates the XML sheet ("WCG") it changes the cell references in the "lastweek" sheet. If I get some time I'll see what has to be done to stop the cell reference updates. Until then I changed the code to get around the problem.

    I updated the spreadsheet you posted so that it works the way you want it to. The updated spreadsheet is at

    http://d01.megashares.com/dl/GcKRgri/Test1Rev2.zip

    When you click on the link you'll get a choice of "regular" or "Hi-Speed Download", select the regular. Ignore anything on the next page about "Reactivating Your Passport". You don't need to do that. Just click on the "Download File" Button and save the file on your PC. You may have to do it a couple of times. When I tested the download I got a Web Site not found. I just went back to the link and then it worked fine.

    When you open the updated spreadsheet it will pull the team stats and update the "thisweek" sheet.

    The changes I made were to modify the CapTeamStats() macro, delete the FormatTeamStats() macro and removed the references in "thisweek" to the "WCG" sheet. The macro will fill in the Member and current points and results in the "thisweek" sheet.

    Bill P
    Last edited by wplachy; 02-11-2013 at 12:04 AM.

  23. #23
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    When I click on the link, then click on the REGULAR DOWNLOAD button, I get a DOWNLOAD FILE button.
    When I click on the DOWNLOAD FILE button, it puts me back to the REGULAR DOWNLOAD button.
    Nothing gets downloaded.

    Edit: I signed up with your Megashares and now have the file.
    Last edited by Gandalf; 02-11-2013 at 02:44 AM.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  24. #24
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Talk about timing. Yesterday WCG had some downtime with the upload/download of their tasks. Sunday is our team's normal Stat Report posting day.
    http://www.pchelpforum.com/xf/thread...-10-13.147024/
    You'll notice my again was about 1.2 million points for a week's worth of crunching.

    Well, when I ran your revision of the Stat Report, it showed I have over one million points gain for one day. Now, my machines work hard, but not that hard. So obviously I figured you have slipped up with your cell movement. Sorry, but that is what I thought. So, I rechecked everything and your revision is spot on. Apparently, unless WCG takes back the extra points, they must have recalculated their counting protocol or their verification techniques have been tweaked. Anyway, the timing was eerie between whatever they did and my downloading of your new revision. I'll be very interested in seeing what the numbers say tonight after the Sunday/Monday calculation day. Also, tomorrow will verify the automatic feature you programmed in.

    I noticed you put in a warning in the event that we get more new members than we have provided rows to account for them. Wise precaution. I'll post again tonight after I confirm the automatic data download feature. WCG only updates their numbers once a day.


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

  25. #25
    Registered User
    Join Date
    Feb 2013
    Location
    Middle Earth
    Posts
    208
    Quote Originally Posted by Gandalf View Post
    ... WCG only updates their numbers once a day.
    The WCG daily stats are out and your programming worked like a charm, but you already knew that didn't you.


    Thanks for all the help.
    Gandalf


    "I refuse to answer that question on the grounds that I don't know the answer" [Douglas Adams (11 March 1952 - 11 May 2001)]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •