Coldfusion converting from json to csv format

Posted on 2011-10-20
Last Modified: 2013-12-24

I have a coldfusion 9 file that is accessed via jquery ajax call. It does some db processing and then sends the output to a cfc called json.cfc which serializes and deserializes and returns output as json. I then use $.parseJSON to parse the returned json data and display a javascript alert box accordingly.

I need to now add additional functionality to this module so based upon the parameter, I can either send the data back in json format or write to a plain text (csv) file. How do I do this?

My existing code is attached.
the file that makes the ajax call and recieves the input(indexer.cfm):
	function getData(sd){
	 	var localsd = sd;
      type: "POST",
      url: "dataAction.cfm",
      data: "schoolName=" + localsd, "outputFormat=json",
      success: function(sname) {
        var data = $.parseJSON(sname);        
       	var myErrorMsg = data.errorMsg;
		var successMsg = data.success;
		var schoolname = data.schoolname;
		 if (myErrorMsg != "") {	
			alert(successMsg + '\n' + '****************' + 'School Name: ' + schoolname); 

this is my file that does the actual processing and sends back the data (dataAction.cfm):
<!---a bunch of database processing 

  myStructure["errorMsg"] = trim(errorMsg);
  myStructure["success"] = success;

<cfif outputFormat is "json">
	<cfinvoke component="cfc.JSON" method="encode" data="#myStructure#" returnvariable="result" />
<cfelseif outputFormat is "plaintext">
	<!---this is where I'm stuck and don't know how to output the content in csv--->

Open in new window

Question by:roger_v
    LVL 24

    Accepted Solution

    i would think that in your success block you would say if json or plaintext.
    it looks like you are now converting to json either way then wanting to convert it back to plaintext.
    in the if block at the end of your code you can do the same as you are doing for if json and just write your text file is not (csv that is)

    hope that helps somewhat
    LVL 1

    Author Comment


    Yes that is what I need to do. Right now I pass in the outputFormat=json from the ajax call above. I'll make another call where the parameter will be outputFormat=plaintext that will hit the <cfelse> statement. My question is: once i get to the 2nd cfif, how do I take the returned json and convert to csv?
    LVL 24

    Expert Comment

    ok well what I meant was it will not then be JSON.
    if you look at your script you are telling it to convert to JSON.
    do you see what I mean?
    without the outputFormat=json it will not be JSON so it doesn't need to be converted.
    What type of output do you get if you do not add the outputFormat = json and var data = $.parseJSON(sname)?
    LVL 1

    Author Comment

    the outputformat = json is just my parameter for me to distinguish from the action page what output is being expected. So I guess I need to have the entire action page as a udf where I make the ajax call and then the action page's if clause does the processing and then passes back the csv.

    What I'm unable to grasp is once the action page gets the parameters, how to I send the content that I get from db processing back in csv text?
    LVL 24

    Expert Comment

    1. i got confused - i was thinking returnFormat that i use with json.

    so on dataaction.cfm - it looks like if outputformat = json then you convert - otherwise it should still be text so no conversion needed.

    then do you mean how do i write a csv file?
    other than <cfset fileWrite(myFile,myStructure)>?

    or do you mean how to put in your delimiters?
    <cfsavecontent variable="xxx">
    <cfloop query="somequery">
    <cfloop list="#columnList#" index="ii">
    <cfset myFile="d:\website\abc\test.csv">
    <cfset fileWrite(myFile,xxx)>?
    LVL 1

    Author Comment

    Yes you're on the right track dgr. Is there any way that I can send the output from the cfif as text? In other words, in the cfif, can I create a UDF so that the value of myStructure (which is usually a single word) can be passed to the calling page?
    LVL 24

    Expert Comment

    lets say you do
    <cfif whatever>
    convert to json and return

    so now your sname var will = 'plaintexter' or whatever other word(s) you put there.

    LVL 1

    Assisted Solution

    If I wanted to get csv data back to client I would create the data as a string as dqrafx has outlined, then jason serialize that string and return the serialized string to the client. When the client deserializes the return it will have the csv string. If you do it that way the client will always get a json string. If you serialized the struct the client will get the object back, if csv it will get the string.

    What will the client do with the data such that it needs to be in csv? Hopefully not parsing it back into a js object.

    I would also note that if your data itself has commas embeddeded you will need to modify dqrafx's routine to create the csv. In general it is much easier to pipe delimit your data.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Superior storage. Superior surveillance.

    WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

    If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
    Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
    The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now