load data assistance

hi,
I have comma seperated data in a file as below,

king, 1000, male, 30
shilpa, 5000, female, 28
john, 4000, male, 34

i would like to selectively load lines which have third column as
'male' into a table, how do i do this using "load data". There is no
where clause in "load data" command syntax.

tia

devasura@gmail.com wrote:
> hi,
> I have comma seperated data in a file as below,
>
> king, 1000, male, 30
> shilpa, 5000, female, 28
> john, 4000, male, 34
>
> i would like to selectively load lines which have third column as
> 'male' into a table, how do i do this using "load data". There is no
> where clause in "load data" command syntax.
>
> tia

why not just load everything and then select create table?

> > tiawhy not just load everything and then select create table?

if i have a lot of data then half the entries would be invalid. In your
case i have to load all the data then delete unwanted by using

delect from table1 where gender = 'female';

I want to selectively load the lines to minimise the load on the server

tia

devasura@gmail.com wrote:

> > > tiawhy not just load everything and then select create table?
>
> if i have a lot of data then half the entries would be invalid. In your
> case i have to load all the data then delete unwanted by using
>
> delect from table1 where gender = 'female';
>
> I want to selectively load the lines to minimise the load on the server
>
> tia
>


windows or unix? there may be a way...

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

devasura@gmail.com wrote:
> > > tiawhy not just load everything and then select create table?
>
> if i have a lot of data then half the entries would be invalid. In your
> case i have to load all the data then delete unwanted by using
>
> delect from table1 where gender = 'female';
>
> I want to selectively load the lines to minimise the load on the server
>
> tia
>

If this is an INNODB table, you might be able to create a foreign key on
the column to another table with just 'male' in it.

Or you can write a program in a language such as Perl, PHP, C, etc.,
which can do the filtering.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

so the only option is to use text filters like grep in linux.
what about windows?

tia

devasura@gmail.com wrote:

> > > tiawhy not just load everything and then select create table?
>
> if i have a lot of data then half the entries would be invalid. In your
> case i have to load all the data then delete unwanted by using
>
> delect from table1 where gender = 'female';
>
> I want to selectively load the lines to minimise the load on the server
>
> tia
>

Unless you are doing ten-thousands of records at a time and you do it more than
100 times a day, you are not going to touch the load on the server...

you could:
(gets all records NOT containing female - because female does contain the word
male).

find /I/V "female" infile > outfile
load outfile
delete outfile

for UNIX/Linux:

grep -iv female infile > outfile
load the file
rm outfile

the odds of having names with the word "female" and in it are remote.
--
Michael Austin.
Database Consultant

<devasura@gmail.com> wrote in message
news:1165071356.693049.28780@l12g2000cwl.googlegro ups.com...
>
> so the only option is to use text filters like grep in linux.
> what about windows?


F:>type file
king, 1000, male, 30
shilpa, 5000, female, 28
john, 4000, male, 34

F:>findstr /R "\<male\>" file > newfile

F:>type newfile
king, 1000, male, 30
john, 4000, male, 34


With awk you'll be more precise (you can get a Windows port):

$ awk '$3 == " male"' FS="," file
king, 1000, male, 30
john, 4000, male, 34


Regards
Dimitre

On 2 Dec 2006 06:55:56 -0800, devasura@gmail.com wrote:

>so the only option is to use text filters like grep in linux.
>what about windows?

grep, gawk, find, ...

>tia
--
( Kees
)
c[_] If only women came with pulldown menus and online help. (#418)