I have about 29k database records with event times in plain text format. I need to parse the plain text into SQL to insert into an events table.
The data format would be: Day of week, start time, name, language
If a day of week is not provided assume it's Sunday.
Attached is some sample data. Each line represents a different type of text format. I can provide a full data export.
Here's the schema and some default values:
CREATE TABLE `Event` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(75) NOT NULL,
`description` varchar(255) default NULL,
`dayofweek` int(10) unsigned NOT NULL,
`starttime` int(10) unsigned NOT NULL,
`length` int(10) unsigned NOT NULL,
`type` int(10) unsigned default NULL,
`status` int(10) unsigned NOT NULL,
`organizationid` int(10) unsigned NOT NULL,
`language` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
name: default to "Worship Service" if no name is given
dayofweek: sunday = 1, saturday = 7 (default to sunday if no day of week is given)
type: worship service = 1, bible study = 2, youth group = 3, sunday school = 4 (default to '1' if no name is given)
starttime: military time 24 hour clock
length: default to 0100 (one hour)
organizationid: the first column in the csv file
status: set to '2'
language: default to null if a language is not specified. If a language other than English is specified then prepend the language in the name of the event. For example a spanish event would be "Spanish Worship Service"
Here's a language mapping:
English => 1
Spanish => 2
Korean => 3
Chinese => 4
Japanese => 5
Russian => 6
Portuguese => 7
French => 8
German => 9
Italian => 10
Indian => 11
Other => 12