Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2013
    Posts
    6

    Unanswered: Save MySQL query results into a text or CSV file

    I found this topic from this link:
    Save MySQL query results into a text or CSV file | a Tech-Recipes Tutorial

    I am try to create the text file from query results but it didn't work and got this error: "Incorrect syntax near the keyword 'INTO'.

    Does any know? What should i do? - thx

    SELECT sale, del
    FROM order
    INTO OUTFILE 'C:/tmp/orders.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You were close! See MySQL :: MySQL 5.0 Reference Manual :: 13.2.8.1 SELECT ... INTO Syntax for the details, but:
    Code:
    SELECT sale, del
       INTO OUTFILE 'C:/tmp/orders.csv'
       FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       FROM order ;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    6
    thanks, i try this but sitll got error: Incorrect syntax near 'C:/tmp/orders.csv'

    Does this file will can be saved into my C drive or can it be save into any specific location from the network?

    thanks
    Last edited by kt_kn; 04-19-13 at 18:18.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Show us the query and the actual message returned. Also where are you running this query from e.g. mysql utility, phpMyAdmin?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there order table?
    Are there sale, del columns on the table?

    Did the query
    SELECT sale, del
    FROM order
    worked?

  6. #6
    Join Date
    Apr 2013
    Posts
    6
    the actual query is
    SELECT saleOrder, Del_date, Quantiy, Item_code, Description
    INTO OUTFILE 'C:/tmp/orders.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM Orders ;

    error: Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'C:/tmp/orders.csv'.

    i am runing this query from the Query Analyzer
    in Microsof SQL Server Management Studio

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the results from running:
    Code:
    SELECT ServerProperty('ProductVersion')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2013
    Posts
    6
    what it is?, the result is 10.50.2500.0

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, so you are running on a Microsoft SQL Server instead of on a MySQL Server!

    Run your query:
    Code:
    SELECT saleOrder, Del_date, Quantiy, Item_code, Description
       FROM Orders ;
    1. Right click on the results pane.
    2. Click on Save Resultset As in the menu that appears
    3. Type C:/tmp/orders.csv as the filename
    4. Click the Save button


    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Apr 2013
    Posts
    6
    this is manually proceses, but i am looking for the way to automatic run this file in specifc time each date and save the results in this location?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The simplest complete solution is to use PowerShell. PowerShell incorporates everything that you might need or want in a single, tidy, well supported environment.

    A simpler but somewhat limited and possibly challenging to format to your tastes solution might be to use BCP. There is an excellent article by Nigel Rivet that can help you get started.

    Another possible solution that is simpler than PowerShell but more flexible than BCP would be SQLCMD. I would only recommend learning and using SQLCMD this way if neither of the above solutions satisfies your requirements... It is a great tool, but it has a significant learning curve and the other two solutions seem like they would be better fits for your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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