creating data base tables from text file data

i have text files generated from another software about genes in human body, i need to insert them to a table and make the table as i need , i have 15 different 开发者_Go百科text files that goes in to one table, as 15 different columns.

GFER = 3.58982863
BPIL1 = 3.58982863
BTBD1 = 4.51464898
BTBD2 = 4.40934218
RPLP1 = 3.57462687
PDHA1 = 4.19320066
LRRC7 = 4.50967385
HIGD1A = 4.46876727

above shown is the data in the text file, gene name and the distances. i need to include this in a table, gene name in a separate column and distance in a separate column, this text file have 3500 lines and i have 14 text files of data, how can i enter this data to a table without manually inserting?any automated software or tool you know? please help me out!

regards, Rangana

The mysqlimport command ought to load it directly, http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html if you use a little trick to tell it that the = sign is the field delimiter.

shell> mysqlimport blah blah --fields-terminated-by==

If that does not work, write yourself a little routine to read the file, split on = sign, and replace it with a comma or something closer to what mysqlimport wants to see as a default.

You need an import wizard, can't say I've personally used one with mysql (but have with other DBMS) a quick google shows this might be what you need. I have a feeling phpMyAdmin used to have a feature that did this.

First create the table as something like:

mysql> create table gene (name varchar(10), distance double);

and then import a file:

mysql> load data infile '/tmp/gene.txt' into table gene columns terminated by ' = ';

The file needs to be in a place that is accessible to the user under which the mysql executable is running.

You can also use mysqlimport from outside the mysql shell. It connects to the server and issues the equivalent load data infile command.

I tested the above with your sample data and it worked.

i have 15 different text files that goes in to one table, as 15 different columns.

Do you mean 30 columns? 2 columns loaded from each file?

You may have to use = (with spaces on both sides as the delimiter). And as Ken said, if that doesn't do it, search and replace " = " to just a comma ",".

If you have SSIS this can be done fairly quick. Set up the 15 input files and map each file to a pair of columns, like:

File1 ... map to ... Column1 & Column2
File2 ... map to ... Column3 & Column4
etc

Or you can combine the 15 files (can be done easily using Excel) into 1 file with 30 columns and load it in.

i have done it, it may seems odd, but i'm adding here for some one to learn if it is valuable, i have opened those data files using open office spreadsheet, open office has this amazing features of separating the data file in to different columns. so i used them and separated my data files columns and saved it as a excel file(.xls) , then using the "sqlmaestro" as suggested by m.edmondson, using that software's importing data as an excel file feature i was able to achieve my task.

thank you all for your valuable answers, they surely add new things to my knowledge! thank you all once again!