From text file to mysql

Anon

i have a text file that looks like this:

Vic B.
male
21
IT

Jane D.
female
25

John D.
male
23
CEO

about 50,000 records like this, and i need to put them in a mysql database from a text file. some recors have 1 more field that others. can this be done. if so how. i alread have a mysql database with the fields: name, sex, age, position. HELP!

Anon

Hi,
Even i have encountered such problems earlier. One solution could be to put the entries in a ms-excel sheet and save it as a file that is \'tab delimited\' one. Then put the file on to the server. the move to mysql where u say

sql>load data infile \'<path where the tab file is situated>\' into table <table name>;

for further details u download the documentation of mysql from mysql.com. They have given provisions for putting the contents of a text file toa database....
Try it out. All the best...
ganesh

Anon

In this case, using excel is not required.
The data is already in a format that is acceptable to mysql, you only have to tell mysql what the format is.

use this statement:

load data infile 'filename.txt'
into table your_table
fields terminated by "\n"
lines terminated by "\n\n";

the "\n\n" is the empty line between records.

Anon

there is another stupid way to solve the problem too i fyou want to share with...is to use mysqlODBC for windows, then use the MSaccess to connect a ODBC link to the linux mysql server, from there you can do anything to manage your mysql database server...

Anon

another problem, not all fields have data. also, notice the some fields extend two lines, but there is no text wrap, that's a hard return

BD171.A24 c: 001
Title: Six great ideas : truth, goodness, beauty, liberty, equality, justice
: ideas we judge by, ideas we act on / Mortimer J. Adler.
Adler, Mortimer Jerome, 1902-
New York : Macmillan, c1981.
xii, 243 p. ; 24 cm.
Subjects: TRUTH. GOOD AND EVIL. AESTHETICS. JUSTICE (PHILOSOPHY) EQUALITY.
LIBERTY.

BF 81 .W35 1971 c: 002
Title: THE GREAT PSYCHOLOGISTS.
WATSON, ROBERT IRVING, 1909-
PHILADELPHIA : J.B. LIPPINCOTT COMPANY, c1971. THIRD EDITION.
627P. INCLUDES INDEXES.
Subjects: PSYCHOLOGY - HISTORY.

BF121.V5 c: 001
Title: Foundations of psychology [by] W. Edgar Vinacke.
Vinacke, William Edgar, 1917-
[New York] : American Book Co. [1968].
xvi, 847 p. illus. (part col.) 24 cm.
Subjects: PSYCHOLOGY - 1965-
Notes: Bibliography: p. 753-783.

BF149.V56 1968 c: 001
Title: Readings in general psychology [by] W. Edgar Vinacke.
Vinacke, William Edgar, 1917- comp.
[New York] : American Book Co. [1968].
xi, 560 p. illus. 24 cm.
Subjects: PSYCHOLOGY - ADDRESSES, ESSAYS, LECTURES.
Notes: Includes bibliographical references.

will this still work?

Anon

Nope.

If some fields sometimes span multiple lines, and some fields are sometimes empty, there is no easy way of finding the end of a record.

Now all you can do is find other ways of finding the field delimiters.

In the example you gave, you might be able to read the file, and
look for lines starting with "BF" that might be your first field.
Then you could look for a line starting with "Title" for the second field.

If you read a line that does not match any of those words, it must belong to the last field.
And if you find another line that begins with "BF", that means the last record is complete.

Quite a nasty way to do it, but it might work....