Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2001
    Posts
    24

    Unanswered: Export MySql table to text-file

    Hello,

    I need to do an export of MySQl-tables to text-files each month. How can I do this? It needs to be done automatically the 15th of each month. So, I can't use the wizard. Any ideas?

    Thx a lot!

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: Export MySql table to text-file

    Originally posted by Green4Ever
    Hello,

    I need to do an export of MySQl-tables to text-files each month. How can I do this? It needs to be done automatically the 15th of each month. So, I can't use the wizard. Any ideas?

    Thx a lot!
    Its quite simple as follows:

    Create a batch file that runs mysql command line to export the table to text file and then have a scheduler run it (batch file)

    Note: use SELECT ... INTO OUTFILE ...

    hope this helps

    qha_vn

  3. #3
    Join Date
    Dec 2001
    Posts
    24
    Ok, but how can I run a mysql-command from a batch-file? Can you give me an example?

  4. #4
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by Green4Ever
    Ok, but how can I run a mysql-command from a batch-file? Can you give me an example?
    can you let me know:

    1-mysql server version
    2-operating system

    i'll give you an example
    qha_vn

  5. #5
    Join Date
    Dec 2001
    Posts
    24
    mysql server version: 4.0.5-beta-max-nt
    operating system: NT 4.0

  6. #6
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    ok, mysql have a command line utility named mysql.exe that helps us to run any pre-defined sql statements (in form of a text file)

    How? Provided that mysql server folder is "C:\mysql", use notepad to create a file (i.e. runsql.bat) that contains only one row:

    c:\mysql\bin\mysql <c:/statemnt.sql> output.tab

    make sure you enter the slash exactly the same as the above row.

    The text file "c:\statemnt.sql" contains all the needed sql statements to export your tables into a text file. please refer to this link for the syntax: http://www.mysql.com/doc/en/SELECT.html

    example of statemnt.sql:
    --------------------
    use test_database;
    SELECT field1, field2, field3 INTO OUTFILE "/tmp/result.text"
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY "\n"
    FROM test_table;
    --------------------

    test running the file runsql.bat from your command line until you get the right output and then have a scheduler run it for you at 15th monthly

    try it and you will surely be happy
    qha_vn

  7. #7
    Join Date
    Dec 2001
    Posts
    24
    It's working. Thx for all your help!

  8. #8
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by Green4Ever
    It's working. Thx for all your help!
    i am rewarded hearing that.
    qha_vn

  9. #9
    Join Date
    Oct 2003
    Posts
    1
    Originally posted by qha_vn
    i am rewarded hearing that.

    HI,

    I'm using mysql on a linux based machine. I'm trying to create a script that will export all tables within a certain database to text files(one for each table). I understand that you can output each table individually by using:

    select from <table name> into outfile '</path/filename'>;

    Any suggestions on how I may automate the whole process within a script? Perhaps using a counter till end of database..etc...?

    Thanks in advance

  10. #10
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Sorry i dont know linux then. can anyone out there helps him? an interesting question actually.
    qha_vn

  11. #11
    Join Date
    Oct 2003
    Posts
    10
    It may be possible using Perl with DBI::Perl.

    Maybe do a "show tables" on that database and using perl to loop through the results and execute a "mysqldump -u <user> -p<pass> <database> <table> > <file>" for each row?

    Thats the direction I would look into.

  12. #12
    Join Date
    May 2003
    Posts
    87

    create insert statements

    And is it possible to create insert statements for the table? I want to move some MySQL data to some other database and hence wanted insert statements created so that I can run these against the different database.

    Thanks.

  13. #13
    Join Date
    Oct 2003
    Posts
    706

    Red face

    How about the obvious?

    How about "mysqldump?" X-)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  14. #14
    Join Date
    May 2003
    Posts
    87
    wow! didn't know about that. thanks :-)

Posting Permissions

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