Create 'Create Table' statment from text index file

[carriehoff]

Hi all,

I've got an index file that contains the field names and data types for my db.
Originally, I thought the intital setup in mySQL would be sufficient, but the fields in that index file change periodically, leading me to believe I should find a more automated approach of recreating the table every evening. (We receive this index file as a text file every night from our local area MLS via FTP)

I can create the 'CREATE TABLE' statement from the text file using an .asp page, then FTP it to my linux server, connect with Putty, execute from the command line, but that seems like too many steps.

I'm looking for a way to create that 'CREATE TABLE' script from the text index file on the Linux server itself, and execute it via cron every evening, so that when I load the corresponding data file, all of the fields will match up.

Can anybody help? Attached is an example index file.

Thanks,
Carrie

[carlosponti]

you cant create the table from a list in a text file in too simple a away. you will have to write a program that inserts your text file into the database and already have that list a table that fits its definition. you don't really need to re-create the table either, every time you want it filled just truncate the table and re-insert the records. the simplest solution is to leave the table definition in place and write a shell script that parses through the file after truncating the table and inserts the records into that table. if you are re-creating your table only because the data definition changes i.e. more columns or other data type changes then i guess dropping the table and re-creating it is necessary but otherwise just leave the table alone.

ps what does your database need a list updated of the indexes on the database? usually indexes are maintained by the database well at least that is how it works in oracle. the only reason you need the list for typically is for reporting purposes. are those indexes specific to your application and not used by the database for optimization? quite an odd instance for the system to be set up with.

[carriehoff]

I agree! The number of columns should not change, but they have changed (with no notification), which leaves my data in mis-matched fields (that's how I found out there was a problem)

I see no other solution than to destroy and recreate the table every evening, just in case columns are added or deleted, because our server receives the files every evening.

Currently, I load the data file into the MySQL table with a bash script, something like 'Load Data local infile [textfilename] lines terminated by . . . ' and so on.

I was hoping there was a way to write a bash script to manipulate the file containing the column names & data types (see attached index file) to create a text file, containing the create table statement (see attached create table text file).

That's all I want to do, is to create one file from the other, using a bash script. Is it possible?

Thanks,
Carrie

If I could just create that text file, I could have the automation I'm looking for. Is it possible to do that with a bash script?

[carlosponti]

yes that is possible as far as i know. you would write a bash script that parses through the file contents and you could spit out another text file with the create table statement that you could run from a mySQL prompt. as far as the syntax bash scripting is a bit beyond my ability currently. i write code in an oracle database that is why this peaked my curiosity.

i am still curious why there is an index table. are those table indexes used by the application? what kind of added columns are there beyond the example data file you attached? it looked as if you only had 4 columns in the index file but then a lot of columns in the example create table file you sent.

code would be something like this:
declare variables needed
open the file
loop through contents
read first line for column names create the table statement(somehow you will need to figure out the datatypes)
second line then on for data(inserts)
then write the file
exit

bash shell scripts are pretty powerful for writing stuff like this due to all the programs that you can call to help. if you run into problems you can post it in the programming section of the forum.


ps you cant do it one step you will have to generate the create table statement and then generate the inserts to populate the table. but it can be done in the same script

[carriehoff]

i am still curious why there is an index table. are those table indexes used by the application? what kind of added columns are there beyond the example data file you attached? it looked as if you only had 4 columns in the index file but then a lot of columns in the example create table file you sent.

You're right the index file only had 4 columns, but I use the values from the second and third columns, to generate the 'Create Table' statment. I suppose you could say I'm parsing that index file with ASP using the values from the second and third columns of the index file. Obviously, I do not have ASP on my Linux server, so I have to then transfer the file via FTP manually.

Originally, there were 117 fields, but then they added 2 more fields, except I was not notified and then only noticed a problem when trying to load a data file with 119 fields worth of data to a db table containing only 117 fields. (Does that make sense?) I had to start from scratch to get it to line up correctly.

I think the data vendor sends the index and data files every evening just so it's easier for them - maybe they were getting repeated requests for them or something. I contacted them today, they have told me that the index files are not supposed to change and they are looking into it now.

I would still like to be able to generate that 'Create Table' statment from a bash script, though. The logic you posted makes perfect sense, I just haven't worked within Linux or Unix long enough to know how to do it. ASP, however, is no problem, but not really much of a help for what I'm trying to do.

If anyone else can lend a hand on how to parse that index text file from a bash script, please let me know.

Carrie