I have a text file that I need to import and transform in access. The first row is a header row. I can open it in access and create the table. I need to transform some of the data.
The fields currently import like this :
SwimmerID 50free 50fly 50back 100IM
With the records below them. SwimmerID is a number, and each race is a time entry. I've been asked to find a way to transform them to be able to match the existing structure of a MySQL table configuration (This is a local club, I am volunteering my help, if you can call it that). I need to take the current table, and transform it so that it takes the field names and writes them as values, and then puts times in next to them, so the new structure looks like this for fields with a few sample record below it:
Swimmer Event Time
45567 50free 42.24
45567 50fly 43.29
45567 50back 49.28
45567 100IM 57.24
(next swimmer)
45568 50free 47.25
45568 50fly 49.57
45568 50back 36.45
45568 100IM 58.92
You can see I need to repeat the ID number to match the number of event fields (always the same number of times), the text file has values in all fields, and I need to repeat the same pattern over and over for each swimmer.
I have found a great script to export the access table to MySQL, so I can do that part, but I need to find a way to transform the data first to match the existing table structure being used on the website (I so enjoy volunteering for things I'm not capable of doing. "Can anybody help with the website?" "Ed has a personal webpage for his family, so he can do it" )
I will need to update this weekly (complete dump of existing data to update with new data.)
If anyone can help, I'd appreciate it. If this is in the wrong forum, moderators please move it to the correct one (I wasn't sure if I should post here or queries or macros)
The fields currently import like this :
SwimmerID 50free 50fly 50back 100IM
With the records below them. SwimmerID is a number, and each race is a time entry. I've been asked to find a way to transform them to be able to match the existing structure of a MySQL table configuration (This is a local club, I am volunteering my help, if you can call it that). I need to take the current table, and transform it so that it takes the field names and writes them as values, and then puts times in next to them, so the new structure looks like this for fields with a few sample record below it:
Swimmer Event Time
45567 50free 42.24
45567 50fly 43.29
45567 50back 49.28
45567 100IM 57.24
(next swimmer)
45568 50free 47.25
45568 50fly 49.57
45568 50back 36.45
45568 100IM 58.92
You can see I need to repeat the ID number to match the number of event fields (always the same number of times), the text file has values in all fields, and I need to repeat the same pattern over and over for each swimmer.
I have found a great script to export the access table to MySQL, so I can do that part, but I need to find a way to transform the data first to match the existing table structure being used on the website (I so enjoy volunteering for things I'm not capable of doing. "Can anybody help with the website?" "Ed has a personal webpage for his family, so he can do it" )
I will need to update this weekly (complete dump of existing data to update with new data.)
If anyone can help, I'd appreciate it. If this is in the wrong forum, moderators please move it to the correct one (I wasn't sure if I should post here or queries or macros)
