?
Solved

Coldfusion converting from json to csv format

Posted on 2011-10-20
8
Medium Priority
?
1,287 Views
Last Modified: 2013-12-24
Hi,

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;
		
	   $.ajax({
      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(myErrorMsg);
		 }
		 else{
			alert(successMsg + '\n' + '****************' + 'School Name: ' + schoolname); 
		 }
      }
    });  
  }
</script>


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

<cfscript>
  myStructure["errorMsg"] = trim(errorMsg);
  myStructure["success"] = success;
</cfscript>

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

Open in new window

0
Comment
Question by:roger v
  • 4
  • 3
8 Comments
 
LVL 25

Accepted Solution

by:
dgrafx earned 1800 total points
ID: 37005363
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
0
 
LVL 1

Author Comment

by:roger v
ID: 37006010
@dgrafx:

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?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 37006068
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)?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:roger v
ID: 37006178
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?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 37006366
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">
#somequery[ii][somequery.currentrow]#,
</cfloop>
#Chr(10)#
</cfloop>
</cfsavecontent>
<cfset myFile="d:\website\abc\test.csv">
<cfset fileWrite(myFile,xxx)>?
0
 
LVL 1

Author Comment

by:roger v
ID: 37006455
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?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 37006860
yes
lets say you do
<cfif whatever>
convert to json and return
<cfelse>
plaintexter
</cfif>

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


0
 
LVL 1

Assisted Solution

by:donniev
donniev earned 200 total points
ID: 37007097
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
This article discusses how to implement server side field validation and display customized error messages to the client.
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…
Suggested Courses

809 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