How load a tab delimited file into my MySQL database via PHPMyAdmin?

Author Name Status Posted Time Larry Coons 10/29/2008 3:57:17 AM Author is Offline Hey, 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 h ere): Code: 0S 0s 1 0 100000S 100000s 1 100000 1000S 1000s 1 1000 100S 100s 1 100 10S 10s 1 10 11S 11s 1 11Location 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. Any Help? Larry< /p>

Subject Author Status Date Re: How load a tab delimited file into my MySQL da tabase via PHPMyAdmin? Indira Sivan 10/30/2008 4:13:54 AM Hi Larry, 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 Post Comments Re: I'm nearly there now Larry Coons 10/30/2008 4:15:56 AM Indira, Thanks anyway. I'm nearly there now. TAB is \t and my line terminator is wrong. Think it's \r only. Trying now...   Post Comments Re: How load a tab delimited file into my MySQL da tabase via PHPMyAdmin? August Buell 10/30/2008 4:15:07 AM Hi Larry, 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?   Post Comments Re: I found a nice script Larry Coons 10/30/2008 4:17:36 AM August, 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. Post Comments Re: Good job, glad to hear it. August Buell 10/30/2008 4:18:54 AM Good job, glad to hear it. Post Comments

Related Forums Optimizing Images Get a Website Template for Free!!! Get a Website Template for Free!!! Do You Sell Your Templates on Ebay? Template Tutorials