Welcome Guest
Log In | Register )
You last visited January 16, 2017, 7:51 am
All times shown are
Eastern Time (GMT-5:00)

Help importing web datafiles to excel

Topic closed. 12 replies. Last post 10 years ago by vt8366a.

Page 1 of 1
PrintE-mailLink
Avatar
Anywhere & Everywhere
United States
Member #10713
January 23, 2005
290 Posts
Offline
Posted: February 9, 2007, 2:59 pm - IP Logged

Is there a way to create a function that will go to 40 lottery websites and download the
last 60 recorded results from the Pick 3 data files on each website. The date field,
and each individual digit field (4 cells). Import each game or data file into the same target range
within separate worksheets of the same excel file. List the records in descending date order.

    CARBOB's avatar - FL LOTTERY_LOGO.png
    ORLANDO, FLORIDA
    United States
    Member #4924
    June 3, 2004
    5959 Posts
    Online
    Posted: February 9, 2007, 3:47 pm - IP Logged

    Is there a way to create a function that will go to 40 lottery websites and download the
    last 60 recorded results from the Pick 3 data files on each website. The date field,
    and each individual digit field (4 cells). Import each game or data file into the same target range
    within separate worksheets of the same excel file. List the records in descending date order.

    I would goto WWW.MrExcel.com and post that question. That sounds like a lot of work, if it can be done.

      winsumloosesum's avatar - Lottery-060.jpg
      Pennsylvania
      United States
      Member #2218
      September 1, 2003
      5396 Posts
      Offline
      Posted: February 9, 2007, 3:50 pm - IP Logged

      WSN1,

      Yes there is but only a number of the state's web sites can be used.

      This is link to an Excel program I use for Pick 3.

      http://rapidshare.com/files/15731991/P3_State_Drawings_Zip.ZIP

      The easiest thing to do is click the LP Results tab above and manually enter each state on the Main Page of the Excel worksheet.

      It should take no more than 10 minutes to complete.  There are Toolbars to update all the states from the Main Page. 

      If you need help let me know!!

      Good Luck. 

        Avatar
        Anywhere & Everywhere
        United States
        Member #10713
        January 23, 2005
        290 Posts
        Offline
        Posted: February 9, 2007, 3:53 pm - IP Logged

        I would goto WWW.MrExcel.com and post that question. That sounds like a lot of work, if it can be done.

        Carbob thanks, I posted this request on MrExcel prior to posting here.

          winsumloosesum's avatar - Lottery-060.jpg
          Pennsylvania
          United States
          Member #2218
          September 1, 2003
          5396 Posts
          Offline
          Posted: February 9, 2007, 4:02 pm - IP Logged

          Here is the same type of Excel file with about 60 draws in descending order from each state.

          I created this Excel Workbook for Dan Hudson's 1UP1Down Software Program.

          It takes 60 drawings and copies them to (Notepad) a .txt file. 

          All you do is update the state drawings (From LP Results Page) on the first tab of the Excel workbook then click to update all of the states.

          http://rapidshare.com/files/14244003/P3_1UP1Down_State_Drawings_Zip.zip 

          Then you can do what you want with each of the individual state draws.  Copy them to another Excel workbook.

          I have an updated Excel file with Thursday's (2/8/07) drawings if you want it. 

            Avatar
            Anywhere & Everywhere
            United States
            Member #10713
            January 23, 2005
            290 Posts
            Offline
            Posted: February 9, 2007, 4:15 pm - IP Logged

            Here is the same type of Excel file with about 60 draws in descending order from each state.

            I created this Excel Workbook for Dan Hudson's 1UP1Down Software Program.

            It takes 60 drawings and copies them to (Notepad) a .txt file. 

            All you do is update the state drawings (From LP Results Page) on the first tab of the Excel workbook then click to update all of the states.

            http://rapidshare.com/files/14244003/P3_1UP1Down_State_Drawings_Zip.zip 

            Then you can do what you want with each of the individual state draws.  Copy them to another Excel workbook.

            I have an updated Excel file with Thursday's (2/8/07) drawings if you want it. 

            Thanks very much, winsumloosesum...I will send you a PM

              Avatar
              Anywhere & Everywhere
              United States
              Member #10713
              January 23, 2005
              290 Posts
              Offline
              Posted: February 9, 2007, 4:20 pm - IP Logged

              Here is the same type of Excel file with about 60 draws in descending order from each state.

              I created this Excel Workbook for Dan Hudson's 1UP1Down Software Program.

              It takes 60 drawings and copies them to (Notepad) a .txt file. 

              All you do is update the state drawings (From LP Results Page) on the first tab of the Excel workbook then click to update all of the states.

              http://rapidshare.com/files/14244003/P3_1UP1Down_State_Drawings_Zip.zip 

              Then you can do what you want with each of the individual state draws.  Copy them to another Excel workbook.

              I have an updated Excel file with Thursday's (2/8/07) drawings if you want it. 

              Yes, I would like a copy. Thanks

                Raven62's avatar - binary
                New Jersey
                United States
                Member #17843
                June 28, 2005
                50955 Posts
                Offline
                Posted: February 9, 2007, 5:25 pm - IP Logged

                Is there a way to create a function that will go to 40 lottery websites and download the
                last 60 recorded results from the Pick 3 data files on each website. The date field,
                and each individual digit field (4 cells). Import each game or data file into the same target range
                within separate worksheets of the same excel file. List the records in descending date order.

                What Version of Excel are you using? Excel 2003 can do it: Import External Data

                A mind once stretched by a new idea never returns to its original dimensions!

                  Avatar
                  Anywhere & Everywhere
                  United States
                  Member #10713
                  January 23, 2005
                  290 Posts
                  Offline
                  Posted: February 9, 2007, 7:01 pm - IP Logged

                  What Version of Excel are you using? Excel 2003 can do it: Import External Data

                  Oh really, I have Excel 2003, and I see under Import External Data another sub-menu...Cool! How do I proceed from there! You PM me with further details. Also, I supposeI could just the LP or USA today websites, instead of all the lottery websites.

                    Avatar
                    Anywhere & Everywhere
                    United States
                    Member #10713
                    January 23, 2005
                    290 Posts
                    Offline
                    Posted: February 10, 2007, 12:51 am - IP Logged

                    Im also thinking that there is a way, to do a keyword search on the LP results page or even the past results page, and have the macro copy and paste the updated dates and draws within the main page a worksheet automatically.

                      Raven62's avatar - binary
                      New Jersey
                      United States
                      Member #17843
                      June 28, 2005
                      50955 Posts
                      Offline
                      Posted: February 10, 2007, 12:02 pm - IP Logged

                      Im also thinking that there is a way, to do a keyword search on the LP results page or even the past results page, and have the macro copy and paste the updated dates and draws within the main page a worksheet automatically.

                      It's a good possibility:

                      Data/Import External Data/New Web Query:

                      Enter URL of Lottery Data:

                      Click Arrows to Select Data:

                      Click Import!

                      A mind once stretched by a new idea never returns to its original dimensions!

                        Avatar
                        Anywhere & Everywhere
                        United States
                        Member #10713
                        January 23, 2005
                        290 Posts
                        Offline
                        Posted: February 10, 2007, 3:55 pm - IP Logged

                        It's a good possibility:

                        Data/Import External Data/New Web Query:

                        Enter URL of Lottery Data:

                        Click Arrows to Select Data:

                        Click Import!

                        Thanks Raven62, that works, however, I got 2 problems:

                        The only arrow that would allow me the import draw table is the entire page. Is there a way to size the selected area to the area of the page you want to import, then crop out the rest that you do not want or build an over the area of the page I want, otherwise, I would have to clean it up another way i guess in excel after it's imported. If I have to clean it up in excel, how?

                        When I imported the draw table some of the draw combos comes across as dates rather than 0-0-0, I've changed my cell format to text but that does not work once the data is refreshed. Is there a way to fix that column of output?

                          Avatar
                          New Member

                          United States
                          Member #48769
                          January 8, 2007
                          20 Posts
                          Offline
                          Posted: February 23, 2007, 10:37 pm - IP Logged

                          Is there a way to create a function that will go to 40 lottery websites and download the
                          last 60 recorded results from the Pick 3 data files on each website. The date field,
                          and each individual digit field (4 cells). Import each game or data file into the same target range
                          within separate worksheets of the same excel file. List the records in descending date order.

                          i have inserted a hyperlink into seperate worksheets that will take me to specfic parts of the lottery site , there i manually cut and paste into the worksheet the data i want, then i have recorded a macro that will insert blank rows, format the data the way i want and copy the new data to the worksheet. i have had problems getting the macro to copy and paste directly from the webpage.