Transforming structured text file to columned CSV in R

Question

I am working on writing script to transform a large text file into a CSV file. All the data in the textfile appears in chunks like the following. Each of these chunks is separated by {}. What I am trying to do is read the first one of these chunks, take the different categories like GlobalId, MetaDataId and the rest and turn them into column headers to then sort the rest of the text file underneath these column headers.

{
    "GlobalId": 4222124650675654,
    "MetaDataId": "0e1a6cbe-fd3d-4782-af6a-fe3ffceb6a0d",
    "vendorId": "",
    "vendorName": "Vendor Test 2",
    "Uid": "vendortest1",
    "statsType": "SUSHI",
    "Url": "http://vendortest1.com/sushi/V5",
    "RunDay": 6,
    "reportName": "JR1, DB1",
    "Release": "R5",
    "supported": true,
    "deleted": false,
    "showIP": false,
    "trickle": 0
},

I tried this: converting multiple lines of text into a data frame and didn't have much success.

score 0 · Accepted Answer · answered Dec 18 '19 at 16:27

This appears to be json which can read using jsonlite

library(jsonlite)
x <- '{
  "GlobalId": 4222124650675654,
  "MetaDataId": "0e1a6cbe-fd3d-4782-af6a-fe3ffceb6a0d",
  "vendorId": "",
  "vendorName": "Vendor Test 2",
  "Uid": "vendortest1",
  "statsType": "SUSHI",
  "Url": "http://vendortest1.com/sushi/V5",
  "RunDay": 6,
  "reportName": "JR1, DB1",
  "Release": "R5",
  "supported": true,
  "deleted": false,
  "showIP": false,
  "trickle": 0
}'

Return field names:

names(fromJSON(x))

[1] "GlobalId"   "MetaDataId" "vendorId"   "vendorName" "Uid"        "statsType"  "Url"        "RunDay"     "reportName"
[10] "Release"    "supported"  "deleted"    "showIP"     "trickle"

Although if the column names are to build a data.frame then the library should be able to do the work for you:

fromJSON(x)

Transforming structured text file to columned CSV in R

1 Answers1