Importing text file

[redneck_kiwi]

I am attempting to import data from a text file. There are many records
to be imported and the raw file format is:

"1 |010-5502-502 |16H1-7-3 |CONTACT, ELECTRICAL,
7/8in. |356.0000 |0.0000"
"1 |03-06-1062 |16H3-5-1 |RECEPTACLE,FRE E
HANGING 6 CON.|1.0000 |0.0000"
"1 |03-06-2061 |16H2-8-2 |PLUG, POWER CONNECTOR
|3.0000 |0.0000"
Obviously, a delimiter is the | symbol. How can I do this?

Thanks!

rk

[Bill Karwin]

Re: Importing text file

redneck_kiwi wrote:[color=blue]
> I am attempting to import data from a text file. There are many records
> to be imported and the raw file format is:
>
> "1 |010-5502-502 |16H1-7-3 |CONTACT, ELECTRICAL,
> 7/8in. |356.0000 |0.0000"
> "1 |03-06-1062 |16H3-5-1 |RECEPTACLE,FRE E
> HANGING 6 CON.|1.0000 |0.0000"
> "1 |03-06-2061 |16H2-8-2 |PLUG, POWER CONNECTOR
> |3.0000 |0.0000"
> Obviously, a delimiter is the | symbol. How can I do this?[/color]

See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Something like this might do it (I have not tested this):

LOAD DATA INFILE "inputfile. txt"
INTO TABLE `myTable`
FIELDS TERMINATED BY '|'
LINES STARTING BY '"' TERMINATED BY '"\n';

However, LOAD DATA INFILE doesn't seem to have support for stripping
extra white space. You may get values with extra spaces at the end:
"1 "
"010-5502-502 "
"16H1-7-3 "
etc.

It might be easier and more flexible to write a script in Perl or some
other language to parse the text file and manipulate the values as you
like before inserting them. The script doesn't necessarily need to
connect directly to a database; it could simply output a series of
INSERT statements based on your text input (watch out for ' characters
in your source text causing imbalanced quotes in the SQL statements),
and then you save that output and run it as a script input to the mysql
command-line tool.

Regards,
Bill K.