Dear colleagues, I am trying to import a CSV file with plot data into Collect Earth so as to have the questionnaires populated with the data and enable me doing the QA The XML importing functionality works with no problems, the CSV is not working at all. I get the following error: Parsing error on row number 1 - dataManagement.csvDataImport.error.fieldDefinitionNotFound,WRONG_COLUMN_NAME, columns,{elementsforet_element} - values {}

This is the file I am trying to import: https://www.dropbox.com/s/h0v8914g465e999/ANDRES_collectedData_earthmadagascar_grillenational_uot_copy_1_on_261119_082258_CSV.csv?dl=0

This is the .cep file: https://www.dropbox.com/s/gu33znvr08px7sr/madagascar_grillenational_uot_copy_1_20190109T103638.cep?dl=0

asked 26 Nov '19, 14:43

abespejo's gravatar image

abespejo
1529
accept rate: 0%

edited 26 Nov '19, 14:46


Dear user,
In Collect you have to go to Data Management, click on Export and select to Collect/Calc format.
alt text

Many thanks,
Open Foris Team

permanent link

answered 02 Oct '20, 09:01

Stefano%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.7k19
accept rate: 20%

Hi again Andres, just saw your response now!

The reason why we prefer to share the data using the XML version rather than the CSV is that it becomes easier to import it! When you have the data as a CSV file you will need to have the data in a format that will fit the structure of the attributes of the survey.

This means that if you have entities within your main entity (the plot) the data must be imported separately.

In the case of your survey there is an entity called element ( that holds the attributes element -a key attribute that is used to identify the values in the DB - and coverage)

So, practically this means that you must divide the CSV that you have into one with the data (the columns) that belong to the plot (minus all of the calculated attributes that you actually don't need to import as they are calculated when the data is imported) and then the data for the elements of the plot as a separate CSV.

There are many things to know here so it is quite an advanced procedure.

First, I have imported your survey CEP file inside the Open Foris Collect Survey Designer. And then I have published it (using the advanced button when you are on the screen that sows the list of surveys). You need to publish the survey in order to be able to later access its data through the Data Management section of Open Foris Collect.

alt text

I have then generated a CSV file from the original one you sent where I have removed the columns that start with element (elementforest_element, elementforet_coverage....) and also removed the columns for calculated attributes (all of the oens that start with alu_category , alu_subcategory, alu_subdivision ... ). The result is this CSV DOWNLOAD PLOT LEVEL DATA . When you import the data, remember that Collect Earth data will be in the ENTRY phase. The data in Collect can be in three phases Entry, Cleansing and Analysis! In the Data import window choose the "Insert new records" option and then choose the CSV file and click on import.

alt text

Then I have created another file with only the plot ID column and the columns that belong to the element entity ( DOWNLOAD INTERMEDIATE CSV HERE) . Using EXCEL (or LibreOffice) you can then create a new CSV that is in the right format to be imported. In this case is quite simple, the entity "element" has just two attributes, element (which is also the key attribute and that can have value "foret" and "noforet" ) and coverage. This means that for each plot there will be two rows, one for foret and another one for noforet, like this

plot_id element coverage
3564    foret   25
3564    non_foret   0
3763    foret   25
3763    non_foret   0
3963    foret   15
3963    non_foret   10
4763    foret   20
4763    non_foret   5

The resulting CSV file is this one DOWNLOAD ELEMENT ENTITY DATA

I have then imported this CSV file, but in the option I have specified that I am UPDATING data in the database, then I have chosen the "element" entity (a child of the main entity "plot"), and I have also (very important) selected that this data update should be applied to the records in the phase "entry".

alt text

So,now finally I have all of the data that was in the original CSV inside the Collect Database!! You can now start Saiku from Collect or you could export the data that you have into a Collec-data/XML file and import it into Collect Earth, where your CEP file has already been loaded. Then you can import it into Collect Earth using the usual File->Data Import/Export->Import new data from ZIP (compressed XML)

EASY, right??

I have done it myself (in case you don't want to repeat this laborious process). Here is the resulting file that you can import into CE : DOWNLOAD FINAL COLLECT_DATA FILE

permanent link

answered 27 Nov '19, 22:28

Open%20Foris's gravatar image

Open Foris ♦♦
9995714
accept rate: 10%

edited 27 Nov '19, 22:30

Thanks so much for this guidance. I am trying to do this same thing for a different country and I am not able to find in Collect the option to export as XML. Would appreciate some guidance on this. Thanks again and cheers.

(30 Sep '20, 03:52) abespejo abespejo's gravatar image

Thanks so much for this guidance. I am trying to do this same thing for a different country and I am not able to find in Collect the option to export as XML. Would appreciate some guidance on this. Thanks again and cheers.

permanent link

answered 30 Sep '20, 03:52

abespejo's gravatar image

abespejo
1529
accept rate: 0%

Importing NEW PLOTS in the database using CSVs in Collect Earth is not allowed....

I know this is a bit confusing as it says that you can use CSVs to update plots ( see menu image )

alt text

The meaning of this: Using a CSV you can update data from plots already on the database ( see this post on how to change data already collected and stored in the CE database, there are other questions posted on the forum about it )

If you need to import data from plots that are NOT ALREADY ON THE DATABASE you can only use the XML( rather zipped XMLs) version of the data. We always advice our users to saved/send/store the data using that format as it is less problematic in the long term, or at least produce both CSV and XML(ZIP) version of the data, one human readable, the other Collect Earth friendly )

So, if you have this data already as XML then just use the File->Data Import/Export->Import new data from ZIP (compressed XML) and import away...if not...

Do not worry, then plan B, you only have the CSV? you can still import the data through Open Foris Collect (no Collect Earth, always confusing)

Steps :

  1. Obviously open Open Foris Collect
  2. Import the CEP file into Collect ( in the menu Survey Designer/Import Survey) in case it is not already on your list of surveys
  3. PUBLISH THE SURVEY ( this way the data management section will allow you to import the data)
  4. Go to the Data Management section and there you will have the choice to import the data as CSV (yes, why don't we offer this in Collect Earth?? good point, we will probably add it soon)
  5. Now the data is in the Collect database (separate from Collect Earth). You need to export the data as Collect Backup and then import the resulting collect-data file into Collect Earth.
  6. If you want to just analyse the data, you can run Saiku directly on Collect ( see in the left frame the Saiku option)

IF YOU NEED MORE GUIDANCE LET ME KNOW TO MAKE THE STEPS EASIER TO FOLLOW!

permanent link

answered 26 Nov '19, 15:17

Open%20Foris's gravatar image

Open Foris ♦♦
9995714
accept rate: 10%

Thank you so much for the quick response. I have followed the steps but when trying to import the data I get the following error:

row: 1

columns: elementsforet_element

errorType: WRONG_COLUMN_NAME

message: Attribute definition not found or sub-field not specified

I have checked the name of the column elementsforet_element of the CSV I try to import and the survey and they are consistent, so I don't know where this error comes from.

Thanks. Andres.

(27 Nov '19, 08:19) abespejo abespejo's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×281

question asked: 26 Nov '19, 14:43

question was seen: 3,772 times

last updated: 02 Oct '20, 09:01