How to convert uploaded CSV to JSON on the server


#1

I have a CSV file which I have uploaded in Squiz and need to convert that to JSON on server. How can it be done using JS? Or is there any other functionality within Squiz? I’m uploading CSV as a file, not CSV data source.


(Tbaatar) #2

Can you not just use something like this: https://www.csvjson.com/csv2json


#3

Hi…thanks for the link…I checked that too but the requirement is that the CSV file will be uploaded in Squiz by the business and need to convert that to JSON on the fly. Can I use plain JavaScript to convert CSV and then access the data using ajax/jQuery??


(Douglas (@finnatic at @waikato)) #4

http://techslides.com/convert-csv-to-json-in-javascript … where the comments provide numerous alternatives.

Is there a reason why you’re not using a CSV data source? A data source asset would allow you to point an asset listing at the data, and with the right markup for each row you could generate the corresponding JSON.


#5

Many Thanks for the link…I have this requirement of converting CSV to JSON using server side JS. I think your link should do the necessary.


#6

How can we use server side javascript resource to leverage the above functionality? That is, convert CSV to JSON using server side JavScript and then manipulate the data using jQuery or Ajax. Please someone help.


(Peter McLeod) #7

Hi
From what I understand of your requirement it seems like you need an asset that can act as a json api. Try the following:

Create a new REST Resource JS asset.

Give the HTTP request a url of:
%globals_get_file^as_asset:asset_url%

In the Javascript processing section the javascript can be the function previous referenced by Douglas:

var data = _REST.response.body;
function csvJSON(csv){

  var lines = csv.split("\n");
  var result = [];
  var headers = lines[0].split(",");

  for(var i = 1; i < lines.length; i++){
	  var obj = {};
	  var currentline = lines[i].split(",");
	  for(var j = 0;j < headers.length; j++){
		  obj[headers[j]] = currentline[j];
	  }
	  result.push(obj);
  }
  return JSON.stringify(result); 
}

var json = csvJSON(data);
print(json);

You can use it by passing a csv file asset id to it in the URL:
http://url-to-restjs-asset/?file=1234

If want want to do stuff on the client side then point the ajax calls to that url to get the JSON version of the data. You’ll need to make sure the REST JS asset doesn’t have any designs/layouts applied that will affect its output.

Thanks
Peter


#8

Hi Peter,

Thanks for the above. Can’t get this link to work - http://url-to-restjs-asset/?file=1234 and when I pass %globals_get_file^as_asset:asset_url% in the URL field, it gives me Matrix warning.
Please help.
Thanks.


(Peter McLeod) #9

Hi
That link just an example URL not an actual one - once you create your REST JS asset, you would use its URL and pass the query parameter to it.
Thanks
Peter


#10

Hi,
Ok, so my understanding so far is I have a separate CSV file uploaded under a page and a REST resource JS file where I have provided the URL as above mentioned and further JS processing function. Then, how do I pass the asset id of the CSV to REST resource? eg. http://url-to-restjs-asset/?file=asset id ? and where?