How To Load Tab Delimited File

[T0PS3O]

I want to load a tab delimited file into my MySQL database via PHPMyAdmin. I'm trying to use the "Insert data from a textfile into table" function but it won't work. It stops after one row which goes completely wrong.

Sample from my .txt file (hope it parses alright here):

0S 0s 1 0
100000S 100000s 1 100000
1000S 1000s 1 1000
100S 100s 1 100
10S 10s 1 10
11S 11s 1 11

Location of the textfile = obvious
Replace table data with file = obvious
Fields terminated by = 'nothing' I guess
Fields enclosed by = 'nothing' I guess
Fields escaped by = not applicaple
Lines terminated by = \r\n I guess
Column names = n/a leaving it blank
LOAD method = DATA LOCAL

With the options I've tried it either loads each line into one field terminating after 4 fields which is one row. Or it puts the entire line into the first field and terminates after one row.

I don't know what to put in Field Terminated By since it's a tab and I don't know how to specify that.

Help appreciated.

[DangerMouse]

tops, you might have to do some funky 'find and replace' on that string... break out excel?

MSSql Server deals with these imports very nicely... Not so sure MySQL has a straight forward solution to it though.. not much help :( sorry

[T0PS3O]

Thanks anyway. I'm nearly there now. TAB is \t and my line terminator is wrong. Think it's \r only. Trying now...

[noppid]

Read the file with line I/O into an array.

Then parse each element of the array(each line) with explode.

$exploded[0] $exploded [1] etc make up each data element for the insert.

Opps, not php...

You need to define a column for each data element across the line horizonatally. I thought, without looking, delimiter was a checkbox? :idunno:

[T0PS3O]

I found a nice script once which does this exlpoding and inserting for you but lost it. This LOAD business has always been cumbersome but it looks like I've cracked it not (can't tell quite yet though, large files!).

UPDATE: Done! \t for field delimiter and \n linefeed for row delimiter did the trick.

[noppid]

Good job, glad to hear it.