Ask Your Question

Revision history [back]

Hi!

In case anyone was wondering I found a way to migrate from .odb to .db for use in SQlite as I required it.

It's a bit of a process but It worked for me in the end.

Requires using Base, Calc, and DB Browser (located here: [https://sqlitebrowser.org])


Part one, exporting tables to CSV.

You will need to export each table individually. In Base 'right click' the table you want to export, 'COPY'.

Go into a new Calc spreadsheet and 'right click' cell A1, 'PASTE'.

File > Save As, change Save as Type: to 'Text CSV (.csv), Save, (I left options that come up all Default), Save.

Do this for each table.


Part two, importing table into DB browser:

Open up DB Browser, Click 'New Database', Name your database and save, When the next window pops up click 'Cancel'.

File > Import > Table from CSV file..., Find and insert your first table (you will need to import each one separately).

In the window that pops up make sure to name your table

Check ' Column names in first line'

field separator ,

quote character "

Encoding (I had weird characters with Umlauts, etc and found that setting it to ISO-8559-1 worked for me)

Trim fields (I don't know what this does exactly but mine was checked and worked)

'OK'


Part three, modifying tables and setting keys:

Expand your table list by clicking the down arrow under 'Tables'. Select your table then click 'Modify Table'.

Set the 'Types' to match what the data should be (e.g. text, integer, etc).

Checking the boxes will set up the each column for:

 'Not'  =  Not Null

 'PK'  =  Primary Key

 'AI'  =  Autoincrement

 'U'  =  Unique

Now to set the 'Foreign Key' you will need to either expand the size of entire window, or shrink down the width of the name,type, default headers. There should be a sneaky 'Foreign Key' header hiding out of sight on the right hand side that should appear. Use the dropdown boxes to set the foreign keys.


Exporting database:

Once all that is done and your ready to export your database, File > Export > Database to SQL file...

Make sure all your tables are selected and hit 'OK' (I left everything default and it worked for me), name your database and hit 'Save'.


Importing into SQlite:

(I moved the database file to the same location as the SQlite executable)

Set your directory to where you keep your SQlite (for me it's C:\sqlite and below will follow suit)

\ CD C:\sqlite

\ sqlite3

\ .read yourfilename.sql

(to check that it loaded type)

\ .tables

(if all is well then we'll save it as a .db now)

\ .save yourfilename.db


CONGRATS YOU DID IT!

now to open your database in sqlite the command will now be

\ sqlite3 yourfilename.db

Hi!

In case anyone was wondering I found a way to migrate from .odb to .db for use in SQlite as I required it.

It's a bit of a process but It worked for me in the end.

Requires using Base, Calc, and DB Browser (located here: [https://sqlitebrowser.org])


Part one, exporting tables to CSV.

You will need to export each table individually. In Base 'right click' the table you want to export, 'COPY'.

Go into a new Calc spreadsheet and 'right click' cell A1, 'PASTE'.

File > Save As, change Save as Type: to 'Text CSV (.csv), Save, (I left options that come up all Default), Save.

Do this for each table.


Part two, importing table into DB browser:

Open up DB Browser, Click 'New Database', Name your database and save, When the next window pops up click 'Cancel'.

File > Import > Table from CSV file..., Find and insert your first table (you will need to import each one separately).

In the window that pops up make sure to name your table

Check ' Column names in first line'

field separator ,

quote character "

Encoding (I had weird characters with Umlauts, etc and found that setting it to ISO-8559-1 worked for me)

Trim fields (I don't know what this does exactly but mine was checked and worked)

'OK'


Part three, modifying tables and setting keys:

Expand your table list by clicking the down arrow under 'Tables'. Select your table then click 'Modify Table'.

Set the 'Types' to match what the data should be (e.g. text, integer, etc).

Checking the boxes will set up the each column for:

 'Not'  =  Not Null

 'PK'  =  Primary Key

 'AI'  =  Autoincrement
Autoincrement **See note below

 'U'  =  Unique

Now to set the 'Foreign Key' you will need to either expand the size of entire window, or shrink down the width of the name,type, default headers. There should be a sneaky 'Foreign Key' header hiding out of sight on the right hand side that should appear. Use the dropdown boxes to set the foreign keys.

Note LibreOffice starts its' autoincrements at 0. Sql starts at 1. If you're using autoincrements you will need to change the entry on the table with a '0" to '1' or more. Because I imported populated tables I simply just changed the line with a 0 to the next number after the last one e.g. 28. If your tables need to keep the order of the autoincrements then may God have mercy on your soul.


Exporting database:

Once all that is done and your ready to export your database, File > Export > Database to SQL file...

Make sure all your tables are selected and hit 'OK' (I left everything default and it worked for me), name your database and hit 'Save'.


Importing into SQlite:

(I moved the database file to the same location as the SQlite executable)

Set your directory to where you keep your SQlite (for me it's C:\sqlite and below will follow suit)

\ CD C:\sqlite

\ sqlite3

\ .read yourfilename.sql

(to check that it loaded type)

\ .tables

(if all is well then we'll save it as a .db now)

\ .save yourfilename.db


CONGRATS YOU DID IT!

now to open your database in sqlite the command will now be

\ sqlite3 yourfilename.db