Help to use txt file or xls file into MySQL

[SA Rob]

Hi folks,I would like to know if it is possible to write a script that will recognise all details in a spreadhsheet or txt file and upload it to MySQL ?

[justsomeguy]

Sounds possible. The first thing you need to do is define what "recognize all details" means.

[jeffman]

If your spreadsheet file is static data, not so hard. If it contains dynamic calculating functions, and you need to keep those somehow, then it's more difficult.If you only need to do this a few times, phpMyAdmin can import/convert files for you.If you need a conversion system, it should not be too hard. If the files contain labels for the columns, that would help a lot. If you already know the data types for the columns, that would help too. If your conversion system needs to look at the data in each column and decide (1) what kind of data it is and (2) how much space to allocate for it, that's trickier, but it can still be done.Any info you can provide about that stuff will help us give you advice.

[SA Rob]

Sounds possible. The first thing you need to do is define what "recognize all details" means.

Thanks for the prompt reply,What I am suggesting is that if I prepare an excel spreadsheet in a certian format , rows columns and fields then the information Feild would be the field heading in MySQL and the data in the columns and rows below thus the same in MySQL.Similarly if the information was found in a text file then structuring the text file so that MySQL would read the information into the correct feilds in MySQL.

[SA Rob]

If your spreadsheet file is static data, not so hard. If it contains dynamic calculating functions, and you need to keep those somehow, then it's more difficult.If you only need to do this a few times, phpMyAdmin can import/convert files for you.If you need a conversion system, it should not be too hard. If the files contain labels for the columns, that would help a lot. If you already know the data types for the columns, that would help too. If your conversion system needs to look at the data in each column and decide (1) what kind of data it is and (2) how much space to allocate for it, that's trickier, but it can still be done.Any info you can provide about that stuff will help us give you advice.

I have an excel spreadsheet that I use to capture golf scores for kids and I was wondering if it was possible then to take the results across in MySQL without having to retype everything.Ideally if I had the process done in PHP where people could use the internet , capture the scores and then produce a result of who the winners are then I wouldnt have to do the import and the data would be consistant all the time

[jeffman]

Yes, that would be a big time saver, and it's very doable.Let's imagine a moment in time where someone is entering scores. Would that user be entering scores for a single player, a fixed number of multiple players, or an arbitrary number of multiple players? (The arbitrary thing would require some javascript, probably, if you don't want to upload an arbitrary number of forms. But definitely doable.)Would it be one score for each of 18 holes, or just the total score? 18 would not really be a lot more work.

[justsomeguy]

phpMyAdmin can already import spreadsheets and CSV files and things, have you looked at that?

[SA Rob]

Yes, that would be a big time saver, and it's very doable.Let's imagine a moment in time where someone is entering scores. Would that user be entering scores for a single player, a fixed number of multiple players, or an arbitrary number of multiple players? (The arbitrary thing would require some javascript, probably, if you don't want to upload an arbitrary number of forms. But definitely doable.)Would it be one score for each of 18 holes, or just the total score? 18 would not really be a lot more work.

Hi Deidre's Dad,The idea is to score the information played for 18 or 36 holes where the kids participate in a tournament.So the information would be for multiple players and multiple rounds (max 4). The scores would be calculated from the data entered and that information can be used to determine thier trends.

[SA Rob]

phpMyAdmin can already import spreadsheets and CSV files and things, have you looked at that?

Thanks for this reply,I havent looked at the importing of the files yet as I did not know this could be done. Please advise whereI should look and any other pointrs you may have?

[jeffman]

The idea is to score the information played for 18 or 36 holes where the kids participate in a tournament. So the information would be for multiple players and multiple rounds (max 4)

So you need to be thinking about two things:1. a database structure to hold this information. I imagine that most of your content tables will look like rows of player names and columns of holes. Each field will hold the score earned by one player at each hole. A game of 18 holes would require a table with 18 columns. How it all fits together, you might want to think about some more. Like, you'll need tables to store information about handicaps per player and pars per course.2. knowing the maximum amount of information that would ever be input at one time, you'll need an HTML form for submitting the data to your server.

[SA Rob]

So you need to be thinking about two things:1. a database structure to hold this information. I imagine that most of your content tables will look like rows of player names and columns of holes. Each field will hold the score earned by one player at each hole. A game of 18 holes would require a table with 18 columns. How it all fits together, you might want to think about some more. Like, you'll need tables to store information about handicaps per player and pars per course.2. knowing the maximum amount of information that would ever be input at one time, you'll need an HTML form for submitting the data to your server.

Hi, You are again correct.This is where I am at:

1. I have an online entryform for all the players names and handicaps.
2. I have a file that will do the draws -sort the players into the filed each with thier own starting times, and they can be in gorups of 2,3 or 4

So yes the information that is still required for me to do is:

1. Set up the html page to capture, reveiw the tournament scores, for the specific tournaments
2. Set up a form for course information

All th edata needs to be linked I guess in some way so that when I request data the seacrh time reduced and specific in return, ie no confussion.We have about 30 event this year , half are completeThaks for your understanding and time, It sounds also like you know somethinng about the game.

[jeffman]

What does the database structure look like now? Is there one? (Or have you been doing this mostly on Excel and making the data connections mentally or by hand?)

[justsomeguy]

I havent looked at the importing of the files yet as I did not know this could be done. Please advise whereI should look and any other pointrs you may have?

The phpMyAdmin Import tab has all of the settings.

[SA Rob]

What does the database structure look like now? Is there one? (Or have you been doing this mostly on Excel and making the data connections mentally or by hand?)

Hi,I use MySQL so far for the database of players that have entered, thier names, contact details, handicaps.Each entry has an ID = "Record_Nr" and it is in its infancy stageI have sevral other golfing software programs which I get ideas from and my scoring program at the moment is a combination of sheets - using excel.The selction and sorting of winners is done manually by using a sorting process and on anothe rsheet I have used a ranking system to the same ends.I hope that helps with providing understanding.

[SA Rob]

The phpMyAdmin Import tab has all of the settings.

Thanks for that information I will go have a look there as well