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 Code (markup): 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.
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
Thanks anyway. I'm nearly there now. TAB is \t and my line terminator is wrong. Think it's \r only. Trying now...
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:
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.