0

I'm a beginner to R.

I have a csv file:

key1 key2 key3 key4 key5 key6 

value value value value value  
value value value value value  
value value value value value 

etc.

I would to convert the CSV file into this format and save it in a .txt file so I can use it in a bulk upload for a server that requires this format: 

{"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"}

{"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"}

{"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"}

Here is what I have so far in Rstudio: 

#install packages
install.packages('jsonlite')


#libraries
library(readr)
library(jsonlite)


#import data
plantasia_menu_items <- read_csv("plantasia - menu_items.csv")
#View(plantasia_menu_items)

#to Json format 
inJSON <- toJSON(plantasia_menu_items)
table <- fromJSON(inJSON)
print(inJSON)

This yields the data in the following format, but I don't know how to restructure it once I have it in JSON and then save it:

{"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"} {"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"} {"key1": "value", "key2": "value", "key3": "value", "key4": "value", "key5": "value"}

It must be on a new line for the server to understand the bulk upload and create a new entry. Stuck for a while now. Any ideas on how to restructure this into a matrix?

cokeman19
  • 2,405
  • 1
  • 25
  • 40
Asger Victor Hansen
  • 1
  • Do you need https://stackoverflow.com/questions/24662303/saving-a-json-object-to-file-json/24662360 – akrun Dec 11 '19 at 01:31
  • It sounds like you're describing ndjson (new-line delimited json), is that correct? If so you can use `jsonlite::stream_out()` – SymbolixAU Dec 11 '19 at 01:37

1 Answers1

0

Here is the code that i ended up using to reformat the data. However, i am unsure how to save it to a .txt file. I cant get it into a data frame because it is not a vector. Any ideas? 

#clean the working environment
rm(list=ls())

#set working directory, keep files organized
setwd("C:/Users/")

#
#install packages('readr'}
#install.packages('jsonlite')
#install.packages("stringr")
#install.packages("stringi")

#libraries
library(readr)
library(jsonlite)
library(stringr)
library(stringi)

#import data in csv format
data <- read_csv("test.csv")
#View(data)

#Convert the CSV to Json format and verify it worked by printing
inJSON <- toJSON(data)
print(inJSON)

#Finding the locations of the separators of each data entry. 
#This will allow us to determine to know where to begin and end the substring
# generating two tables indicating each location of patterns along the whole string
start_table <- data.frame(str_locate_all(inJSON, "key1"))
#start_table

stop_table <- data.frame(str_locate_all(inJSON, ","))
#selecting row 1, col 2.
#stop_table[1,2]

#defining the function that will restructure the data for bulk upload format
restrucJSON <- function(jsonformat){

  #prepping the loop 
  count <- str_count(jsonformat, "key1") #count the number of times the loop is going to run, or the number of database entries 
  a <- 1 #there is only one row per entry 
  b <- 6 #there are 6 columns in this data, so we take every 6th position of the commas

  #loop to print
  for (i in c(1:count)) { #using the count to tell the loop how many times to run
    x <- start_table[a,1]
    y <- stop_table[b,1]
    print(substr(jsonformat, x-2, y-1)) 
    a <- a+1 #
    b <- b+6 #start counting from the next 6th comma
  }

}

#try out the function with the inJSON object
export <- restrucJSON(inJSON)
Asger Victor Hansen
  • 1