关于Excel Get Data from OBDC功能中无法执行CREATE OR REPLACE TABLE语句的技术咨询

Great question! The core issue here is that Excel's Get Data (Power Query) via ODBC is built primarily for retrieving data (like SELECT queries), not for executing DDL/DML statements (such as CREATE, INSERT, or UPDATE) against your BigQuery dataset. That’s exactly why you’re hitting the "This native database query isn't currently supported" error.

Below are three actionable workarounds to execute your CREATE OR REPLACE TABLE statement directly from Excel:

1. Use VBA to Execute the DDL Statement #

This is the most reliable method, as VBA bypasses Power Query's restrictions and directly interacts with the BigQuery ODBC driver to run non-query statements.

Steps: #

1. Open the VBA Editor: Press Alt + F11 in Excel.
2. Insert a new module: Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste the following code (update the connection string and SQL to match your setup):

Sub RunBigQueryDDL()
    Dim conn As Object
    Dim cmd As Object
    Dim sqlStatement As String
    
    ' Initialize ADODB objects
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    
    ' Replace with your BigQuery ODBC DSN or full connection string
    conn.ConnectionString = "DSN=YourBigQueryDSN;"
    conn.Open
    
    ' Your CREATE OR REPLACE TABLE statement
    sqlStatement = "CREATE OR REPLACE TABLE `your-project.your-dataset.table_A` AS (" & _
                   " SELECT item_a, item_b, item_c" & _
                   " FROM `your-project.your-dataset.table_B`" & _
                   ")"
    
    ' Execute the statement
    cmd.ActiveConnection = conn
    cmd.CommandText = sqlStatement
    cmd.Execute
    
    ' Cleanup
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
    
    MsgBox "Table created/updated successfully!", vbInformation
End Sub

4. Run the macro: Press F5 in the VBA Editor, or assign it to a button in your Excel workbook for easy access.

Note: Ensure your BigQuery account has the necessary permissions (CREATE TABLE and SELECT on the source table) and that your ODBC driver is up-to-date.

2. Try Power Query's "Run SQL Script" (Excel 365+) #

Some recent versions of Excel 365 include a "Run SQL Script" feature in Power Query that may support non-query statements, depending on your ODBC driver's capabilities.

Steps: #

1. Go to the Data tab → Get Data → From Other Sources → Blank Query.
2. In the Power Query Editor, go to the Home tab → Run SQL Script.
3. Paste your CREATE OR REPLACE TABLE statement into the text box, then click OK.

Caveat: This feature isn't guaranteed to work for all ODBC drivers (including BigQuery), so test it first. If it throws an error, stick with the VBA method.

3. Wrap the Logic in a BigQuery Stored Procedure (Indirect Method) #

If you prefer to keep the SQL logic in BigQuery, you can create a stored procedure and call it from Excel.

Step 1: Create the Stored Procedure in BigQuery #

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.create_table_A`()
BEGIN
  CREATE OR REPLACE TABLE `your-project.your-dataset.table_A` AS (
    SELECT item_a, item_b, item_c
    FROM `your-project.your-dataset.table_B`
  );
END;

Step 2: Call the Procedure from Excel #

• Option A: Use the VBA method above, replacing the sqlStatement with CALL your-project.your-dataset.create_table_A();`.
• Option B: Try calling it via Power Query's native SQL (though this may still trigger the same error as before, so VBA is safer).

内容的提问来源于stack exchange，提问作者SJK