-2

I am trying to convert SAFECAST API JSON to a CSV suitable for spreadsheet programs like Excel etc. to prepare some data summaries etc. I have semi-manual WGET script to download several JSON files based on my filters.

the JSON files are now available from this shared folder: https://www.mediafire.com/folder/d0f4ztqv19xzl/SAFECAST_API_scripts

I am able to convert single JSON to usable CSV using jq (Ubuntu linux):

cat bgeigie_imports_001.json |  jq -r '["id", "user_id", "source url", "status", "submitted", "measurements_count", "approved", "created_at", "updated_at", "name", "description", "lines_count", "credits", "height", "orientation", "cities", "subtype", "rejected"], (.[] | [.id, .user_id, .source.url, .status, .submitted, .measurements_count, .approved, .created_at, .updated_at, .name, .description, .lines_count, .credits, .height, .orientation, .cities, .subtype, .rejected]) | @csv ' > bgeigie_imports_001.csv

but I am unable to make it work automatically and convert all json files in the folder (this thread did not help).

any ideas? Loading the file names for input/output from a txt file might also help as I have a list from the wget script...

appreciate any advice, unfortunately my skills are limited

thanks

PS: merging the json first using jq -s '.' *.json > All_data.json produces somehow different file and the jq command above fails to work properly

Improve this question
6
  • Can you just loop over all the input files? for file in *.json; do ... ; done? Commented Aug 12, 2024 at 15:19
  • 1
    In what way did this thread not help, what happened when you tried to loop? Commented Aug 12, 2024 at 16:59
  • @tink honestly, I was not able to properly combine the mentioned example with my command to make it work... :-( Commented Aug 12, 2024 at 17:58
  • 1
    Can you show how you tried to combine it? It should just be something like for file in *.json; do jq '.' "$file"; done > all_data.json Commented Aug 12, 2024 at 21:18
  • @Barmar as mentioned in the post I used: jq -s '.' *.json > All_data.json - the resulting json file seemed to be OK on the first sight but the CSV conversion produced unusable file - just collumn names if I remember correctly. Commented Aug 13, 2024 at 10:00

1 Answer 1

Reset to default
1

From your post it's quite unclear how many JSON files you have, how they correlate to the API link and what they contain, so I can't really produce a tested working example; you also mentioned the final goal of a single CSV? I'd expect e.g. the following to work for you:

find . -maxdepth 1 -type f -name \*.json -print0 | xargs -0 -I"^" jq -r '["id", "user_id", "source url", "status", "submitted", "measurements_count", "approved", "created_at", "updated_at", "name", "description", "lines_count", "credits", "height", "orientation", "cities", "subtype", "rejected"], (.[] | [.id, .user_id, .source.url, .status, .submitted, .measurements_count, .approved, .created_at, .updated_at, .name, .description, .lines_count, .credits, .height, .orientation, .cities, .subtype, .rejected]) | @csv' ^ >> data.csv
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

OK, to clarify it - current data filter for one particular task: api.safecast.org/en-US/… gives me 22 pages e.g. 22 json files - I currently generate the links for Wget in LO Calc as I cannot easily make wget to "try" to download all 22 pages just with one link. The JSON files contain exactly the same structure as the example in the original question.
Yes, the final goal is a single CSV because I need it for some data overview - how many files uploaded per month / device etc.- this will be done in Excel probably by one of my colleagues. I will check your script tomorrow, thanks much.
@Juhele - you might want to add one or two actual files (trimmed to a few working lines) to your question rather than the link to the API where you get them from. Let me know how this works out for you anyway.
@Juhele - ping?
your script gives me errors: pastebin.com/5zSHfMWm (was too long to post directly here)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.