Moving Data from calc to base

[ztminhas]

Good Afternoon,

This is follow-up from copying data from base to calc at How to use a “SELECT TOP 1…” T-SQL query in a calc macro with MSSQL Server - English - Ask LibreOffice

This is good for a lot of the work I am doing. However I also need to do the reverse. I need to send data from spreadsheets to base. If there is any way that uses built-in / native features, that would be greatly appreciated.

Any small example will help. Thank you once again for all of your support.

[Villeroy]

When you drop database data into a sheet, you have guaranteed data types. Only numbers in one column, only text in another column. Dates and booleans are converted to correct spreadsheet values (formatted numbers).

The built-in method to do the opposite goes like this:

1. Copy the spreadsheet range with or without one header row.
   2a) Select the table icon in the Base window.
   2b) Alterternatively select the “Tables” icon in the data source window. The table icon does not work here.
2. When you paste to the selected icon, a dialog pops up. Check “append data”. In case of 2b) you have to enter the name of the target table.
3. In the second step of that dialog, you get the opportunity to map each copied column to its corresponding database column.
   
   Dropping sheet data into a database fails in most cases because spreadsheets with pure column types are rare.
   In most cases you get errors due to inconsistencies, blanks where the database does not accept blanks, (empty) strings where the database does not accept strings, duplicates where the database does not accept duplicates, foreign keys that do not exist in the related detail table.
   
   If you are familiar with SQL, you may dump everything into a temporary text-only table and run carefully crafted UPDATE/INSERT statements to transfer consistent data into the actual data tables.
   
   If you are more familiar with spreadsheets you may use some carefully crafted formulas to test your data for consistency and fix them before you do the copy/paste operation.

[ztminhas]

Thank you for that. I am familiar enough with SQL, and I have lots of experience in error checking data as well as preprocessing before import/export. I have many times manually copied data from calc to base. If there is a macro way to replicate this process, that would be greatly appreciated. Thank you once again.

[Villeroy]

Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet