Bug: Query View - Export to Excel not working on Windows

[darrylthom]

Error:
Export failed: IO Error: Cannot open file "c:\Test\test.xlsx": The system cannot find the file specified.

Might be Windows specific

[luusluus]

I'll look into this first thing.
Before I release I'll also test on windows as well.

[luusluus]

I've managed to build my app on windows and debug it and it seems to be a bug on duckdb's side.

I've tried to run the export without path characters like so:

COPY (SELECT * FROM tbl) TO 'test.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

instead of providing a path like c:\test\test.xlsx and it returned the same error.
I've also upgraded duckdb to the latest version and it returned the same error.

I'll try to do some more testing or reading docs. But if all fails, I'll open an issue at duckdb

[darrylthom]

Does it perhaps need double backslashes to escape in the file path? Or maybe somehow forward slashes might work even though technically Windows uses backslashes. Could also be related to GDAL specifically.

[luusluus]

That's the way how it is in the current version. At least the string in memory would be c:\\Test\\test.xlsx

I've also already tried forward slashes. same error.

Yes, that could be the case, because duckdb doesn't have excel support natively. You have to install the spatial extension within duckdb.

[luusluus]

I have the found the repo where I should report issues to https://github.com/duckdb/duckdb_spatial

Things I have tried:

// savedPath = 'c:\\\\Users\\lucie\\Source\\vscode-parquet-visualizer\\data\\small.xlsx'; // The system cannot find the file specified.
      // savedPath = 'c:\\\\Users\\lucie\\Source\\vscode-parquet-visualizer\\data\\small.xlsx'; // The system cannot find the file specified.
      // savedPath = "c:\\test.xlsx"; The system cannot find the file specified.

      // savedPath = "c://test.xlsx"; The system cannot find the file specified.
      // savedPath = "c:////test.xlsx"; The system cannot find the file specified.
      // savedPath = "c://////test.xlsx"; The system cannot find the file specified.
      // savedPath = "C://test.xlsx"; The system cannot find the file specified.
      // savedPath = "C:////test.xlsx"; The system cannot find the file specified.

      // savedPath = savedPath.replace(/\\/g, '/'); // The system cannot find the file specified
      // savedPath = 'C://test.xlsx.tmp'; // The system cannot find the file specified
      // savedPath = 'C://test.tmp'; // The system cannot find the file specified 

I'll try to reproduce the error by using duckdb CLI on windows.

[luusluus]

In command prompt

duckdb

INSTALL spatial;
LOAD spatial;
 
COPY (SELECT 1 as a, 2 as b) TO 'c://Users//output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
IO Error: GDAL Error (3): Cannot create c://Users//output.xlsx: Failed to open file c://Users//output.xlsx: {"exception_type":"IO","exception_message":"Cannot open file \"c://Users//output.xlsx\": Access is denied.\r\n"}

In command prompt as administrator

duckdb

INSTALL spatial;
LOAD spatial;

COPY (SELECT 1 as a, 2 as b) TO 'c://Users//lucie/output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
COPY (SELECT 1 as a, 2 as b) TO 'c:\Users\lucie\output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
COPY (SELECT 1 as a, 2 as b) TO 'c:\Users\lucie\output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
COPY (SELECT 1 as a, 2 as b) TO 'c://Users//lucie/output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

In admin this seems to work fine.

I haven't been able to reproduce it.

[luusluus]

I've written a python client and ran it in windows

import duckdb

con = duckdb.connect()

con.install_extension("spatial")
con.load_extension("spatial")

con.sql("COPY (SELECT 1 as a, 2 as b) TO 'output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx')")

This seems to work fine.

[luusluus]

I've also written a typescript client and ran it in windows

import * as duckdb from "duckdb";


async function exportExcel () {
    const db = new duckdb.Database(":memory:");
    
    db.all(`
        INSTALL spatial; LOAD spatial;
        COPY (SELECT 1 as a, 2 as b) TO 'C:\\Users\\lucie\\output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');
      `, function(err, res) {
        if (err) {
            console.warn(err);
        } 
        console.log(res);
      }
    );
}

exportExcel().catch((e) => {
    console.log(e);
})

outputs

[Error: IO Error: Cannot open file "C:\Users\lucie\tmp_output.xlsx": The system cannot find the file specified.
] {
  errno: -1,
  code: 'DUCKDB_NODEJS_ERROR',
  errorType: 'IO'
}

So I reproduced the error with a simple typescript client in windows, while python and cli on windows just work fine.

[luusluus]

I've created an issue here:
duckdb/duckdb-node#142

While we wait for a fix from their side, I'll disable the export for windows only.

[darrylthom]

I tested this some more and got it working.

I had to make files called output.xlsx AND tmp_output.xlsx in my output folder. When I replaced output.xlsx by exporting, it removes temp_output.xlsx and replaces the output.xlsx.

Is it possible to have it create the 2 blank .xlsx files there first before it tries to replace them? That would fix it. You might have to force it using another library or something.

[luusluus]

Amazing, this could work. Maybe just creating an empty file will work? I'll try.

[luusluus]

Hmm that doesn't work. I created two empty files called output.xlsx and tmp_output.xlsx and tried to replace output.xlsx but they I got a new error:

Error: Invalid Error: Unknown exception in Finalize! {errno: -1, code: 'DUCKDB_NODEJS_ERROR', errorType: 'Invalid', stack: 'Error: Invalid Error: Unknown exception in Finalize!', message: 'Invalid Error: Unknown exception in Finalize!'}

So I'll have to try to output to excel with another library.

[luusluus]

Fix here: #109