search

little-brother / sqlite-gui

Lightweight SQLite editor for Windows
GNU General Public License v2.0
1.07k stars 51 forks source link

Add sqlite extension to select ods and xls files #150

Closed SilvioGrosso closed 4 months ago

SilvioGrosso commented 6 months ago

hello @little-brother

First off, I am a bit ashamed to ask this question because I suppose I am personally doing something wrong with my trials but I am a bit at loss now...

I have been searching an sqlite extension for a while now to select .ods files (from Calc LibreOffice) and .xlsx files (From Excel Microsoft Office) via SQLite to work on Windows 10. I suppose I have currently found one, which is: xlite (https://github.com/x2bool/xlite) I have therefore downlaoded the [xlite.dll.zip] (https://github.com/x2bool/xlite/releases/latest/download/xlite-windows-x64.zip)️ and pasted the xlite.dll file into its folder (sqlite-gui.1.8.2-x64-inja\extensions)

Here comes the problem: Whenever I try to open an .ods file or even an .xlsx file an error always occurs and I don't know whether this is due to my wrong path OR this extension being incompatible with the inja sqlite-gui.1.8.2-x64 version (tested on Windows 10 - 64 bit).

My .ods file (named PROVA) is stored on the partition labelled D, therefore I write: CREATE VIRTUAL TABLE test_data USING xlite ( FILENAME 'D:\PROVA.ods', WORKSHEET '2020', RANGE 'A2:F', -- optional COLNAMES '1' -- optional ); But I always get an error: Error, File name is not provided

I have tried many syntaxs to provide the correct path, e.g.: " " instead of single ' OR // instead of \
to no avail

Thanks a lot in advance for any suggestion BTW, Happy new year 2024! :-)

little-brother commented 6 months ago

At first verify that xlite-extension was loaded in a status bar.

  1. Try to add backslash FILENAME 'D:\\PROVA.ods' or use /. \ is used to control chars e.g. \n means NEW LINE. \P is an incorrect control char, so it's rejected => filename = D:ROVA.ods.
  2. Try to use xlite in SQLite CLI or DB4S. If you have the same issue than I recommend to create a report here.

sqlite-gui has own extension to access to Excel files (.xls, .xlsx and etc) It's ODBC. This extension can read any source, e.g. MySQL/Postres/csv/etc if you have a corresponding ODBC driver. I found only proprietary ODBC driver for ODS. So at this time you should use xlite.dll for ODS.

On another hand ODS file is a zipped xml. So most likely it's not to hard to implement it as a standalone extension (with many limitations, of course) But it has not much sense since xlite already exists.

SilvioGrosso commented 6 months ago

Hello @little-brother,

THANKS a lot indeed for your help!

The Xlite funtion does appear in the status bar.

With your suggestion (/ use) everything works as expected:

image

SilvioGrosso commented 6 months ago

Hello @little-brother

Is it possible to add this extention (xlite ) by default on the next versions?

It looks like its licence is MIT (https://github.com/x2bool/xlite). It is really easy to work with and from my initial texts it looks stable enough.

I am asking this because I have also tried to use the odbc driver, the one shipped by default with the SQLITE-GUI, but, due to my ignorance, I have been unable so far to import my Excel files (xls and xlsx). From the GUIs, I am unable to select the files (tools > import via odbc)

I have checked the web-site guide (Export/Import data via ODBC paragraph) but it looks like I need an SQL SERVER installed on my computer (Windows 10 - 64 bit).

little-brother commented 6 months ago

Is it possible to add this extention (xlite ) by default on the next versions?

Doubtful. I prefer to keep a distributive as small as possible. That is why the inja-version exists. But to add a simple plugin manager to download and install extensions by one click seems like a good idea. sqlean can be as one of sources. DB4S has a bunch of requests for the similar feature.

I have checked the web-site guide (Export/Import data via ODBC paragraph) but it looks like I need an SQL SERVER installed on my computer (Windows 10 - 64 bit).

No. You don't need MSSQL. Microsoft Access Database Engine is a pack of ODBC drivers. Windows has only x32-bit drivers by default. Drivers can be installed with MS Office too.

SilvioGrosso commented 6 months ago

Hello @little-brother, Yep. I fully understand your concern :-) Having an option to easily install the sqlean extensions would be great even though it is aleady pretty easy right now to get them working.

My best regards and thanks again for your help!

Il martedì 2 gennaio 2024 alle ore 18:18:19 CET, little-brother ***@***.***> ha scritto:

Is it possible to add this extention (xlite ) by default on the next versions?

Doubtful. I prefer to keep a distributive as small as possible. That is why the inja-version exists. But to add a simple plugin manager to download extensions by one click seems like a good idea. sqlean can be as one of sources. DB4S has a bunch of requests for the similar feature.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

little-brother commented 4 months ago

Since 1.9.0 sqlite-gui doesn't have any extension in a distributive. All extension codes were also removed to own repository. All extension can be installed by Database > Extension manager.

xlite is added to the extension list but I recommend to use Attach database by ODBC feature.