Base created from xlsx file - move to proper DB

users
#1

Hello all.

I thought I had gotten the data from a flat file (xlsx) into Base. I was trying to import and create a new database.

My work requires my data to be in the company OneDrive. When I try to open the connected flat file, I get errors that the file location or parameters are not OK. So all the work I did is lost. This is likely a bug but I don't have time to report this at the moment.

Apparently I incorrectly used: "File:Connect to an existing database" as that just appears to connect to the flat file but won't actually create a proper table in Base. While I can understand the use case for just accessing a flat table, I'd like to import not just connect.

There has to be an easy way to import the data without having to manually create a table and then import the csv file. What am I missing?

I'm just trying to get data into Base easily.

LO v6.4.0.3 on Win10 64B.

Thanks in advance.

#2

Sorry, I should have added that I'm on digest so please reply to the list but also to me directly.

Thanks.

#3

Base, as you have found out, is first just a front end to all manner of
data sources.

So, for what you want to do I would approach it thusly.

1st - create an empty Base file for whatever database I want to use. Just
the default embedded is an example.

2nd - create the Base file which connects to the xlsx file as the data
source. I take it you have that already.

Now with both open, with the mouse drag the table (the spread sheet data)
from the 2nd file to the 1st, dropping onto the Tables section.

This triggers the import dialog.

Hope that helps,

Drew

#4

Forwarded to OP

#5

As Drew has said, you can use your existing flat file in several ways :

1) Go via the empty ODB file method to drag the linked XLSX sheet from
your first ODB file to the second, empty ODB file and then follow the
import wizard.

2) Create an empty ODB file and keep it open. Open your XLSX sheet
directly in LO Calc. Select all of the data including column headers,
that you want to import.
Drag and drop this selected data onto your open ODB.
Use the import wizard options to configure the way you want the data to
be imported into a new table that will be created as a result of the
drag and drop maneuver.

Be aware that the import wizard isn't perfect, depending on the version
of LO you are using, it might cause you a bit of grief, so double-check
afterwards that it has done what you wanted (within the confines of what
it can actually do, of course). You might find that some post-import
database alteration statements might be necessary.

Alex

#6

As Drew has said, you can use your existing flat file in several ways :

1) Go via the empty ODB file method to drag the linked XLSX sheet from
your first ODB file to the second, empty ODB file and then follow the
import wizard.

2) Create an empty ODB file and keep it open. Open your XLSX sheet
directly in LO Calc. Select all of the data including column headers,
that you want to import.
Drag and drop this selected data onto your open ODB.
Use the import wizard options to configure the way you want the data to
be imported into a new table that will be created as a result of the
drag and drop maneuver.

Be aware that the import wizard isn't perfect, depending on the version
of LO you are using, it might cause you a bit of grief, so double-check
afterwards that it has done what you wanted (within the confines of what
it can actually do, of course). You might find that some post-import
database alteration statements might be necessary.

Alex

#7

Thank you Alex and Drew.
I'll try your suggestions today.
I appreciate your help!

One more question if I might.
If I understand correctly, HSQLDB is being replace by Firebird. So I'd
like the engine to be Firebird so I don't end up with an issue in the
future.
How does one know that Firebird is being used?

#8

I would only recommend using embedded Firebird if you're comfortable
with all of its still numerous bugs, and are happy to get your hands
dirty correcting data types, lengths, etc and possibly corrupted data.

Also, it is my understanding that there is currently still a limitation
of 64Kb for the total insert length of any transaction, meaning that you
have to be especially wary of the number, and length of any VARCHAR data
that you have in your spreadsheet.

If you have Unicode characters in any cell, you might also get an import
failure because these are coded over 4 bits per character, which can
rapidly cause the automatically created insert statement to exceed the
64KB limit, and again, fail.

My advice would be to stick to embedded HSQLDB until, or unless, you are
comfortable with such limitations.

Alex

#9

I would only recommend using embedded Firebird if you're comfortable
with all of its still numerous bugs, and are happy to get your hands
dirty correcting data types, lengths, etc and possibly corrupted data.

Also, it is my understanding that there is currently still a limitation
of 64Kb for the total insert length of any transaction, meaning that you
have to be especially wary of the number, and length of any VARCHAR data
that you have in your spreadsheet.

If you have Unicode characters in any cell, you might also get an import
failure because these are coded over 4 bits per character, which can
rapidly cause the automatically created insert statement to exceed the
64KB limit, and again, fail.

My advice would be to stick to embedded HSQLDB until, or unless, you are
comfortable with such limitations.

Alex

#10

See below...

I would only recommend using embedded Firebird if you're comfortable
with all of its still numerous bugs, and are happy to get your hands
dirty correcting data types, lengths, etc and possibly corrupted data.

Always look at the resulting table to see if it's fields are defined correctly and display correctly when looking at table data.

Make sure columns are defined with proper data type such as date or time in your spreadsheet.

Until the resulting table is as close to your intended field type, keep making a new table.

Understanding through re-iteration will help you see the pitfalls.

If you can't get the import to the correct data type, import in such a way as to have complete data from spreadsheet field.

At this point, from within Base, you may be able to make a new table that has a correct data type and copy from Table-0 to Table-1.

Hope this helps

Paul

#11

Thank you Alex, Paulo and Drew.
I didn't realize there were so many "bugs" still to work out.
I'll stick with HSQLDB for now.
I trust there will eventually be a way to migrate to the new DB in the
future?

#12

Fair enough what people have said.
The one thing that hasn't been said is what you need to do with that that
it needs to be in a database.

Depending on what that is I may disagree with my colleagues and say go for
the firebird engine.

So - what are you hoping to get from the data in a database table vs the
spreadsheet?

Thanks

#13

I hope that once you have HSQLDB working, you will transfer eventually migrate to Firebird.

It has been far superior and and stable once I worked out the bugs.

https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB

https://wiki.documentfoundation.org/Documentation/FirebirdMigration

Glad to help.

#14

I agree with both Paul and Drew, Firebird is an excellent DB engine.

I have had recourse to fairly complex Firebird engine databases in a
client-server environment professionally for many years.

Alex