how to insert values from a txt file to a db table

[harryos]

hi
I am a beginner with web apps and databases.
i am using mysql as db for a web application(for learning purposes).It
has a table called 'equipment' which has to be populated from values
given in a text file

the table is created using an sql stmt like

create table equipment (
id varchar(256) not null,
title varchar(40) not null,
imageURL varchar(512) not null,
thumbnailURL varchar(512) not null,
shortSummary varchar(80) not null,
summary varchar(4000),
perWeek decimal(10,2) not null,
perDay decimal(10,2) not null,
type varchar(4) not null,
primary key (id)
);

The text file containing the values is like

id:000-KONRAD
titleolish Barbarian
summary:Assassin
pic:konrad.jpg
week:300.00
day:45.00
type:g_a
Born in Poland, 1978. Mentally unstable.
FSCK
id:000-TOM
title:American CS Intelligience Officer
pic:tom.jpg
summary:Too much coffee
week:300.00
day:40.00
type:g_a
<h3>Database God</h3>
FSCK
....
....

there are about 100 such sets(beginning with id and ending with the
FSCK string.How do i use this text file to insert values into the
table?
thanks
harry

 

[charlesfr.rey]

hi
I am a beginner with web apps and databases.
i am using mysql as db for a web application(for learning purposes).It
has a table called 'equipment' which has to be populated from values
given in a text file

Well, since you've posted on a forum about Java programming, here what
I'd do in Java:

1. Connect to the database via JDBC (see
http://www.stardeveloper.com/articles/display.html?article=2003090401&page=1),
parse the text file, produce the related INSERT statements, end of
story.

2. Or you could write a Java program that just parse your text file
and produce the corresponding SQL, then use the "mysql" command line
to execute your SQL file.

Anyway, what I've just proposed doesn't depend on Java and you could
use any other programming language in a similar way.

 

[Arne Vajhøj]

I am a beginner with web apps and databases.
i am using mysql as db for a web application(for learning purposes).It
has a table called 'equipment' which has to be populated from values
given in a text file

the table is created using an sql stmt like

create table equipment (
id varchar(256) not null,
title varchar(40) not null,
imageURL varchar(512) not null,
thumbnailURL varchar(512) not null,
shortSummary varchar(80) not null,
summary varchar(4000),
perWeek decimal(10,2) not null,
perDay decimal(10,2) not null,
type varchar(4) not null,
primary key (id)
);

The text file containing the values is like

id:000-KONRAD
titleolish Barbarian
summary:Assassin
pic:konrad.jpg
week:300.00
day:45.00
type:g_a
Born in Poland, 1978. Mentally unstable.
FSCK
id:000-TOM
title:American CS Intelligience Officer
pic:tom.jpg
summary:Too much coffee
week:300.00
day:40.00
type:g_a
<h3>Database God</h3>
FSCK
...
...

there are about 100 such sets(beginning with id and ending with the
FSCK string.How do i use this text file to insert values into the
table?

I don't think MySQL's LOAD DATA INFILE can do it, so you
need to write a loader program that parses the file based
on the fieldname prefix and INSERT in the database using
PreparedStatement.

Arne