SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member atem451's Avatar
    Join Date
    May 2001
    Location
    Berlin
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    advanced mysqldump-syntax

    Hi Sitepoints.

    I want to dump the content of my table into a textfile in csv-format, but there is too much content to simply write it in a loop into a file (max. executiontime of the script...), so I want to use mysqldump. mysqldump usually writes complete insert-statements into the dumpfile, but I need only certain fields of the table (let's say "name", "telefon" and "email"), seperated by "|", lines terminated by "\n".

    I've seen that mysqldump can do that, I only got problems with the exact syntax.
    Could anybody give me an example-syntax?
    Footbag: Spread the Spirit

  2. #2
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SitePoint Member atem451's Avatar
    Join Date
    May 2001
    Location
    Berlin
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I know now how the Syntax should look look like and i tried it like this:

    mysqldump --host=XXX mydb --tables mytable --tab="." --user="XXX" --fields-terminated-by="|" --lines-terminated-by="\n"

    I thing this should create a file named mytable.txt which contains the csv-formated data from the table, but instead, I get one long string with no line-seperator and a lot of "\N" in the end!

    can anybody please tell me what i do wrong?
    Footbag: Spread the Spirit

  4. #4
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:

    mysqldump --host=XXX mydb --tables mytable --tab="." --user="XXX" --fields-terminated-by="," --lines-terminated-by="\n"

  5. #5
    SitePoint Member atem451's Avatar
    Join Date
    May 2001
    Location
    Berlin
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but why?
    I can't use a ",", I need a "|"
    And it should not make any difference or?
    I just don't understand, why I get all these "\N" and no linebreaks.
    Footbag: Spread the Spirit

  6. #6
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My bad, I thought you wanted the values seperated by a ','. Even though all the values are on the same line are the being seperated by '|' correctly?

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might try \r\n not sure why but I just suggested this to someone yesterday and it worked, so it might be worth a shot.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Member atem451's Avatar
    Join Date
    May 2001
    Location
    Berlin
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I start feeling stupid ....

    I really start feeling stupid but I tried it and it still does not work.

    my code is like this:

    PHP Code:
    $f_sep="|";

    $l_sep="\r\n";

    $sycall="mysqldump --host=$env_db_host $env_db_name --tables $env_survey_table --tab=\".\" --user=\"$env_db_user\" --fields-terminated-by=\"$f_sep\" --lines-terminated-by=\"$l_sep\"";
    $os=system($sycall); 
    I get the results, but there are still no linebreaks, and all empty fields are filled with a \N instead of nothing.

    Any futher hints why it is so???
    Footbag: Spread the Spirit


Bookmarks

Posting Permissions

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