I would like to analyze the sum of Pick-3 winning numbers in a state lottery that reports them as :xyz and not as x-y-z. It has proven very difficult to transform the x-y-z to x,y,z needed for an analysis.

Can some body please tell me a state that list the numbers as xyz, not x-y-z.

Help would be greatly appreciated. I need one year of data.

Bertil

South Carolina after deleting the st/ bx winners under each draw.

June23, 2004 [ Evening ] 2 0 3 
  June24, 2004 [ Midday ] 9 4 4
June24, 2004 [ Evening ] 4 5 6 
  June25, 2004 [ Midday ] 5 8 7
June25, 2004 [ Evening ] 6 2 7 
  June26, 2004 [ Midday ] 4 7 2
June26, 2004 [ Evening ] 9 3 7
June27, 2004 [ Evening ] 7 8 8 
  June28, 2004 [ Midday ] 1 6 4
June28, 2004 [ Evening ] 7 1 0 
  June29, 2004 [ Midday ] 1 6 9
June29, 2004 [ Evening ] 2 6 3 
 June30, 2004 [ Midday ] 5 7 5
June30, 2004 [ Evening ] 6 1 9

Bertil

Seems like a very easy task to me as they are always single digits!

What are you importing into?

Why can't you use the mid function and convert to byte?

Give some more details and I am sure a simple solution is available.

Colin 

I think what Bertil is saying is that the numbers are listed as 123 and not 1,2,3 or 1-2-3.

If you have MSExcel you can place the digits into seperate cells.

Save the list of numbers to notepad, open Excel and click on the folder icon. This will open a window of your folder and files, at the bottom where it says File type, change that to say All Files as notepad is a .txt file. Find the file that has the numbers list and open it. This will open another window that will allow you to convert the data into Excel, where it says choose the file type that best fits your data, choose fixed and click next. By clicking on the once on the measurement bar you can place seperator lines between the digits. Click finish and your digits are in three seperate columns.

Hope this helps.

UCI1

  UC11, Your advice looks good but there is a problem when saving the data file from the state lottery to my spread sheet. All numbers get converted to dates except for those with a zero in them. Cand these dates be reconverted to numbers and if so how should I do that. Once I have the numbers on the spread sheet as x-y-z I can replace the dashes with a blank and then prcess the digits into separate cells and perform statistical analysis of various kinds.

Bertil

Quote: Originally posted by Bertil on July 11, 2004

---

 

  UC11, Your advice looks good but there is a problem when saving the data file from the state lottery to my spread sheet. All numbers get converted to dates except for those with a zero in them. Cand these dates be reconverted to numbers? No not at this point. 

 Once I have the numbers on the spread sheet as x-y-z I can replace the dashes with a blank and then prcess the digits into separate cells and perform statistical analysis of various kinds. Only if the numbers and dashes are in seperate columns (cells). You must place the data on to notepad first and save.

Save the data to notepad first and then open Excel, click on the folder icon at the top of the screen in the tool bar. This will open the text import wizzard, follow all 3 steps. The 3rd step you can change how the data is viewed by Excel spreadsheet but most likely you won't have to as the default is general. I placed all of the past drawings day/eve for CT pick3 in your private inbox, copy and paste the numbers from there to notepad and follow the Excel text import wizzard instructions.

Good Luck

UCI1 Playing Strategic Lottery

bertil, leave the -signs there... follow UCI1's instructions... then when using the excel import wizard, you can choose - as a column separator.

there you can also pick the TYPE (date, number, text) of the column data.

you could also force-format the cell, or just put into notepad and use "find and replace" to replace the - with nothing.

When I got the data for the PB initially, I used the text file on the MUSL site, opened it with MSWord, sorted by date 'ascending' (so the newest is on the bottom) and then imported to excel.

I was able to copy 27 years of pick 3 data (one year per page) and sort it and import it in under 20 minutes using word as an intermediate step. (because you can SORT after converting text to a table in word)

Normally I would just leave something like this alone after giving the information I gave - but I'm mindful that others read posts like this.

So, what's the big deal I thought. Now, I have to agree that the CT site is one of the worst I've come across as far as downloading data but not insurmountable.

Here is what I recommend everybody does. Go and get yourself a student version of MS Access. You won't regret it.

First step use Save As from your browser to put it somewhere in your Computer in HTML format.

Open Access and create a database. Import your HTML File answering appropriately the questions the Wizard asks you. From the advanced button fine tune your date settings. Presto, you have it in Access with some errors which Access tells you about. No worries.

Create a query and paste in this SQL:

SELECT [Connecticut Lottery1].ID, [Connecticut Lottery1].Date,
Mid([Pick3],1,1) AS P3C1, Mid([Pick3],3,1) AS P3C2, Mid([Pick3],5,1) AS P3C3,
Mid([Pick4],1,1) AS P4C1, Mid([Pick4],3,1) AS P4C2,
Mid([Pick4],5,1) AS P4C3, Mid([Pick4],7,1) AS P4C4
INTO CT_P3_P4
FROM [Connecticut Lottery1]
WHERE (((Mid([Pick3],1,1)) Like "[0-9]*"));

Boongallalee, Oodnadata there you have it in a Table. Five minutes. To get the rest of the data just repeat the steps - maybe another five minutes for all of it.

Free up your minds - ditch the Excel - it's like a being in a jail looking at that grid all the time.

Colin