1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How To Load Tab Delimited File

Discussion in 'MySQL' started by T0PS3O, May 10, 2005.

  1. #1
    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.
    SEMrush
    Help appreciated.
     
    T0PS3O, May 10, 2005 IP
    SEMrush
  2. DangerMouse

    DangerMouse Peon

    Messages:
    275
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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
     
    DangerMouse, May 10, 2005 IP
    T0PS3O likes this.
  3. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks anyway. I'm nearly there now. TAB is \t and my line terminator is wrong. Think it's \r only. Trying now...
     
    T0PS3O, May 10, 2005 IP
  4. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #4
    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:
     
    noppid, May 10, 2005 IP
    T0PS3O likes this.
  5. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    T0PS3O, May 10, 2005 IP
  6. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #6
    Good job, glad to hear it.
     
    noppid, May 10, 2005 IP