Base: Is there a way to import data from a spreadsheet into a table to modify current records (match & replace)

[b2683152]

In some database software like Filemaker, data in .csv or spreadsheet format can be imported into the database from a form/layout view to update existing records, rather than create new records. This is achieved by selecting identical fields in both source and destination to use as a match, then selecting which fields to actually import into the database.

For example, I have a .ods with several columns of data, one of which is identical to a column of data in my .odb table. I want to match those identical fields against one another, and then import the the data from the .ods to overwrite the contents of one or more fields in the database table.

How can this be achieved in Base?

Thanks.

[Ratslinger]

While I don’t believe there is a way to “Update” columns in Base as you have described, there is another way. Since you don’t mention which database you are using (Base is not a database) this is based on HSQLDB which comes with Base.

First, create a table from your spreadsheet items by copying the columns you will use for updating and past them into the ‘Tables’ area of Base. This will start a wizard to create a table. Then from the menu Tools->SQL enter your SQL UPDATE statement such as:

UPDATE "Bike" SET "Bike"."Wheels" = (SELECT "Table1"."Wheels" FROM "Table1" Where "Bike"."Id_Bike" = "Table1"."Id_Bike") Where Exists (SELECT "Table1"."Wheels" FROM "Table1" Where "Bike"."Id_Bike" = "Table1"."Id_Bike")

This sample SQL updates table ‘Bike’ column ‘Wheels’ with the data from table ‘Table1’ (newly created table) column ‘Wheels’ only when the Id from each table matches. Before running any SQL of this type, it is always advisable to first backup your information.

[b2683152]

This worked, thanks Ratslinger. I am using HSQLDB. In the future I would love to see a GUI import dialog with two table columns (source & destination) with fields to match and replace like Filemaker has, but in the meantime this SQL statement works nicely.