Is there a "Text to Columns" function or app (similar to the Text to Columns Data Tool in Excel) in Salesforce?

Some of my data sets are too large for Excel to handle prior to uploading into Salesforce.

If there isn't an actual Text to Columns function / app in Salesforce, would the next best solution be the following?

1) Create a custom object that is used only parse the data in a text-to-columns manner

2) Upload my data into that parsing custom object so that it takes the "full name" column and parses it out into a "First Name" and "Last Name" column

3) Export the parsing object's data

4) Upload the cleaned up data into my Contacts object.

Let me know what you StackExchangers think! Thanks

  • 3
    Hve you thought about sanitizing your input file using a script before importing it into Salesforce? Assuming you use CSV this should be rather quick in Perl or Python.
    – FrankZ
    Nov 23, 2013 at 8:41
  • Thanks for suggesting that! I would definitely be down to do that. Can you explain further? I don't have any experience with Perl and extremely limited experience with Python so far. (I'm still learning all this.) Also - I was mostly trying to see if Salesforce could do more of the grunt work for us, especially since we're paying so much to use it at work. Nov 23, 2013 at 22:39

2 Answers 2


Fields in salesforce.com are limited to 32,768 characters in length, so trying a pure "custom object with database trigger" solution might not be viable. If you know the data will be less than 32k per row, or you can somehow meaningfully split them up across multiple fields, you could just use a custom-written trigger to parse the data and put it where it belongs. I've written a mini-CSV parser for a query builder widget, and it's really not that difficult to do. I'm not aware of any specific program that allows for splitting a single field into multiple fields during an import session at this time, although there will probably be in the future.

Generic CSV Parser, Batchable

global with sharing class RecordProcessor implements Database.Stateful, Database.Batchable<DatabaseRecord>, Iterable<DatabaseRecord>, Iterator<DatabaseRecord> {
    global RecordProcessor(String csv) {
        data = csv.split('\n');
        Pattern endPattern   = Pattern.compile(',".+[^"]$'),
                startPattern = Pattern.compile('^[^"]+,"');
        for(integer idx = 0; idx < data.size()-1; idx++) {
            Matcher endMatch   =   endPattern.matcher(data[idx]  ),
                    startMatch = startPattern.matcher(data[idx+1]);
            if(endMatch.matches() && startMatch.matches()) {
                data[idx] += data.remove(idx+1);
        headers = data.remove(0).removeStart('"').removeEnd('"').split('"?,"?');

    string[] data, headers;
    global class DatabaseRecord {
        global DatabaseRecord(String[] headers, String data) {
            fields = new Map<String, String>();
            String[] hdrs = headers.clone(), columns = data.split(',');
            for(Integer idx = 0; idx < columns.size() - 1; idx++) {
                if(columns[idx].startsWith('"') && !columns[idx].endsWith('"')) {
                    columns[idx] += columns.remove(idx+1);
            while(!hdrs.isempty() && !columns.isempty()) {
                fields.put(hdrs.remove(0), columns.remove(0));
        Map<String, String> fields;

    global Boolean hasNext() {
        return !data.isempty();

    global DatabaseRecord next() {
        return new DatabaseRecord(headers, data.remove(0));

    global Iterator<DatabaseRecord> iterator() {
        return this;

    global Iterable<DatabaseRecord> start(Database.BatchableContext BC) {
        return this;

    global void execute(Database.BatchableContext BC, DatabaseRecord[] records) {
        // Process the records here

    global void finish(Database.BatchableContext bc) {
        // Report any errors here
  • Would this be done within Salesforce or separately as a "data cleanup" step before anything gets uploaded to Salesforce? I'm looking for something that can be done within Salesforce to decrease the number of steps taken prior to uploading all the data into the system. Nov 25, 2013 at 19:21
  • @dataprointhemaking Since you asked, I'm going to give you a freebie. Updating answer with a Batchable CSV parser. You should note that the maximum limit of a file would probably be just under 6Mb with the current governor limits, however-- it's likely that Excel could handle any file this setup could plus much more. However, you'd then move the processing from Excel to salesforce.com, which may be more efficient.
    – sfdcfox
    Nov 25, 2013 at 20:07

Here's a tiny example of how this could be done using Perl. As an example, I take the following CSV file called data.csv:

1,"Ernest Hemingway",1899
2,"Arthur Miller",1915
3,"Thomas Mann",1875

With a Perl one-liner the names could be split at the blanks and written as two separate fields:

perl -ne '@c=split(/,/); $c[1] =~ s/\s/","/gm; print join(",", @c);' < data.csv > datanew.csv

The result would look like this:


The perl command runs like this on a Unix/Linux machine. Using Perl on Windows will require some changes due to the quotes used. Also, this does not need to be run as a one-liner. The Perl script can also be written into a file:

#!perl -w

while (<>) {                     # read records from stdin
    @columns = split(/,/);       # split fields at commas into an array
    $columns[1] =~ s/\s/","/gm;  # replace blanks in 2nd field with ","
    print join(",", @columns);   # print all elements of the array joined by commas

and then run: perl filename.pl < data.csv > datanew.csv

The complexity of your file preparation is mainly limited by your scripting abilities. If you prefer to use Python or any other scripting language, that's obviously also a good choice.

Once you've done this, why not use your script with Salesforce's Bulk API to load the generated file into your org?

  • Just wanted to confirm: This would be done outside of Salesforce - separately as a "data cleanup" step before anything gets uploaded to Salesforce - correct? I'm looking for something that can be done within Salesforce to decrease the number of steps taken prior to uploading all the data into the system. Nov 25, 2013 at 19:23
  • Yes, this would have to be done outside of Salesforce.
    – FrankZ
    Nov 25, 2013 at 19:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .