3

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 '13 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. – dataprointhemaking Nov 23 '13 at 22:39
4

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);
                idx--;
            }
        }
        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);
                    idx--;
                }
            }
            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. – dataprointhemaking Nov 25 '13 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 '13 at 20:07
2

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:

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

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. – dataprointhemaking Nov 25 '13 at 19:23
  • Yes, this would have to be done outside of Salesforce. – FrankZ Nov 25 '13 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.