Problem importing spreadsheet data into base

[3graygirls]

I am new to Libre Office and am trying to create a table from an ODS spreadsheet, following directions in how to convert a ods spreadsheet to and odb table and have been unsuccessful.

The spreadsheet is about 36,000 rows and looks like this:

owner branch testid matchid name admin people starred hint matchurl
Larry Sam Gray 6AE091D1-5B75-4D41 D3567149-25BD-4225-A585 anderson 186626 0 0 http://www.ancestry.com/dna/tests/6AE091D1-5B75-4D41/match/D3567149-25BD-4225
Cherie Sam Gray 40883664-FCC0-42DB D3567149-25BD-4225 anderson 186486 0 0 http://www.ancestry.com/dna/tests/40883664-FCC0-42DB/match/D3567149-25BD-4225
Ivan William Gray 972DB2BA-0F75-4F4ABEA088FD-DD82-4F17 wanderlust 170278 0 0 http://www.ancestry.com/dna/tests/972DB2BA-0F75-4F4A/match/BEA088FD-DD82-4F17

[The testid, branchid and matchurl are actually longer but same pattern.]

I get this error:

Value too long in statement [INSERT INTO “Gray Ancestry matches” ( “ID”,“owner”,“branch”,“testid”,“matchid”,“name”,“admin”,“people”,“starred”,“hint”,“matchurl”) VALUES ( ?,?,?,?,?,?,?,?,?,?,?)]
Continue anyway?

If I say Yes, the program completes and when I open the table, I get:

Error code: 40

S1000 General error java.lang.NullPointerException in statement [SELECT * FROM “Gray Ancestry matches”]

I’ve tried variations on the opening dialogue from taking all defaults on the columns to adjusting numbers to using the Auto for 36,000 rows and have gotten 63 rows, but then can’t append data.

Help!
CGH

[Ratslinger]

Hello,

Hard to state exactly where the problem lies. First you have not provided important information - specific LO version used, OS, and which database you are using. If Using Firebird embedded there can be multiple situations. You have also not stated which field, if any, is being used as the primary key. This is critical in a table.

The Value too long error is because the data you are moving from a calc cell is larger that the defined table field. Most likely this is “matchurl” field but it could be any field actually. You can tell the longest length of data in a cell for a column using (say for col A):

=MAX(LEN(A1:A40000))

but it must be entered as an array. So after entering the formula, do not press Enter, press CTRL + Shift + Enter at the same time. This will give you the needed information to set the database field and possibly need to set a bit larger.

If further problems, need more information as mentioned and possibly how the table is being set with field types.

[3graygirls]

Sorry about the omissions. It’s the latest LO version (I installed it Saturday) on Windows 7. The problem seems to have been that I had created a HSQLDB; it worked once I created a Firebird embedded DB. I can’t find any documentation that explains why one vs the other except as a tidbit in a posting. The Auto function on the type formatting changes the create a new field as primary key, that I was selecting, from Integer to Double (DOUBLE), and I changed it back to Integer. I can’t find documentation on what the different format types are. For some reason, Auto makes “people”, “starred” and “hint” all Double (DOUBLE). I’m so frustrated I left those as is even though those fields should be Numeric.

Because this is new to me, I’m using the windows, not SQL statements.

CGH

[Ratslinger]

@3graygirls,

Firebird may have worked vs HSQLDB possibly because you do not have Java (or a proper version of it) as required. Also note, with Firebird, copy or paste of data use a field of type DOUBLE and not NUMERIC or DECIMAL as this is a problem. New tables for entry are OK and changing after is OK also. Just not for Copy/Paste.

Here is a general list of database field types → SQL General Data Types.

Finally, there are two “Latest” versions - Still & Fresh. Best to give specific version such as v6.2.0.3