dbeaver

@export in DBeaver to xlsx

While having the following export command for Oracle script

@export {"type": "xlsx", "producer": {"extractType": "SINGLE_QUERY", "segmentSize": "110000", "fetchSize": "15000", "openNewConnections": "true", "queryRowCount": "false"}, "consumer":{"outputFolder": "C:\TEST", "outputFilePattern": "app_ao_${timestamp}", "outputTimestampPattern": "yyyyMMdd"}, "processor": {"border": "NONE", "dateFormat": "dd-mm-yy"} }

I keep getting the following error

Invalid syntax. Use '@export {"type": <type>, "producer": {...}}, "consumer": {...}}, "processor": {...}}'
  com.google.gson.stream.MalformedJsonException: Invalid escape sequence at line 1 column 199 path $..outputFolder
See https://github.com/google/gson/blob/main/Troubleshooting.md#malformed-json
  com.google.gson.stream.MalformedJsonException: Invalid escape sequence at line 1 column 199 path $..outputFolder
See https://github.com/google/gson/blob/main/Troubleshooting.md#malformed-json
    Invalid escape sequence at line 1 column 199 path $..outputFolder
See https://github.com/google/gson/blob/main/Troubleshooting.md#malformed-json
    Invalid escape sequence at line 1 column 199 path $..outputFolder
See https://github.com/google/gson/blob/main/Troubleshooting.md#malformed-json

Mentioned links to github do not help.

If I change C:\TEST to C:/TEST it goes through task steps but I have to change it manually to C:\TEST here: general settings

Why is it?

I am using DBeaver Version 25.0.3.202504201842

Solution

  • Why DBeaver does this:

    1. Confirmation/Review: DBeaver aims to provide a user-friendly experience. Even with a script, it wants to give you a chance to review all settings, especially critical ones like output folder, file name, and data processing options, before a potentially large export begins.

    2. Missing or Conflicting Parameters: While your producer settings might be complete, there could be other settings (in consumer, processor, or even general DBeaver preferences) that aren't fully covered by the @export command, or that DBeaver wants to confirm.

    3. GUI vs. Task Execution: The @export command in the SQL editor is primarily designed to launch the wizard with pre-populated values. For truly automated, headless exports without any GUI interaction, DBeaver uses a different mechanism: Data Transfer Tasks.

    How to achieve a truly headless export (no wizard prompt):

    If your goal is to run an export completely without manual intervention (i.e., without DBeaver opening the wizard and asking you to click through it), you need to use DBeaver Data Transfer Tasks.

    Here's the general process:

    1. Define your Query: Write your SQL query that you want to export.

    2. Create a New Data Transfer Task:

      • Go to File > New > Task.

      • Select Data Transfer.

      • Click Next.

    3. Configure the Source:

      • Choose your Connection.

      • Select SQL query as the source.

      • Paste your SQL query directly into the SQL query field.

    4. Configure the Producer/Extraction Settings:

      • Go to the Extraction tab in the task editor.

      • Here, you will find the GUI equivalents of your producer settings:

        • Extract type: Choose "Single query" or "Segments"

        • Segment size: Enter 110000

        • Fetch size: Enter 15000

        • Open new connections: Check this box.

        • Query row count: Uncheck this box (since you had false).

    5. Configure the Consumer/Output Settings:

      • Go to the Output tab.

      • Format: Choose "XLSX".

      • Output directory: Enter C:\TEST (you can use backslashes here, DBeaver's GUI handles it).

      • Output file pattern: Enter app_ao_${timestamp}.

      • Output timestamp pattern: Enter yyyyMMdd.

      • Configure any other desired settings (e.g., encoding, append).

    6. Configure the Processor/Format Settings:

      • Go to the Format tab.

      • Border: Choose "None".

      • Date format: Enter dd-mm-yy.

    7. Save the Task: Give your task a meaningful name.

    8. Run the Task:

      • You can run the task directly from the DBeaver Task view (usually found under Window > Show View > Tasks).

      • Right-click on your saved task and select Execute.

    Benefits of using Data Transfer Tasks: