Note: Please precede the names of your files on the local schema with your netid.

(DRAFT)


  • In Excel, add a key column to your table to make it easier for DBeaver to pick a key.
  • Make a .csv copy of the file.
  • In DBeaver, right click on the local schema you want to load the file to.
  • Click on import data.
  • At the “Import Source” window, select.csv file if it isn’t already selected.
  • Click on next.
  • A Select input file window opens. Navigate to the file you want, select it, and then click on open.
  • The “Input Files” window opens, including the file you selected. Don’t worry about the target box when in this window.
  • Click on next.
  • In the “Tables Mapping” window, look at the target field. You can create a table, append to a table, or replace a table through the mapping field. Take the target name the system suggests, or edit it to be the target table you want. Then make sure the mapping field is what you want as it relates to the target table.  Use “create” to make a brand new table. Use “existing” to append data to an existing table. Use “recreate” to replace an existing table.  Here is how DBeaver describes it:
    • Create: “Transfers source data into a newly created table or column in the target container.”
    • Existing: “Transfers source data to an existing table in the target container.”
    • Recreate: “Recreate the table, which means that the available data, keys, indexes and other possible entries of the existing table will be lost.”
  • You can also pick which column types you want from this window, through the Configure button. (See DBeaver’s documentation linked below.)
  • Click next to go to the “Data load settings” window.
  • Accept the setting unless your import fails, which Joanne Leary notes happens for her frequently “… when importing a spreadsheet of the reserve statistics, because some fields are too long to fit into the system-guessed-at field length in DBeaver). When an import fails, it seems to create a skeleton table with the field names (no data in it). The error message shows which fields it didn’t like. Then I go into the properties of each problematic column (in the table skeleton in DBeaver) and increase the size, like changing VARCHAR(56) to VARCHAR(1000). Then I re-import the table into the table skeleton.  (FYI, this is the same problem that I experienced in Access.)”
  • Click next
  • In the “Confirm” window, click proceed.
  • The table opens in DBeaver.  Make sure all rows were loaded.
  • For more information, see DBeaver’s documentation at:  https://dbeaver.com/docs/dbeaver/Data-transfer/#importing-data-from-csv-fi
  • March 2024: Note that for Metadb, you must grant access to local tables in local_shared. How this is done will hopefully change; consult with Sharon. Currently you need to use a command for each individual. For example: GRANT SELECT ON local_shared.lm_adc_location_translation_table TO z_slm5



Updating tables: To update a local table (first open the table in the data view):

  • To add a row, right click in a cell of the row you want to add the row after, click on “Edit,” and then click on “Add row,” enter data as needed, and then click on save.
  • To remove a row, right click in a cell of the row you want to delete, click on “Edit,” and then click on “Delete current row,” then click on save.
  • To correct a value within a cell, click on the cell, edit as needed, and then click on save; you can also open a editing window by right clicking and clicking on “Edit.”


  • No labels