Trouble to import data to excel from microsoft query

  • Hi guys,

    I'm having trouble to import data from microsoft query into the excel. After writing a big query and executing it successfully, I need to import this query to excel in order to send the data to my boss. However, this time I changed the way I set up my query and excel does not like to export data with this type of code. This is the code I think making the problem for excel to export data.

    DECLARE @current_month INT;

    DECLARE @previous_month INT;

    DECLARE @previous_2_months INT;

    DECLARE @previous_3_months INT;

    SET @current_month =CAST(DATEPART(m,getdate())as INT);

    SET @previous_month =CAST(DATEPART(m, DATEADD(m, -1, getdate()))as INT);

    SET @previous_2_months =CAST(DATEPART(m, DATEADD(m, -2, getdate())) as INT);

    SET @previous_3_months =CAST(DATEPART(m, DATEADD(m, -3, getdate())) as INT);

    I have tried the case that what if I do not use Declare and set for datetime . Excel was able to successfully export the data. For example.

    Before I change I used this :

    DECLARE @current_month INT;

    DECLARE @previous_month INT;

    DECLARE @previous_2_months INT;

    DECLARE @previous_3_months INT;

    SET @current_month =CAST(DATEPART(m,getdate())as INT);

    SET @previous_month =CAST(DATEPART(m, DATEADD(m, -1, getdate()))as INT);

    SET @previous_2_months =CAST(DATEPART(m, DATEADD(m, -2, getdate())) as INT);

    SET @previous_3_months =CAST(DATEPART(m, DATEADD(m, -3, getdate())) as INT);

    SELECT

    [branch_id]+' - '+[branch_description] as branch

    ,COALESCE(p21_view_oe_hdr.class_2id,'STORE') as invoice_status

    ,@current_month as current_period

    ,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_price] ELSE 0 END) as current_month_sales

    This is how I changed:

    SELECT

    [branch_id]+' - '+[branch_description] as branch

    ,COALESCE(p21_view_oe_hdr.class_2id,'STORE') as invoice_status

    ,@current_month as current_period

    ,SUM(CASE WHEN [p21_sales_history_report_view].period = CAST(DATEPART(m,getdate())as INT THEN [detail_price] ELSE 0 END) as current_month_sales

    and I had to delete the "Declare" and "Set" time as the begginning.

    As the conclusion, I believe the problem comes from Declare and Set function. In order to keep it easy to read the code in SQL, declare and set function are very necessary for me. If you guys know how to fix this problem by keeping the code "before I changed". Please let me know, I really appreaciate it. As I researched for hours online and could not find an absolute solution for this.

  • Personally, I would create a stored procedure and call that from Excel. That way, the user cannot change the query and you can use all of the SQL features.

    Jared
    CE - Microsoft

  • Do you know how to create a procedure for the query? I do it on Excel or SQL

  • Use google and search for SQL Server CREATE PROCEDURE

    You will create a stored procedure in SQL Server that contains all of the code needed. Then Excel will simply execute that stored procedure.

    http://blogs.office.com/b/microsoft-excel/archive/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba.aspx

    Jared
    CE - Microsoft

  • Thanks it works.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply