Alter table based on comment header in import file

[elsenorjose]

I have a text file which is the output of a Perl script that parses Windows Media Log files into a media analytics table. There are some additional columns which are defined by the customer so the actual table format can and does change. The engineer who provides this table has created a comment header with revision information as well as column names. Is there anything I can do to have MySQL look at the comment header and alter my table to include the newly added columns without destroying any data in the table? The column names would always be in the same line in the import file and when a new field is added, it would be appended to the field list, not added anywhere in between existing fields.

Example:

# FIELDS: client_ip,date_YYYY-MM-DD, time_HH:MM:SS,http_method,c_url,status_code,
total_bytes,transfer_time,"referrer",referrer_host,"user_agent",
total_object_size,byte_range_start,byte_range_end,last_byte_served_flag,
first_byte_served_flag,all_bytes_served_flag, akm_c_ip_country, akm_c_ip_dma, akm_c_ip_lat, akm_c_ip_long,CPROG,MARKET,NG_FORMAT,SITE_ID,STATION_ID,PCAST_TITLE,PCAST_AUTHOR,
PCAST_CAT

 

[elsenorjose]

I should note that I am using 'LOAD DATA LOCAL INFILE...' to import the file into my database.

Using MySQL 5.0.27 Community Edition using MyISAM tables

 

[hvass]

Ideally the perl script would, rather than writing out a rather vague header, write out a create table sql statement. It would be difficult to write something to interpret the header just in sql

load data infile '' into sometable fields terminated by '\n' lines terminated by '\n' limit 1

Then attempt to split up the header record on , to produce a create table statement

ugly.

I would write another perl script that read the header record split the header into its constituent fields guessing the field formats in order to construct a create table statement. Then I would create the table and do the load data using the Perl DBI in the same perl script.

 

[elsenorjose]

Thanks hvass. Only problem I see is that I'd have to recreate the table thus losing all data. I was hoping somehow to leverage MySQL's ability to insert a new column without destroying the data.

 

[hvass]

I miss understood are you looking for something as simple as

alter table mytable add column cprog varchar(255)

 

[elsenorjose]

Yes, that too would work but it's manual. I was hoping to find someway to programatically add a new column to the table if it was found in the comment header of the import file. I think that logically speaking it can't be done in SQL. How would MySQL know how to read the comment header and then add a new column because it saw a new field value? I think I need to ask the engineer to write a Perl script to update the tables that way.