Error in extracting Comtrade data using API call in Stata

[Siddhant Marur]

Hello all

I am trying to extract data from the UN Comtrade database using their API (https://comtrade.un.org/data/doc/api/). However I consistently get an error message stating that the file was not found- which is strange considering the API call by itself seems to be working fine (when used in a browser for example). The code and error message are given below. I am aware of an existing thread on the same issue, but the matter does not seem to have been resolved there- I am trying to apply wbuchanan's advice to use jsonio by reading up on the documentation of the package and understand how it works. But if anyone has a simpler solution or any idea of what could be causing this issue, needless to say I would appreciate your input.

 import delimited "http://comtrade.un.org/api/get?r=all&freq=A&ps=all&px=H1&p=all&rg=2&cc=AG4&fmt=csv"

file http://comtrade.un.org/api/get?r=all&freq=A&ps=all&px=H1&p=all&rg=2&cc=AG4&fm t=csv not found
server says file temporarily redirected to https://comtrade.un.org/api/get?r=all&freq=a&ps=all&px=h1&p=all&rg=2&cc=ag4&fm t=csv
could not open url
r(603);

Attempt to extract using jsonio in JSON format

jsonio kv, file("http://comtrade.un.org/api/get?r=all&freq=A&ps=all&px=H1&p=all&rg=2&cc=AG4&fmt=csv")

--Break--
r(1);

Any thoughts on why could a command give a 'Break' return without my explicit instruction?

[Robert Picard]

If I try your API call in a browser, I get a file download named "comtrade.csv" which contains:

Query complexity: your query is too complex. Please simplify.
Both 'all' reporters and 'all' partners may not be selected. Select a different reporter or partner.
Resultset count: 0

Skimming the web page you linked to, I found a sample API call that saves the data in a CSV format. I suggest you download the results from the API query separately and then import into Stata. This way, you can open the results in any editor or spreadsheet program:

copy "https://comtrade.un.org/api/get?max=50000&type=C&freq=A&px=HS&ps=2013&r=826&p=0&rg=all&cc=AG2&fmt=csv" "result.csv", replace
import delimited using "result.csv", clear

describe

and the results:

. describe

Contains data
  obs:           371                          
 vars:            35                          
 size:       117,978                          
------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------------------------------------------
classification  str2    %9s                   Classification
year            int     %8.0g                 Year
period          int     %8.0g                 Period
perioddesc      int     %8.0g                 Period Desc.
aggregatelevel  byte    %8.0g                 Aggregate Level
isleafcode      byte    %8.0g                 Is Leaf Code
tradeflowcode   byte    %8.0g                 Trade Flow Code
tradeflow       str9    %9s                   Trade Flow
reportercode    int     %8.0g                 Reporter Code
reporter        str14   %14s                  Reporter
reporteriso     str3    %9s                   Reporter ISO
partnercode     byte    %8.0g                 Partner Code
partner         str5    %9s                   Partner
partneriso      str3    %9s                   Partner ISO
ndpartnercode   byte    %8.0g                 2nd Partner Code
ndpartner       byte    %8.0g                 2nd Partner
ndpartneriso    byte    %8.0g                 2nd Partner ISO
customsproccode byte    %8.0g                 Customs Proc. Code
customs         byte    %8.0g                 Customs
modeoftranspo~e byte    %8.0g                 Mode of Transport Code
modeoftransport byte    %8.0g                 Mode of Transport
commoditycode   byte    %8.0g                 Commodity Code
commodity       str233  %233s                 Commodity
qtyunitcode     byte    %8.0g                 Qty Unit Code
qtyunit         str11   %11s                  Qty Unit
qty             byte    %8.0g                 Qty
altqtyunitcode  byte    %8.0g                 Alt Qty Unit Code
altqtyunit      byte    %8.0g                 Alt Qty Unit
altqty          byte    %8.0g                 Alt Qty
netweightkg     byte    %8.0g                 Netweight (kg)
grossweightkg   byte    %8.0g                 Gross weight (kg)
tradevalueus    double  %10.0g                Trade Value (US$)
ciftradevalueus byte    %8.0g                 CIF Trade Value (US$)
fobtradevalueus byte    %8.0g                 FOB Trade Value (US$)
flag            byte    %8.0g                 Flag
------------------------------------------------------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

.

[Siddhant Marur]

Thank you for the helpful response Robert, using your code I am having some success with extracting the data.

And referring to "you can open the results in any editor or spreadsheet program", this is exactly what I want to do- maintain my do-file in such a way that it is completely replicable. I suppose I am a still a bit puzzled as to the first error regarding query complexity, since the query seemed to be working fine until a couple weeks ago. This is where the append command should come in handy.

Thanks again, and hopefully this helps the next person dealing with Comtrade data extraction.

[Rabih El Habta]

Hello all,

I am trying to extract data from the UNdata using their SDMX Web Service.

I tried the following two options:

1- import delimited

import delimited https://data.un.org/ws/rest/data/IAEG-SDGs,DF_SDG_GLH,1.9/...1.........../ALL/?detail=full&dimensionAtObservation=TIME_PERIOD&format=csv

2-

download the results from the query separately and then import into Stata

suggested in #2 by Robert Picard

copy "https://data.un.org/ws/rest/data/IAEG-SDGs,DF_SDG_GLH,1.9/...1.........../ALL/?detail=full&dimensionAtObservation=TIME_PERIOD&format=csv" "result.csv", replace

import delimited using "result.csv", clear

however, I am getting the following error message:

server reported server error

Appreciate your advise to resolve this error.

Thank you,