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

  • Need help pulling SQL queries from the server into Excel

    I'd like to be able to pull SQL queries that I do on the server into Excel. Is this done through an ODBC connection? I've never set up an ODBC connection before. What're the steps I need to take to get this setup? Were on Shoretel 14.2


  • #2
    Have you tried directly querying the database from Excel using the Data tab, get data, Database, SQL? You might be able to skip the ODBC portion. If you really want to setup ODBC it is done via the Windows Control Panel. I know this doesn’t completely answe your question but hopefully it helps a little.


    • #3
      SQLyog (community edition) has a copy option that sets the data from your query up in a format that is appropriate to paste into excel. Are you looking to do this programmatically or would a manual export like that work?


      • #4
        I'd like to not have to paste it every time into Excel. I would like it to just be there if possible.


        • #5
          I know I'm late to the party, but if you still need ideas (or someone else is looking for a solution to this problem), there are several ways. First you'll need to install the appropriate MySQL drivers for the method you choose. Here is what I've got installed and I'll explain each:

          The MySQL Connector Net 6.9.9 is for connecting to the database through .Net (I use Powershell). A newer version might work, but this is what I've been using.
          The MySQL Connector/ODBC 5.1 are 32 and 64 bit ODBC drivers. You'll need the one that matches the bit-depth of your installed version of Excel if you want to connect directly from Excel. I've not been able to get a newer version to work with the shorewarecdr database.
          The MySQL Connector/ODBC 8.0 is for something else.
          The MySQL Workbench 6.3 CE is for testing/developing your SQL query. (I find it easier to use than the query editor built in to Excel)
          I've not been able to get a newer version to work with the shorewarecdr database.

          My preferred method is using powershell for the final product. I can do more with the results including scheduling it, creating an Excel file, and emailing it to someone.
          Here is an example of a report I create to see how many times calls to our Customer Service queue are forwarded to another queue because the CS agents are all busy. I can schedule this to run daily to get the information for the previous day.

          The CSBusyRolloverAnonymized.txt file is a powershell script that grabs the info from the shorewarecdr database and dumps it to a CSV file.

          The 2xl.txt file is a powershell script that converts that into a well formatted Excel spreadsheet for emailing to the person who requested it.

          These can be run manually or called from a third script or cmd file that can be scheduled.

          If anything here looks like it might be useful, but I've been unclear, please let me know and I'll try to explain better.
          Attached Files
          Last edited by Flint; 07-15-2019, 11:41 AM.