Announcement

Collapse
No announcement yet.
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql script help...I think it's probably an easy question

    I have this script I want to automate but I don't know how to use the CLI programs. I just want to run this and send the [B]comma-delimited[B] data to, say, c:\output.csv

    The script's name is sysdir.sql and the contents are:
    SELECT
    `DN`
    , `Description`
    FROM
    `shoreware`.`dn`
    WHERE (`ExcludeFromDialByName` <> 1
    AND `DNTypeID` = 1);
    ----

    What command line script would accomplish what I'm trying to do? I don't see a comma in the script above so I'm guessing I need to adjust that as well.

    Thanks in advance!!

  • #2
    SELECT * FROM tableINTO OUTFILE '/output/path'
    FIELDS TERMINATED BY '\t\' ->LINES TERMINATED BY '\n';

    That should do ya. If you can get the MySQL Workbench onto the server, it has a built-in export to CSV/Excel. I know that isn't always practical though.

    Comment


    • #3
      Ok I have no idea where to plug in those two lines. I already have a SELECT so I ignored the first part of that line, but the INTO was not recognized by Workbench.

      That FIELDS line I tried in a couple places and it wasn't recognized either.

      Workbench is not for the feint of heart. Apparently you must know SQL to do anything. I like SQLyog because even after just a few minutes I could actually create a query because it has a reporting function. If Workbench does, I haven't found that yet.

      Once I do have a script that works, what would the .bat file look like?

      Comment


      • #4
        Echo select UserDN, GuiLoginName from shoreware.users where AllowSoftPhone = '1' order by UserDN; >>"%tmp%\sql.sql"
        "c:\\Program Files\\Shoreline Communications\\Shoreware Server\\MySQL\\MySQL Server 5.0\\bin\\mysql.exe" --user root --password=shorewaredba --database=shoreware <"%tmp%\sql.sql" >"%HOMEDRIVE%\%HOMEPATH%\Desktop\Users.txt"
        del "%tmp%\sql.sql"

        Comment


        • #5
          I convereted that to my use:

          Echo select DN, Description from shoreware.dn where (ExcludeFromDialByName = '0' AND `DNTypeID` = 1) order by DN; >>"%tmp%\sql.sql"
          "c:\\Program Files\\Shoreline Communications\\Shoreware Server\\MySQL\\MySQL Server 5.0\\bin\\mysql.exe" --user root --password=shorewaredba --database=shoreware <"%tmp%\sql.sql" >"%HOMEDRIVE%\%HOMEPATH%\Desktop\Users.txt"
          del "%tmp%\sql.sql"

          And it works great! The only thing left for me to figure out is how to get that into comma-delimited. I'm googling that now but if anyone knows and wants to beat me to the answer, my pride won't be hurt! LOL

          Comment

          Working...
          X