It has been written as for supporting this help request on the Kexi forum. Any comments are welcome in that thread.
Note: There is alternative approach: https://forum.kde.org/viewtopic.php?f=220&t=139243
This tutorial shows how to take an input file oaklandweather.sdb which is a native standard SQLite3 file containing one table with data and create database project that can be used with Kexi.
The input SQLite3 file is available here (compressed to 600KB using xz, contains publicly available data).
1. In Konsole terminal you can use sqlite3 console app to show schema (Sqliteman GUI app would not show the schema):
2. Then you can type:
3. This shows you schema for one table archive:
CREATE TABLE archive ( dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, usUnits INTEGER NOT NULL, interval INTEGER NOT NULL, barometer REAL, pressure REAL, altimeter REAL, inTemp REAL, outTemp REAL, inHumidity REAL, outHumidity REAL, windSpeed REAL, windDir REAL, windGust REAL, windGustDir REAL, rainRate REAL, rain REAL, dewpoint REAL, windchill REAL, heatindex REAL, ET REAL, radiation REAL, UV REAL, extraTemp1 REAL, extraTemp2 REAL, extraTemp3 REAL, soilTemp1 REAL, soilTemp2 REAL, soilTemp3 REAL, soilTemp4 REAL, leafTemp1 REAL, leafTemp2 REAL, extraHumid1 REAL, extraHumid2 REAL, soilMoist1 REAL, soilMoist2 REAL, soilMoist3 REAL, soilMoist4 REAL, leafWet1 REAL, leafWet2 REAL, rxCheckPercent REAL, txBatteryStatus REAL, consBatteryVoltage REAL, hail REAL, hailRate REAL, heatingTemp REAL, heatingVoltage REAL, supplyVoltage REAL, referenceVoltage REAL, windBatteryStatus REAL, rainBatteryStatus REAL, outTempBatteryStatus REAL, inTempBatteryStatus REAL );
4. This table needs to be manually re-created using Kexi to make it match the original table as much as possible. Unfortunately -- because this is a big table.
To do so, you can create a new blank database file project using the Kexi app. Give it a name such as oaklandweather so Kexi will pick a name oaklandweather.kexi. Do not close the Kexi project.
5. In the oaklandweather.kexi project, start designing a new table.
|A hint: on Linux you can copy column names a bit faster by double clicking on the name in the table schema above, double clicking on a subsequent empty cell in the Field Caption column of the Table designer, and then pressing the middle mouse button to paste the previously made selection.|
|It is possible to create the table in Kexi using text editor and a SQL command but it would be reserved for more advanced users. Given there is enough interest instructions can be published.|
6. Save the table's design with the Archive caption. Physical table's name will be set by Kexi as archive what matches the table name in the oaklandweather.sdb file.
Note that after saving the design you can again open it for designing. The only remark is that upon saving the altered table design you would loose any data in the table, if there is any, so it is better to make sure the table design is perfect before any data is inserted into the table.
Make sure there are all the 52 columns have been inserted in the archive table design and that all are in correct order and have proper type. Any difference can make the resulting archive table inaccessible for Kexi.
When you are done, close the Kexi app.
The Kexi file without data is available here for reference.
7. So far the oaklandweather.kexi file has no data in it. Now you may be ready to execute the final command to import the data. In the Konsole terminal enter to the directory where the oaklandweather.sdb and oaklandweather.kexi files exist or add proper paths to them. Then type (or paste) the command:
sqlite3 oaklandweather.sdb .dump | egrep -e "^(BEGIN|INSERT|ANALYZE|COMMIT)" | sqlite3 oaklandweather.kexi
This works as follows:
8. Done! You can open the oaklandweather.kexi database project file in Kexi and open the 'archive' table in it to see all the data.
The resulting Kexi file is available here (compressed to 650KB using xz).