Link to home
Start Free TrialLog in
Avatar of hermesalpha
hermesalphaFlag for Paraguay

asked on

How do I use MySQL Server 5.1 for viewing, handling and exporting/importing to/from my Excel workbook a lot of textfiles created by a statistical software developed in Delphi 7.1?

I have a lot of textfiles that I normally use in an installed software (which has been developed in Delphi 7.1), and now want to use these textfiles "externally" outside of this software (for example in mySQL Server 5.1 that is already installed on my computer, although I have hardly ever used it; mySQL Server 5.1 is a prerequisite for running this software).

This software is a program for multiple linear regression analysis of historical harness racing races (trotting races), and it stores information about harness racing horses, drivers, past races, etc.

The program uses DbExpress in Delphi for connecting to a number of different databases. Databases that can be used with DbExpress are Interbase, Firebird, DB2, MySql, SQL-server, Oracle,Blackfish etc.

I am mostly interested in the contents and making own research on the contents, which is the reason I want to use these textfiles "externally" for viewing in mySQL Server 5.1 and for final viewing and editing and adding in my Excel workbook.

An example of when I want to export from one of these textfile databases to my Excel is when I have my harness racing Excel workbook open for a particular racing day (let us say 20160526) at a particular race course (let us say "Solvalla") or two particular race courses (for example "Solvalla" and "Jägersro") and want to import all historical data for the horses running this day so I can view them in my Excel and make modifications.

An example of when I want to import into my mySQL Server 5.1 database from my Excel is when I have completed my analyze of a current harness racing day's races and want to store my rankings, comments and other data for future reference.

I'm not sure how to handle this what regards viewing the textfiles in mySQL Server 5.1, creating correspondent tabs and columns in Excel for importing the contents, and the actual exporting and importing procedure.

I did a basic database course several years ago but can't remember much of what I learned then.

I have uploaded here an example of one of these textfiles.
horse.txt
SOLUTION
Avatar of William Nettmann
William Nettmann
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hermesalpha

ASKER

The solution William suggested seems very well suited for my purposes. The more I can work in Excel, the better for the material I have because I need to keep a good overview all the time of many different parameters and information.

Snoyes, when you mention CSV, do you think I could use the sample textfile I uploaded here in MySQL (just save it as CSV file)? But the best would be to import directly to Excel, if I only knew the structure first of the textfile.

Even though I'm an experienced wager on harness racing, when I open the textfile I uploaded here it's not easy to sort out the information. So what would the first step be? Wouldn't it be to sort out the information first, structure it according to headings, and then create the same structure in Excel with column headings, and then import/open the textfile (or textfile saved as CSV) in Excel using the mySQL add-on? But I'm not sure about the procedure and where to start.

I have a lot of these textfiles, although this textfile is one of the larger ones. This textfile contains information about the horses, other textfiles might contain information about the drivers, the trainers, the tracks etc.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sinisa Vuk
On Torry site ... there are few converters which can convert to xls or other db... (Advanced CSV Converter or Exportizer or ESF Database Convert...)
Sinisa, the database files I have are in txt format, would a converter work for that? (Just to change file extension from txt to csv first?)
worth to try ....  your txt file is just like csv but separate by TAB (0x09)
Sinisa and Snoyes, I will try first to import directly into Excel, just wonder what settings I should use? So I try importing the textfile I uploaded here directly into Excel.

Choose the file type that best describes your data: Delimited?
But it says (pre-selected) "932 : Japanese (Shift-JIS)". Why? What should I change this to?

Delimiter I think is only tab (the data seems quite in order).
Text qualifier? (none or ""?)

Column data format: I'm not sure about all the columns, if not sure should I choose "General"?

Sinisa, do I really need a converter? And if I need, what should I choose on that Torry site (there are many to choose from)?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am trying now to download the software I use for viewing these databases in textfiles, but I can't download it for some reason. I get almost all downloaded, but never all. This is the website:

www.travstat.se

The file is on 277 MB.

I need to open the database files in this software first before I can sort out the meaning of the different columns in the textfiles. Some columns are possible to understand immediately, some are only possible to understand once I've opened the database in this software.
Thanks for clarifying the best methods for handling database textfiles in Excel through MySQL, will try your suggestions.