function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JBerryJBerry 

Removing characters from an Apex query

Hi,
I'm currently pulling my data using the scripting toolkit through Dos, and saving it into a .CSV file.  The problem I am running into is that some of the fields contain comma's in them and its forcing part of that field into the next column.  I know there is a way to run an If...Then loop to check each variable and remove any comma's, but I'm  drawing a total blank on getting it to work correctly.  Here is my current code for the query:

Code:
function Results(queryResult) {
    if (queryResult.size > 0) {
        var output = "";
        var records = queryResult.getArray("records");
        output += "Start Time" +","+ "Opportunity" +","+ "Implem" +","+ "ProxiAg" +","+ "HybridOp" +","+ "RemoteOp" +","+ "A/V" +","+ "Contact" +","+ "Phone#" +","+ "Launch" + "\r\n";
        for (var i=0;i<records.length;i++) {
            var account = records[i];
            output += account.Start_Time_CST__c + "," + account.Name + "," + account.Implementation__c + "," + account.Proxiagent_Name__c + "," + account.Hybrid_Software_Operator__c + "," + account.Remote_Operator__c + "," + account.Audio_Video_Location__c + "," + account.Client_Contact_Name__c + "," + account.ProxiAgent_Contact_Numbers__c + "," + account.Launch_Support__c + ",test" + ",test" + "," + account.Launch_Support_Notes__c + "," + account.Follow_up_support_notes__c +"\r\n";
        }
        WScript.echo(output);
    }else WScript.echo("No records matched.");
    ToolKit.Quit();
}
var callback = {onSuccess : Results, onFailure : function(e){WScript.echo(e)}};
var queryResult = sforce.connection.query("Select Start_Time_CST__c,Name,Implementation__c,Proxiagent_Name__c,Hybrid_Software_Operator__c,Remote_Operator__c,Audio_Video_Location__c,Client_Contact_Name__c,ProxiAgent_Contact_Numbers__c,Launch_Support__c,Event_Date__c,Follow_up_support_notes__c,Launch_Support_Notes__c from Opportunity where Event_Date__c=2007-06-25", callback);

Right now the fields Name, Follow_up_support_notes__c, and Launch_Support_Notes__c have the possibility of a user entered comma in them. 

Any idea on the string method to use to run through these fields and remove any comma's in the data pulled from them?

Ron HessRon Hess
put an escaped quote before and after any field that is found to contain a comma

so
 if ( has a comma) {
   output = "\"" + field + "\"" + ...
}

should work ,
JBerryJBerry
I figured out a workaround to make this easier.  When I'm exporting from salesforce, I have just told it to use "|" instead of "," between the fields.  Now when I'm importing it into my SQL database, I have modified it to look for a "|" instead.  I figured this is easier to just go ahead and change my delimiter then write string handling scripts.

My next question is though, how do I reupload these files to Salesforce?  I exported from my SQL database, and I have the opportunity ID's located with each row.  I know how to create new opportunities, but I cannot locate how to update via CSV based on the opportunity ID I pulled.

I have seen the InsertFromArray command, but am not aware of it's capabilities on updating rather then creating.
SFDChereSFDChere

Hello

 

I am having the same problem .......trying to generate CSV file from Apex code and some of my fields have a ',' which is causing the issue. How can I get rid of it. How to specify from the apex code not to use the ',' delimiter.

 

Help plz.....