inserting data to MySQL from a text file

If you are looking for fixed-width the following will be helpful:

"If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are written and read using the ``display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. LINES TERMINATED BY is still used to separate lines. If a line doesn't contain all fields, the rest of the fields will be set to their default values. If you don't have a line terminator, you should set this to ''. In this case the text file must contain all fields for each row. Fixed-row format also affects handling of NULL values; see below. Note that fixed-size format will not work if you are using a multi-byte character set."
--http://www.mysql.com/doc/en/LOAD_DATA.html

Essentially you will have to setup a table that has each column set to the right display width and then LOAD data with the FIELDS info set blank. That being said it would probably be easier to use a tool like Excel to import your fixed width file and save it as tab seperated.

If you are in a Unix environment (or have a DOS or Windows copy of sed) this will work :-

sed -e 's/ /|/g' -e 's/$/|/' -e 's/^/|/' -e 's/.|/|&/1' -e 's/|.|../&|/' -e 's/|..|../&|/' -e 's/|..|..|..|../&|/'  {filename} > {fn2}

It is a bit terse (sed is) but quicker than Excel or most other languages.  I have tried this with GNU sed and SCO (POSIX and SVID) sed.

Regards,  Richard

I don't really get what you are trying to say?

i did a little research.. do you think inserting the data from a text file into one column on a seperate table and then splitting them up would work? but i'm also concerned about the speed of the queries.. query after query would sorta slow it down quite a bit right? especially if it's gonna be on the web?

anyways, you can do that with MS access and then split them up into different columns, useing the mid() function..
if anyone has a better idea, please.... :)


maybe i should state the question a little clearer:::)
all i'm trying to do is get data from a text file..
but my text file doesn't have tabs to seperate the columns.. otherwise it would work fine.. of course..
so.. how do i do that?
any ideas?

oh! and they discovered a new species of frog in india! ha ha!

hey, thanks! it worked! :D
great!