Hey.
I’m currently trying to integrate some huge calc tables into my database.
The problem is that having to manually change 200 variables to “int” instead of text is a real bother. Any way to ease this process? I mean the data is already formatted as numbers in calc but it seems this information is note carried forward.
I’ve spent a few hours trying to google-fu my way to a solution but nothing has come up.
Thanks for your help
I understand the question to be seeking a way to sidestep the GUI, which is cumbersome for large-scale changes.
You can change the table by drafting a SQL script, which you can compose in a text editor. This might result in improvement in speed in creating the instruction. Here is the instruction to enter into Tools → SQL where the table with the column is Table1 and the text column is text:
ALTER TABLE "Table1"
ALTER COLUMN "text" INT;
For additional columns, just repeat that statement with correct information, customized to the particular field, separating the statements by semi-colons This worked for me with two statements, should work for more.
To make this even more efficient, here is another SQL script that will automatically spit out a CSV with the above statement for every VARCHAR column in your Table1 – you will need to change both instances of Table1 to your table name:
SELECT CONCAT(CONCAT('ALTER TABLE "Table1" ALTER COLUMN "', "COLUMN_NAME"), '" INT;') "phrases"
INTO TEXT "sql_altertable_phrases"
FROM "INFORMATION_SCHEMA"."SYSTEM_COLUMNS"
WHERE "TABLE_NAME" = 'Table1'
AND "TYPE_NAME" = 'VARCHAR'
In my Linux system, the CSV is outputted to /home/user/Downloads/ The new table definition will be visible through the GUI after you do View → Refresh Tables. I would refresh immediately to avoid an ambiguous state for LO Base.
Warning: you must delete the reference to the CSV table in the list of tables when you are done and before closing LibreOffice. For a unknown reason, the presence of that table will prevent HSQLDB from loading anything on restart and the error seems irrecoverable. So delete the reference to the CSV table, as it does not really help.
This probably is the simplest and most efficient way. The statement will try to change the table definition and convert the data in the column. In testing, the statement failed when the data could not be converted, but I would be extra careful about that-- create a backup before executing and verify the output of the operation.
You also could CREATE a new table with the desired definition and then INSERT the data using the SQL tool, (or ALTER TABLE ADD COLUMN and UPDATE) with or without use of the CAST function. This is all documented in the HSQLDB v.1.8 manual.
EDIT: added shortcut for creating script with SYSTEM_COLUMNS
EDIT2: Added further cleanup instruction.
(if this answered your question, please accept the answer by clicking the check mark (
) to the left)