We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Adding Calc rows to a data base table with existing rows.

asked 2020-10-08 19:49:53 +0200

ernesto.besada gravatar image

Hi! I have a database table with existing rows and a Calc data sheet with rows with the same type of data in the data base. Now, I need to add the Calc rows to the database table but I can´t do it. Is it possible to do? In MS Access simply you copy the rows in excell and paste in the new row and all the rows are copied to the table. In LibreOffice Base, all the data are copied in the first row, first column, that is, all in one cell!!

Thanks a lot.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-10-09 05:13:47 +0200

frofa gravatar image

updated 2020-10-09 10:35:04 +0200

Hello ernesto.besada

...I need to add the Calc rows to the database table but I can´t do it. Is it possible to do?...

It is possible to do. Try these steps (with both your CALC sheet and Base document open on-screen):

  1. Make sure the columns of your Calc sheet are in the same order and have the same field-names as the columns of your target Base TABLE.
  2. I find it also helps to make your Calc sheet columns match the data-types of your target BASE TABLE - e.g. DATE columns in the Calc sheet should be in YYYY-MM-DD format and of DATE format. I also have the ID column (integer primary key PK) as the first column - although the data from this ID column will not necessarily be used if the corresponding Base table column is set to autovalue.
  3. Select all the populated row and column cells of you Calc sheet including the first row of field/column names (click on upper left corner cell to select all cells of the Calc sheet) and then copy to the CLIPBOARD.
  4. Select the target BASE TABLE in your main Base table listing, and do a PASTE.
  5. The BASE import WIZARD should give you the option matching the Calc sheet fields/columns with the Base table column names.
  6. NOTE: tick the Base import wizard options append data, and use first line as column names - then click Next button.
  7. IMPORTANT: When you see the field-matching information in the wizard window, un-tick the primary key ID column assuming you have set an INTEGER PK column with autovalue as the first column of your target table. The autovalue setting will allow the PK to increase by 1.
  8. Finally, other variations of the above steps might be necessary if the import process fails (for some reason).

Report in detail if there are problems/errors with the above process. NOTE: Do NOT use an Excel spreadsheet - only use a LibreOffice Calc sheet.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-08 19:49:53 +0200

Seen: 243 times

Last updated: Oct 09 '20