How to convert uploaded CSV to JSON on the server

[heidi]

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]

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

[heidi]

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]

How can it be done using JS?

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.

[heidi]

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.

[heidi]

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.

[PeterM]

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

[heidi]

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.

[PeterM]

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

[heidi]

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?

[serger]

CSV Data Source to JSON output

If you just want to use it in an application as JSON but don’t need to convert it on the server and keep it as a CSV data source, you can do this:

1. Create your CSV data source
   
2. Create an asset listing that lists data source record sets
   
   
3. Point to to your csv as the root node
   
   
4. Set the page content to the start of a json array
   
   
5. Set the default format to the content of a json array item
   
   
6. Voila json
   
   

Edit:
I made a mistake here, you have to create a position format for -1 (the last row) to exclude the last comma. Ref: https://matrix.squiz.net/manuals/asset-listing/chapters/position-formats

Also make sure your design for that asset listing is blank (call it json design and assign it to your asset listing)

<MySource_PRINT id_name="global" var="content_type" content_type="application/json" />
<MySource_AREA id_name="page_body" design_area="body" >
<MySource_SET name="format" value="low_bandwidth" />
</MySource_AREA>

[LisaB]

you have to create a position format for -1 (the last row) to exclude the last comma.

Hey Serge, one small suggestion for your very thorough answer
You could avoid having a second format by only adding the comma if it’s not last in the asset list, e.g.
%begin_asset_is_list_last%%else_asset%,%end_asset%