From SwissExperiment

Importing manually sampled data from Excel to the SwissEx wiki using Macros

These guidelines describe how to upload manually sampled data into the SwissEx wiki site. The process involves formatting the data in Excel so that it can be read and queried in SwissEx, creating semantic properties and a template for the data on the SwissEx end, and actually uploading the data using an Excel macro. See http://semantic-mediawiki.org/wiki/Help:Inline_queries for information on how to query this data in SwissEx after it has been uploaded.

Prepare Excel file:

• I find it best to first create a file with the data in the correct format but no macros (this way you have a clean copy of the correctly formatted data).
• Header should generally include the following information in columns A / C:
  • Fieldsite / e.g. Stillberg (this should match the name given to the fieldsite page in SwissEx) [note: in cases with multiple fieldsites in one data file, this line can be left blank and the various fieldsites can be inserted as an additional data column]
  • Contact / your SwissEx user name
  • Sample entry date / (optional)
  • Sample result date / (optional)
  • (blank)
  • Template name / Descriptive name of dataset
  • Related experiment setup / e.g. Fieldsite:CO2_enrichment_(FACE)
  • Measured variables / property names (see below) of all variables that have been measured (separated by commas)
• Each row of data in the first column must have a unique value (this value serves as the page name in the wiki).

Create a template file in SwissEx:

• Click on “special pages” (located at the bottom of each page on the wiki).
• Click on “properties” under “lists of pages” and check if the variables in the Excel file to be uploaded have already been defined as properties.
• If you need to define a new property, click on “create a property” under “semantic forms” on the main “special pages” webpage.
• Enter a property name (e.g. Tree Height) and type (e.g. Number, Length). Other common types are String (should be used for any text values) and Date.
• You can specify the default units for a property. To do so, save the page and then click on “edit” at the bottom of the page. Add “The unit used for standard display is [[display units::'''xx''']]” below the existing text.
• When all properties have been defined, go back to the main “special pages” and click on “create a template” (first check that a template for the data you want to upload doesn’t already exist under “templates”).
• Fill out the template form:
  • Template name: match the template name given in the Excel file; it is helpful to start all templates for a given experiment with the same text (e.g. Stillberg FACE; Mountland Vaud)
  • Category defined by template: generally should be “Manually sampled data”.
  • Information in the grey box relates to a single variable (column in Excel file). Click “add field” for each property you want to create.
    • Field name: generally 2, 3, 4….(corresponds to the column number in the Excel file). To deviate from the order in the Excel file, add the fields in the order you want (e.g. Field name 3, 5, 4). Column 1 of the Excel file does not need to be defined because the macro defines it as the page name. Note that you cannot skip columns in the Excel file.
    • Display label: name of variable (label for value in Excel column, e.g. CO2 Treatment).
    • Semantic property: scroll the drop down list to find the appropriate property (see above). For a parameter corresponding to a fieldsite in SwissEx (e.g. Stillberg), using the property “Deployment Name” allows you to query based on this variable. For a measurement location (e.g. plot), use the property “Station name”.
• The fields in the “Aggregation” box can generally be left blank. Save the page.
• Units that should be displayed for an individual measured variable can be specified. Click on “edit” at the bottom of the template page. In the template code, insert space + units after each measured parameter. Save the page.

   e.g. | [[Mean shoot increment length::{{{9|}}} mm]]

Set up user access limitations:

• Go to Help from the main SwissEx menu and click on Help:AccessControlHowTo to set up a user group.
• Insert the following text at the top of the template code.

  <accesscontrol>(your user group’s name)</accesscontrol>

Managing macro information:

• Check that the Developer toolbar is activated in Excel (http://www.excel-vba-easy.com/vba-how-to-create-macro-excel.html provides instructions).
• Open the example file (Stillberg_macro_template_may2011.xls) and enable macros content (you might have to save the file, reopen it, and re-enable macros again after doing this the first time).
• Check that “design mode” is not pressed in the Developer tab.
• Copy-paste all values from the above Excel file into this example file.
• Check that sheet 2 exists but that any existing values have been deleted. Press “no” when asked if you want to delete the query associated with the sheet.
• Open the macro code (Designer – Visual Basic).
• Under “Sub URL_Post_Query”, check that the namespace is correct for the field site associated with the data (.PostText = "pagetitle=" & title & "&namespace=xxx&textbox=" & URLEncode(text)). [e.g. Stillberg = 146; Mountland = 134]
• Sheet 2 can only accommodate 256 columns, which means that no more than ±100 rows of data can be read in at one time. If the dataset is larger, you must divide the upload process into smaller sections: under “Public Sub Upload_Data()”, change “For Counter = 1 To row” to “For Counter = 1 To 100”. To check that formatting is correct, it is useful to first upload only a few rows (1 To 2); this avoids having to delete many pages from the wiki if they are incorrect.
• If multiple fieldsites are included in the data file (see 1b above), delete the two lines of code:

Dim fieldsite As String
fieldsite = Worksheets("Sheet1").Cells(1, 3).value

Run macro:

• Press the “login” button. Enter your user ID and password; general information from the SwissEx wiki should appear on sheet 2.
• Press the “Upload Data” button; data will be uploaded to the wiki one row at a time.
• After all specified rows of data are uploaded successfully, create space on sheet 2 by deleting columns associated with the uploaded data (do not delete columns associated with SwissEx general information).
• Change counter values as appropriate (eg. 101 To 200) and resave the Excel file before uploading the next batch of data.
• Check that the appropriate data pages have been uploaded by clicking “my contributions” in the wiki; click on the new contribution to see your data.

Further explanations about these topics can be found at: http://semantic-mediawiki.org/wiki/Help:Properties_and_types http://semantic-mediawiki.org/wiki/Help:Semantic_templates

Example template code with explanations

<accesscontrol>Stillberg</accesscontrol>       ##  limits access control to the group Stillberg</span>
<noinclude>
This is the "Stillberg FACE tree height" template.
It should be called in the following format:

{{Stillberg FACE tree height
|1=                                                            ##  column number in Excel file
|2=
|3=
|4=
|5=
|6=
|7=
|8=
|9=
}}

Edit the page to see the template text.
</noinclude><includeonly>
{|
! Fieldsite                                                     ##  title for table (label in bold)
| [[Deployment Name::Stillberg]]                    ##  property name called by template followed by the
|-						                        corresponding field number (column) in the Excel file
! Measured Variables
| {{#arraymap:Tree Height|,|x|[[Database parameter name::x]]|\n\n}}
|}

----
{| class="wikitable" cellpadding="10" cellspacing="0" border="1"
! Stillberg Plot.Tree: 				
|[[Stillberg TreeNumber::{{{2|}}}]]			
|-						   
! FACE Plot Number: 
| [[FACE Plot::{{{3|}}}]]
|-
! FACE Plot ID: 
| [[Station name::{{{4|}}}]]
|-
! CO<sub>2</sub> Treatment: 
| [[CO2 Treatment::{{{6|}}}]]
|-
! Tree Species:
| [[Tree Species::{{{5|}}}]]
|-
! Temperature Treatment: 
| [[Temperature Treatment::{{{7|}}}]]
|-
! Year: 
| [[Timestamp::{{{8|}}}]]
|-
! Tree Height: 
| [[Tree Height::{{{9|}}} cm]]
|}


<!--ADD EXPERIMENTAL METHOD-->
{| class="wikitable collapsible" style="width: 98%; font-size: 90%; border: 1px solid #aaaaaa; background-color: #f9f9f9; color: black; margin-bottom: 0.5em; margin-left: 1em; padding: 0.3em; text-align:left;"
|-valign=top
! style="text-align: center; background-color:#ccccff;" colspan="2" |<big>Experimental method</big>
|-valign="top"					              ##  setup for table displaying data
|[[Special:AddData/Experimental/{{NAMESPACE}}:{{#uid:}}|'''Click here to register an experimental method''']].
|-valign="top"
| {{#ask:[[Category:Experimental Method]][[Database table name::{{{DBaseTableName|{{{DBaseName|}}}}}}]][[Database parameter name::{{{DBaseParameterName|{{{Parameter|}}}}}}]]
|?Action
|?Start date
|?End date
|format=table
|default=There are no experimental methods registered}}
|}

<!--ADD NOTES-->
{| class="wikitable collapsible" style="width: 98%; font-size: 90%; border: 1px solid #aaaaaa; background-color: #f9f9f9; color: black; margin-bottom: 0.5em; margin-left: 1em; padding: 0.3em; text-align:left;"
|-valign=top
! style="text-align: center; background-color:#ccccff;" colspan="2" |<big>Deployment Notes</big>
|-valign="top"
| {{{Notes|No notes added.}}}
|{{#if:{{{Image|}}}|[[Image:{{{Image}}}|320px|thumb|right|{{{SerialNo|}}} deployed at {{{Location}}}]]| }}
|}
[[Category:manually sampled data]]		    ##  category as defined in template
</includeonly>