Results 1 to 5 of 5
  1. #1
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Space Delimited File Question
    I am working on a script and wanted to know if anyone knows how to create/export a mySQL DB table to a space delimited file? Thanks!
    Thanks,
    Doug

  2. #2
    ABW Ambassador I.M.O.G.'s Avatar
    Join Date
    February 19th, 2011
    Location
    Rootstown, OH
    Posts
    1,096
    Looks like something of this sort should do the trick. In the last line, "users" would be the table name you want to export from, the select statement in the first line would specify the columns you are interested in exporting.

    Code:
    SELECT id, name, email INTO OUTFILE '/wherever/export.txt'
    FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY ‘\\’
    LINES TERMINATED BY '\n'
    FROM users WHERE 1
    Last edited by I.M.O.G.; July 5th, 2011 at 01:23 PM.
    Matt Bidinger
    Online Community Engagement

  3. #3
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Thanks for the help. It looks like it worked! Is there a way to get a CR at the end to the last field for each record set? TIA
    Thanks,
    Doug

  4. #4
    ABW Ambassador I.M.O.G.'s Avatar
    Join Date
    February 19th, 2011
    Location
    Rootstown, OH
    Posts
    1,096
    I'm not clear on what you are asking on the followup question, however each row exported should be on a new line with the code I suggested - thats the "LINES TERMINATED BY '\n'" part of the code.

    Once the data is exported, I would handle massaging, layout, or other tweaks to the raw contents via a separate script or utility. When working with DBs though, I generally limit putting things in or taking things out very strictly to just that - KISS principle to prevent mangling anything.

    Anything else I want to do with the data, I keep segregated to the timeframe before you insert or after you export. If you do some linux stuff, sed should be able to do what you want fairly simply. I'm not that good with sed, awk, and regexp, but with that triumvirate, you can do most any text file manipulation very quickly.
    Matt Bidinger
    Online Community Engagement

  5. #5
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    I had to had '\r' to the LINES TERMINATED BY '\n' as I am viewing this on a windows box but you were dead on! Thanks so much for the help and quick response!
    Thanks,
    Doug

  6. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. data feed text file name question.
    By steven420 in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: August 23rd, 2006, 08:40 PM
  2. CSV/Excel to Space Delimited
    By Doug247 in forum Midnight Cafe'
    Replies: 5
    Last Post: December 12th, 2005, 04:52 PM
  3. Disk Space Question
    By leeann in forum Midnight Cafe'
    Replies: 8
    Last Post: June 27th, 2005, 06:10 PM
  4. newbie export file question
    By bposner in forum WebMerge (Fourthworld.com)
    Replies: 5
    Last Post: December 4th, 2004, 03:10 PM
  5. robots.txt file question
    By Doc Sawyer in forum Search Engine Optimization
    Replies: 4
    Last Post: December 7th, 2002, 07:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •