CSV import to update data

[Alex Fletcher]

We're working on a project to do a CSV import to update a number of our "brief" AtoM records with data coming from another external source.

Based on the documentation, it looks like we need the identifier, title, repository for matching purposes, and we'll also need the location to be pulled out with this.

I've been looking at the DB and it's not clear to me what kind of SQL query I might need to use in order to pull out this relevant information. Does anyone have any ideas as to how a query might be put together that can export all of this data into a CSV (or something similar) that we can use to match up with our data and expand these brief records?

Thanks,

Alex

[Corinne Rogers]

Hi Alex,

If I understand your question correctly, you are exporting existing records, adding more information from another source, and re-importing back into AtoM. This will require the import to match and update current records. 

If you try this, do test it in a development environment on a vagrant box and not in your production environment!

The current implementation of the CSV import in AtoM assumes that the import data (all of it) comes from some other system and not roundtripping to update current records. This means that using csv import for updating purposes is risky, and not recommended. As I understand it, if matching in all fields between exported data and re-imported data is exact, it will update the current records. However, CSV will try to look at the key map table and look for foreign system IDs, but these will be AtoM IDs and the keymap matching will fail. Secondarily it will look at a combination of title, identifier, and repository. If none of these fields has been modified in any way, it should match and update.

Changing the way csv imports work would require development. Development on this CSV roundtripping has been proposed, but would have to be sponsored.

However, this doesn't answer your question about the SQL query! If you still intend to proceed, let us know and we will address the query.

best regards,

Corinne

---

Corinne Rogers, MAS, PhD

Systems Archivist

Artefactual Systems

[Mathieu Deschaine]

Hi Alex, 

I have done some testing with this and had some success, without going into SQL.  I'll describe what I did as best I can.  Basically my situation was, I had a fairly large collection of digital objects and I wanted to add a rights statement to each of those objects.  I'll preface this by saying I am running AtoM 2.4.0-156. 

1.  I added the top level collection to the clipboard, and from the clipboard, exported as a CSV making sure "Include Descendants" and "Include All Levels of Description" are checked. 
2. This creates a CSV export Job and from the Job Screen you can download the resulting CSV.
3. Open the CSV and add the data.  In my case is was adding the rights statements to the Access Conditions column.  Do not move or remove any other fields.
4. Once your sheet is updated, go to the Import CSV page
1. Select "Update matches ignoring blanks fields in CSV"
2. Select "Skip Unmatched Records"
3. Limit matches to your repository
4. and select the top level description that started it all.
5. Choose your updated CSV and let it run.
5. That should update your chosen fields.

as Corinne says, test this in a dev environment, multiple times to be safe.

Best,

Mathieu