CODEWITHSUNDEEP
rjsondataframe
user12490809
user12490809

Reputation: 89

how to get a data table from JSON Response

I'm using API in order to get the data. The response is in JSON format. As of now, I've used the below steps.

s2 <- GET("https://api2.elasticgrid.com/api/v1/analytics/vendor/partnerengagement/advanced/all") 

char <- rawToChar(s2$content)

I'm getting the output as

char 

[1] "{\n\t\"data\":  [\n\t\t{\"Entity Type\":\"transfer_fund\",\"Entity ID\":\"136023000000199105\",\"Reference Number\":\"\",\"Transaction Number\":\"6\",\"Transaction Amount (BCY)\":\"INR 2,600.00\",\"Account ID\":\"136023000000000361\",\"Debit or Credit\":\"debit\",\"Transaction Date\":\"15 Apr 2016\",\"Currency Code\":\"INR\",\"Tax ID\":\"\",\"Taxable Amount (BCY)\":\"INR 0.00\",\"Accrual Transaction ID\":\"136023000000199113\",\"Transaction Amount\":\"-INR 2,600.00\",\"Customer ID\":\"\",\"Vendor ID\":\"\",\"Transaction Amount (FCY)\":\"INR 2,600.00\",\"Taxable Amount (FCY)\":\"INR 0.00\",\"Last Modified Time\":\"2016-05-17 09:38:27\",\"Transaction ID\":\"136023000000199107\",\"Reference No.\":\"\",\"Project ID\":\"\",\"Account Name\":\"Petty Cash\",\"Account Type\":\"Cash\"}.....

How to convert this into a data table?

Thanks.

Upvotes: 0

Views: 136

Answers (1)

xwhitelight
xwhitelight

Reputation: 1579

I can't access the address but to read JSON to R object, use the fromJSON() function from the jsonlite package:

data <- jsonlite::fromJSON(char)

Update: The file has problems with some special characters. You need to replace them first:

char <- gsub("\t", " ", char, fixed = T) # ideally we need to replace tab characters in strings to '\t' but it's not possible to do that since it'd replace tab characters outside strings as well and cause problems
char <- gsub("\\'", "\'", char, fixed = T)
data <- jsonlite::fromJSON(char)$data

Upvotes: 2

Related Questions