NASA FIRMS data ingestion

[Kyle_Mac]

Hello Knime community, I have a problem I hope someone can help with. I am trying to incorporate NASA FIRMS VIIRS into my geospatial analysis but am struggling with the API call.

The GET request node connects to NASA FIRMS and extracts the data I need, but I cannot figure out how to get the CSV writer to format the data correctly. When I write the API response to a csv and open it in Excel I get a single cell with the headings separated by comas, as well as multiple rows of data.

Using the text to column function in excel somehow deletes the data but keeps the headers. In the subsequent rows I get the remaining data (image and file attached here).

Can anyone recommend a better way to make this API call / format th CSV within Knime (avoiding manual data manipulation in Excel). Would it be better to manage this (writing to a db for example)?

I am very new to Knime but am determined not to use anything else if I can avoid it.

NASA_FIRMS-test.csv (380.6 KB)

[MartinDDDD]

If you have the data in a proper table after the API call and you want to work with it in Excel - why not use the Excel Writer node instead of the CSV Writer node?

In general CSV = Comma Separated Values so it should not be a surprise that when you open it, you see strings with commas in between. If other CSVs display “better” - maybe your OS uses “;” instead of “,” to recognise separation in CSVs?

If Excel / CSV is just a medium for you to store the data to further use it in your workflow I can recommend to use the Table Writer Node (and Table Reader Node). These store them in a KNIME-specific .table format…

Other options could be to use sqlite DB or H2O DB, which are supported in KNIME.

[knimediger]

@Kyle_Mac

First of all welcome to the world of KNIME.

The attached csv file looks great and when I open it using LibreOffice I instantly get the expected result in form of a table.

So please check whether the settings in Excel are correct for importing this csv file.
This file is using the “,” (comma) as separator.

HTH

[Kyle_Mac]

Thank you for the welcome. The DB option isn’t available for me at the moment, but I tried writing it to an excel file only to get the following error, “Execute failed: The maximum length of cell contents (text) is 32767 characters”.

I think the way Knime is reading the API call is causing the error. The call https://firms.modaps.eosdis.nasa.gov/api/country/csv/[PRIVATE API KEY]/VIIRS_SNPP_NRT/UKR/7 is returning a text file with no errors, and with no errors when I open it in excel and convert ot csv.

[MartinDDDD]

What does the output table look like that comes out of the Get Request Node?

Based on your additional comments it sounds like you have one 1 column x 1 row setup.

In that case you may be able to “unwind” that cell into a proper table with some additional nodes. E.g. you could:

1. use Cell Splitter to split by “row delimiter” (might be \n) - After this you will have many new columns and still one row
2. use Table Transposer to flip the table 90° so that what was previously your columns turns into rows
3. use Cell Splitter again to split by “column delimiter” (should be ,) - after this you should have the proper table…

If you can provide some anonymised sample data as an example I’m happy to have a go.

[mlauber71]

@Kyle_Mac have you considered importing the resulting file into KNIME as CSV and then exporting it as an Excel file so as to directly get the Excel format?

[knimediger]

@Kyle_Mac

I’m struggling a bit with the actual setup you are using at the moment.

You added a file to your first post.
How did you get this file? Was this a manual action via REST call?
Did you already try to read this file using KNIME? What node did you use?
If you used KNIME for reading: How does your workflow look like?

Another question coming to my mind: What is the xls file used for? Do you use this for further data wrangling? Is it used for visualisation purposes?

[Kyle_Mac]

Knimediger, my workflow went…

1. get MapKey API from NASA FIRMS (free, just need reg details)
2. test with Postman - works fine, and generates txt file without errors

In Knime… (screenshot below)
3) add API request to GET Request node
4) add CSV Writer
5) add CSV Reader

So far so good. But when I open the csv (attached in my first post) I get a clear error where the first 386 rows are written in 1 cell, along with tthe first two cells of row 387. AFter that it is all ok.

[MartinDDDD]

I see - you get all your contents in one cell and when you then write it as CSV, it writes the full content “into one cell”.

Can you try this:

1. Configure your CSV writer as shown in the image 1 below - no Quote Chars etc, no headers etc and save.
2. Use a CSV reader and configure it as per below. Here it is important that if the standard row-delimiter does not work (i.e. it does not split content into rows…) that you go back to your get request node, expand the table, copy the content of the table into a text editor, then you manually go to the end of what should be one line and copy the character between the end of one line and the beginning of the next (will likely appear “invisible”. You then copy that character in as your custom row delimiter. For me that turned it from an input as per image 2 to what you see in image 3

image 1:

image 2:

image 3:

[Kyle_Mac]

Martin it works! Thank you. Of course it was simpler than I thought.