In ScienceBase, metadata records may be created by parsing Excel files. During file processing, each row becomes an individual metadata record. The columns represent metadata fields describing the records. Prior to file processing, the metadata (column) fields in the Excel file must be associated to ScienceBase data fields, which requires thorough knowledge of the input data and the ScienceBase data model. Translation between input metadata fields and ScienceBase data model must be provided to guide metadata parsing into appropriate ScienceBase fields.
Directions
To process an Excel file:
1) create a new item in ScienceBase, provide metadata to describe the record;
2) upload the Excel file containing metadata records for parsing;
3) upload processing file that translates the Excel metadata fields (column names) to ScienceBase metadata fields (example below);
4) once the metadata record is created and files are attached, please e-mail sciencebase@usgs.gov to request file processing and include the item URL to which the files are attached. The item to which the files are attached becomes the parent item by default. Created metadata records become children of the parent item. Different parent URLs may be specified.
Example
https://www.sciencebase.gov/catalog/item/5212959be4b08b85aab69e09
To this item, the following 2 files are attached:
File 1 - Excel file contains 2 records (shown below) for parsing into individual metadata records in ScienceBase
File 2 - Translation file defining metadata fields (Excel columns) and assignments to appropriate ScienceBase metadata fields (this file is written using JSON format)
{
"mappingInfo":{
"description":"A Excel to ScienceBase Mapping",
"columns":13
},
"columns":{
"column0":
{
"header":"Funding Agency"
},
"column1":
{
"header":"Starting FY"
},
"column2":
{
"header":"Ending FY"
},
"column3":
{
"header":"Project Title"
},
"column4":
{
"header":"Lead Investigator"
},
"column5":
{
"header":"Phone"
},
"column6":
{
"header":"Email"
},
"column7":
{
"header":"Cooperators",
"field":"contact",
"multiple":true,
"separator":",",
"contact":{"name":"$this","type":"Cooperator/Partner"}
},
"column8":
{
"header":"Question"
},
"column9":
{
"header":"Objectives"
},
"column10":
{
"header":"Impacts"
},
"column11":
{
"header":"Abstract"
},
"column12":
{
"header":"NWCSC Category(s)"
}
},
"item":{
"title":"$column3$",
"body":"<h1>Question</h1><p>$column8$</p><h1>Objectives</h1><p>$column9$</p><h1>Impacts</h1><p>$column10$</p><h1>Abstract</h1><p>$column11$</p>",
"provenance":{"html":"created by Robb using magic"},
"contacts":[
{"name":"$column0$", "type":"Funding Agency"},
{"name":"$column4$", "type":"Principal Investigator", "ttyPhone":"$column5$", "email":"$column6$"}
],
"dates":[
{"type":"Start Date","dateString":"$column1$"},
{"type":"End Date","dateString":"$column2$"}
],
"tags":[
{"name":"$column1$", "scheme":"http://www.sciencebase.gov/vocab/category/NCCWSC/Project/Fiscal%20Year", "type":"Label"},
{"name":"$column2$", "scheme":"http://www.sciencebase.gov/vocab/category/NCCWSC/Project/Fiscal%20Year", "type":"Label"},
{"name":"Other", "scheme":"http://www.sciencebase.gov/vocab/category/NCCWSC/Project/Organization%20...", "type":"Label"},
{"name":"Test Import Organization", "scheme":"http://www.sciencebase.gov/vocab/category/NCCWSC/Project/Organization%20...", "type":"Label"},
{"name":"Other Project", "scheme":"http://www.sciencebase.gov/vocab/category/NCCWSC/Project/Project%20Type", "type":"Label"}
],
"facets":[
{"className":"gov.sciencebase.catalog.item.facet.ProjectFacet"},
{
"className":"gov.sciencebase.catalog.item.facet.ExpandoFacet",
"object":{"theme":"$column12$"}
}
]
}
}
Once the files are processed, an e-mail will notify data provider. For help setting up an Excel parsing import process, please e-mail: sciencebase@usgs.gov.
Supported ScienceBase field values for Excel file processing:
