AtoM wiki - User contributions [en]

Resources/CSV transformation

2021-05-01T21:42:46Z

Mcantelon: /* Configuration */

{{#pagetitle:CSV transformation}}
[[Main Page]] > [[Resources]] > Resources/CSV transformation

By creating a custom script, you can transform CSV files exported from other systems into CSV that is compatible with AtoM's CSV import tools.

<admonition type="seealso">
* For more information on CSV import, see the CSV section of our [https://accesstomemory.org/docs User manual.]
* For copies of the CSV import example templates, and change logs across major versions, see: [[Resources/CSV templates|CSV templates]]
</admonition>

===Transformation classes===

AtoM comes with PHP classes that help in the creation of scripts to change CSV file data so it can be imported into AtoM using AtoM's CLI import tools.

These classes can be used to process a CSV file, doing the following:

* Set child IDs to parent IDs (when importing hierarchical data)
* Add columns
* Rename columns
* Change column data

For an example of what one of these scripts looks like, check out <code>lib/task/import/example/transformation/example.transform.php</code>.

You can run the example by entering:

php symfony csv:custom-import \
--import-definition=lib/task/import/example/transformation/example.transform.php \
--output-file=/tmp/transformed.csv lib/task/import/example/transformation/example.csv

====System requirements====

* AtoM
* MySQL

====Included PHP classes====

The <code>QubitCsvTransform</code> class provides the base functionality for
transformation.

The <code>QubitCsvTransformFactory</code> class provides a convenient way to create instances of <code>QubitCsvTransform</code> objects configured to meet common transformation needs.

===Configuration===

MySQL is used as a working area for transformation data. This is done to avoid running out of RAM when working with large datasets.

When running transformations you'll need to set a series of MySQL access-related environmental variables. In Linux/OS X:

export MYSQL_USER='youruser'
export MYSQL_PASSWORD='yourpassword'
export MYSQL_HOST='yourmysqlhost'
export MYSQL_DB='yourdbname'

===Writing transformation scripts===

When leveraging the <code>QubitCsvTransformFactory</code> class, data gets transformed in three steps:

# Any necessary manipulation of data is performed and, if importing hierarchical data, if a row is determined to be a parents its legacy ID is noted
# If importing hierarchical data, each row's <code>parentId</code> column is set and the row is stored in MySQL
# Data is retrieved from MySQL, sorted by the data by level of description, and written to CSV files (each CSV file contains up to 10,000 rows)

====Parent/child association====

If you're going to use parent/child association you'll need to either include "legacyId" and "parentId" columns in your input CSV or rename/add them using your transform logic (see the example transform which renames the input CSV's ID field and adds a parent ID column).

You'll need to create logic to determine a row's key, if it the row is a parent, and logic to lookup a parent's key, if the row is a child. See the example transform (until more documentation is added here).

Parents need to be added to CSV data before their children so during import the parents' Qubit IDs can be established and associated with subsequent children. Because of this, transformation logic needs to sort CSV data by level of description. The level of description for each CSV row should be specified in a column named, or renamed, <code>levelOfDescription</code>.

Sorting requires storing of CSV data and because we don't want to store data in RAM, we store it in MySQL.

====Adding columns====

The <code>addColumns</code> option can be used to add new columns to the CSV data.

For example:

$setup = new QubitCsvTransformFactory(array(
'cliOptions' => $options,
'machineName' => 'accessions',
'addColumns' => array(
'someColumn',
'someOtherColumn'
)
));

====Transforming column data====

The 'transformationLogic' option can be used to apply ad-hoc transformation logic to the CSV data.

$transformLogic = function(&$self)
{
$self->columnValue('a column', $self->columnValue('another column'));
};

$setup = new QubitCsvTransformFactory(array(
'cliOptions' => $options,
'machineName' => 'accessions',
'transformLogic' => $transformLogic
));

====Amalgamating columns====

There's also a helper method for amalgamating multiple columns, allowing column data to be prefixed.

$transformLogic = function(&$self)
{
$self->amalgamateColumns(array(
'NOTES',
'More:' => 'MORE NOTES'
), 'radNoteGeneral');
};

===Running a transformation===

Run a transformation script from your Qubit root directory using:

php symfony csv:custom-import \
--import-definition=/path/to/transformation/script.php \
--output-file=/tmp/transformed.csv \
/path/to/transform_me.csv

===Testing a transformation===

One way to test transformations during development is to create a copy of
the source data with all data rows removed except one. In this data row
you put values that, when viewed in Qubit after an import of the transformed
CSV file, will show you that data going through transformation is arriving
in the intended Qubit fields.

===How to create transformations===

See <code>example.transform.php</code> for an incomplete example.

More documentation as time permits.

===Bugs/caveats===

CSV data runs through the <code>fgetcsv</code> function which automatically trims leading space from column values.

* [[#Resources/CSV transformation|Back to top]]

[[Category:Resources]]

Main Page

2015-04-28T07:17:24Z

Mcantelon:

= Welcome to the Access to Memory wiki =

== TO DO ==
* [[Unification]]
* [//www.mediawiki.org/wiki/Localisation#Translation_resources Localise MediaWiki for your language]

'''PRO TIP''': Extension to add nice page titles is now installed! So if you have a page like "Community/Users", you can now add the following at the top

<pre>
{{#pagetitle: Users }}
</pre>

... and it will change the display, while keeping the nice hierarchy we have made. '''REMEMBER''' to make sure that a nice breadcrumb has been added first. Check out the [[Community/Users|Users]] page as an example.

You can also give links to pages that same clean look like this:
<pre>
[[Community/Users|Users]]
</pre>

More on this extension can be found here: https://www.mediawiki.org/wiki/Extension:PageTools

'''SUGGESTION''':

I've noted that changing the page title display does not actually change the page name - so searching for "Users" will not bring up a result in the autocomplete for search (only searching for Community/Users will).

My suggestion, to at least try to make this more clear, is to keep the original page name in the breadcrumb - i.e. since the page you are on is not actually entered as a hyperlink when manually creating the breadcrumb, we might as well leave it as the full page name.

So.... NOT LIKE THIS:

<pre>
[[Main Page]] > [[Community]] > Users
</pre>

BUT LIKE THIS:

<pre>
[[Main Page]] > [[Community]] > Community/Users
</pre>

== Community ==

* Add Category: community to each page
* Add to URL /Community/...

=== Pages already created ===
* [[Community]] (landing page, very simple)
* [[Community/Users]]
* [[Community/Success stories]]
* [[Community/Community resources]]

== Development documentation ==

* Add to Category: Development documentation
* Add to URL /Development/...
* '''Have not yet made:''' landing page for "Development" itself

=== Pages already created ===

* [[Development/Multilingual]]
* [[Development/Treeview]]
* [[Development/Accession module]] (Images still need to be brought over from old wiki)
* [[Development/Functional testing]] as well as a number of sub-pages
* [[Development/Release]]

== Releases ==

* Add all pages to Category: Releases
* Add to URL /Releases/...
* Landing page for [[Releases]] created

===Pages already created ===

* [[Releases]]
* [[Releases/Release announcements]]
* [[Releases/Release announcements/Release 2.0.0|Release 2.0.0]]
* [[Releases/Release announcements/Release 2.0.1|Release 2.0.1]]
* [[Releases/Release announcements/Release 2.1|Release 2.1]]
* [[Release 1.4]]
* [[Release 1.1]]
* [[Release 1.0-beta]]
* [[Release 1.0.1-beta]]
* [[Release 1.0.2-beta]]
* [[Release 1.0.3-beta]]
* [[Release 1.0.4-beta]]
* [[Release 1.0.5-beta]]
* [[Release 1.0.6-beta]]
* [[Release 1.0.7-beta]]
* [[Release 1.0.8-beta]]
* [[Release 1.0.9-beta]]

===Still to do===

* [[Releases/Release announcements/Release 1.2]] (Half-done)
* [[Releases/Release announcements/Release 1.3]]
* [[Releases/Release announcements/Release 1.3.1]]

* [[Releases/Roadmap]]
Our vision! Time to implement! We should have sections for things that we know are going in releases (e.g. we can start documenting 2.2 features in a section here, for example... will make it easy to move to a release announcement page later) and we should have a "Wishlist" section. Ideally, this would be short, bullet-list like - but where we have thoughts/wireframes/ideas/issue links/costing estimates, we can create a page in /Development for the feature, document it more thoroughly there, and then link.

== Resources ==

* Add all pages to Category: Resources
* Add to URL /Resources/...
* Landing page for [[Resources]] created

===Pages already created===

* [[Resources]]
* [[Resources/Issue tracker]]
* [[Resources/Presentations]]
* [[Resources/Metadata crosswalk]]

Main Page

2015-04-28T07:17:00Z

Mcantelon:

= Welcome to the Access to Memory wiki =

== TO DO ==
* [[Unification]]
* [//www.mediawiki.org/wiki/Localisation#Translation_resources Localise MediaWiki for your language]

'''PRO TIP''': Extension to add nice page titles is now installed! So if you have a page like "Community/Users", you can now add the following at the top

{{#pagetitle: HeyHeyHey }}

<pre>
{{#pagetitle: Users }}
</pre>

... and it will change the display, while keeping the nice hierarchy we have made. '''REMEMBER''' to make sure that a nice breadcrumb has been added first. Check out the [[Community/Users|Users]] page as an example.

You can also give links to pages that same clean look like this:
<pre>
[[Community/Users|Users]]
</pre>

More on this extension can be found here: https://www.mediawiki.org/wiki/Extension:PageTools

'''SUGGESTION''':

I've noted that changing the page title display does not actually change the page name - so searching for "Users" will not bring up a result in the autocomplete for search (only searching for Community/Users will).

My suggestion, to at least try to make this more clear, is to keep the original page name in the breadcrumb - i.e. since the page you are on is not actually entered as a hyperlink when manually creating the breadcrumb, we might as well leave it as the full page name.

So.... NOT LIKE THIS:

<pre>
[[Main Page]] > [[Community]] > Users
</pre>

BUT LIKE THIS:

<pre>
[[Main Page]] > [[Community]] > Community/Users
</pre>

== Community ==

* Add Category: community to each page
* Add to URL /Community/...

=== Pages already created ===
* [[Community]] (landing page, very simple)
* [[Community/Users]]
* [[Community/Success stories]]
* [[Community/Community resources]]

== Development documentation ==

* Add to Category: Development documentation
* Add to URL /Development/...
* '''Have not yet made:''' landing page for "Development" itself

=== Pages already created ===

* [[Development/Multilingual]]
* [[Development/Treeview]]
* [[Development/Accession module]] (Images still need to be brought over from old wiki)
* [[Development/Functional testing]] as well as a number of sub-pages
* [[Development/Release]]

== Releases ==

* Add all pages to Category: Releases
* Add to URL /Releases/...
* Landing page for [[Releases]] created

===Pages already created ===

* [[Releases]]
* [[Releases/Release announcements]]
* [[Releases/Release announcements/Release 2.0.0|Release 2.0.0]]
* [[Releases/Release announcements/Release 2.0.1|Release 2.0.1]]
* [[Releases/Release announcements/Release 2.1|Release 2.1]]
* [[Release 1.4]]
* [[Release 1.1]]
* [[Release 1.0-beta]]
* [[Release 1.0.1-beta]]
* [[Release 1.0.2-beta]]
* [[Release 1.0.3-beta]]
* [[Release 1.0.4-beta]]
* [[Release 1.0.5-beta]]
* [[Release 1.0.6-beta]]
* [[Release 1.0.7-beta]]
* [[Release 1.0.8-beta]]
* [[Release 1.0.9-beta]]

===Still to do===

* [[Releases/Release announcements/Release 1.2]] (Half-done)
* [[Releases/Release announcements/Release 1.3]]
* [[Releases/Release announcements/Release 1.3.1]]

* [[Releases/Roadmap]]
Our vision! Time to implement! We should have sections for things that we know are going in releases (e.g. we can start documenting 2.2 features in a section here, for example... will make it easy to move to a release announcement page later) and we should have a "Wishlist" section. Ideally, this would be short, bullet-list like - but where we have thoughts/wireframes/ideas/issue links/costing estimates, we can create a page in /Development for the feature, document it more thoroughly there, and then link.

== Resources ==

* Add all pages to Category: Resources
* Add to URL /Resources/...
* Landing page for [[Resources]] created

===Pages already created===

* [[Resources]]
* [[Resources/Issue tracker]]
* [[Resources/Presentations]]
* [[Resources/Metadata crosswalk]]