Solved

How to change default type(JSON) to csv while taking data using api

  • 27 April 2020
  • 5 replies
  • 125 views

I want to get csv file after hitting the api,by default i am getting json format.Could anyone tell what changes do i need to make for the given example.

 

curl https://mixpanel.com/api/2.0/jql \
-u ce08d087255d5ceec741819a57174ce5: \
--data-urlencode params='{"from_date":"2016-01-01", "to_date": "2016-01-07"}' \
--data-urlencode script='function main(){ return Events(params).groupBy(["name"], mixpanel.reducer.count()) }'
icon

Best answer by stephanie 30 April 2020, 20:04

Hi @harsh,

 

I mentioned the Mixpanel-Api Module as an alternative to export event data or people data through python script on CSV format. 

If you run your curl command along with  --data "output_format=csv"  at the end it should now put out the data in csv format. 

curl https://mixpanel.com/api/2.0/jql \
-u ce08d087255d5ceec741819a57174ce5: \
--data-urlencode params='{"from_date":"2016-01-01", "to_date": "2016-01-07"}' \
--data-urlencode script='function main(){ return Events(params).groupBy(["name"], mixpanel.reducer.count()) }'\
--data "output_format=csv"

The JQL endpoint's default  output is in JSON as you have tried, and we don't quite have official documentation on supporting direct output of CSV for this endpoint. The reason is that processing for CSV takes additional time and JQL has a maximum internal runtime, so sometimes queries might not finish if you add the option to convert the output to CSV.

That being said, that should only apply to large datasets that are already taking a long time to process and download. If your dataset is relatively small and completes within 60 seconds or so, you should be fine using the CSV option.  For larger data sets you can use the alternative UI button or API Module. 

 

Hope this helps! 

View original

5 replies

Userlevel 5
Badge +4

Hi @harsh,

 

You can export data from your project using our API module along with Python to specify the format you want to export your data. Here is an old community post where monicaerdmann shares a python script to export you events in csv format. 

I noticed you are using the JQL API to get this data. You probably know this but just in case I thought it would be worth mentioning that you can export JQL reports in CSV format as well. 

After running the JQL the export button will be available to export data in csv format

 

Hope this helps!

Hi @stephanie ,i would like to clarify my doubt.

I am able to get csv or json using the interactive interface.

But while writing the shell command/curl command ,i got json format by default.

I have gone through mixpanel api documentation,but no where it is mentioned how to change the default format type to csv .Could you please make changes by giving an example of the above written code.

Thanks in advance.

 

 

 

curl https://mixpanel.com/api/2.0/jql \ -u ce08d087255d5ceec741819a57174ce5: \ --data-urlencode params='{"from_date":"2016-01-01", "to_date": "2016-01-07"}' \ --data-urlencode script='function main(){ return Events(params).groupBy(["name"], mixpanel.reducer.count()) }'

Userlevel 5
Badge +4

Hi @harsh,

 

I mentioned the Mixpanel-Api Module as an alternative to export event data or people data through python script on CSV format. 

If you run your curl command along with  --data "output_format=csv"  at the end it should now put out the data in csv format. 

curl https://mixpanel.com/api/2.0/jql \
-u ce08d087255d5ceec741819a57174ce5: \
--data-urlencode params='{"from_date":"2016-01-01", "to_date": "2016-01-07"}' \
--data-urlencode script='function main(){ return Events(params).groupBy(["name"], mixpanel.reducer.count()) }'\
--data "output_format=csv"

The JQL endpoint's default  output is in JSON as you have tried, and we don't quite have official documentation on supporting direct output of CSV for this endpoint. The reason is that processing for CSV takes additional time and JQL has a maximum internal runtime, so sometimes queries might not finish if you add the option to convert the output to CSV.

That being said, that should only apply to large datasets that are already taking a long time to process and download. If your dataset is relatively small and completes within 60 seconds or so, you should be fine using the CSV option.  For larger data sets you can use the alternative UI button or API Module. 

 

Hope this helps! 

Hi @stephanie Thanks a lot for taking your time ,however i have also faced while quering for a large time frame,whole data may not get Processed.Is there any flag parameter by which we can know that whole data is not processed or not.

Faced this issue a lot of time.As of now i download 2-3 times same file and check if the file size is same.

Userlevel 5
Badge +4

No know flag for it on my end 😞 . As shared, to export in CSV format in JQL is not something that has official documentation as it does have its query limits.

If you would like to have support of JQL export in CSV format for large sets, you can submit an Idea here and I would be happy to log a product gap for our engineering team team to review and consider having in their road map. 

Reply