Upload and Parse CSV via VisualForce

Update August 22nd, 2015 – Please note that this is an experiment in reading a CSV and pulling the data into Visualforce. In no way should this be part of a Production-ready solution as it DOES NOT SCALE. You will hit limits as the number of rows in the CSV increases. As a solution, this would NOT be considered a best practice.

I recently had to develop a Visualforce page that allowed a Salesforce user to upload a CSV file and then generate some records from the parsed values. Yes, I know that is what the Data Loader is for, but the intended user here was not technically adept. And I thought it was an interesting experiment.

Csv upload

The Salesforce Developer Wiki already had a sample class for parsing the file – http://wiki.developerforce.com/index.php/Code_Samples#Parse_a_CSV_with_APEX . So, I had to just create the VF page and my controller. Though, I did modify the parsing method from the Wiki to use the carriage return in the string split. CSV’s produced on my Mac didn’t parse correctly using the newline ‘\n’. I found a good post here explaining how Windows and Mac/Unix handle carriage returns differently. Here’s the results.

Visualforce page: uploadCSV

<apex:page controller=”uploadCSVcontroller” >
  <apex:form >
  <apex:inputFile value=”{!contentFile}” filename=”{!nameFile}” /><br/>
  <apex:commandButton value=”Upload” id=”theButton”/>
  </apex:form>
  <apex:outputPanel id=”results”>
  <p>nameFile: {!nameFile}</p>
  <p>rowCount: {!rowCount}</p>
  <p>colCount: {!colCount}</p>
    <table title=”CSV Output” border=”1″ width=”100%”>
       <apex:repeat value=”{!results}” var=”row”>
           <tr>
               <apex:repeat value=”{!row}” var=”cell”>
                   <td> {!cell} </td>
               </apex:repeat>
           </tr>
       </apex:repeat>
     </table>
  </apex:outputPanel>
</apex:page>

Apex Class:

public class uploadCSVcontroller {

    public Blob contentFile { get; set; }
    public String nameFile { get; set; }
    public Integer rowCount { get; set; }
    public Integer colCount { get; set; }

    public List<List<String>> getResults() {
        List<List<String>> parsedCSV = new List<List<String>>();
        rowCount = 0;
        colCount = 0;
        if (contentFile != null){
            String fileString = contentFile.toString();
            parsedCSV = parseCSV(fileString, false);
            rowCount = parsedCSV.size();
            for (List<String> row : parsedCSV){
                if (row.size() > colCount){
                    colCount = row.size();
                }
            }
        }
        return parsedCSV;
    }
    /*
    Credit to
    http://wiki.developerforce.com/index.php/Code_Samples#Parse_a_CSV_with_APEX
    */
    public static List<List<String>> parseCSV(String contents,Boolean skipHeaders) {
        List<List<String>> allFields = new List<List<String>>();

        // replace instances where a double quote begins a field containing a comma
        // in this case you get a double quote followed by a doubled double quote
        // do this for beginning and end of a field
        contents = contents.replaceAll(‘,”””‘,’,”DBLQT’).replaceall(‘”””,’,’DBLQT”,’);
        // now replace all remaining double quotes – we do this so that we can reconstruct
        // fields with commas inside assuming they begin and end with a double quote
        contents = contents.replaceAll(‘””‘,’DBLQT’);
        // we are not attempting to handle fields with a newline inside of them
        // so, split on newline to get the spreadsheet rows
        List<String> lines = new List<String>();
        try {
            //lines = contents.split(‘\n’); //correction: this only accomodates windows files
            lines = contents.split(‘\r’); // using carriage return accomodates windows, unix, and mac files
            //http://www.maxi-pedia.com/Line+termination+line+feed+versus+carriage+return+0d0a
        } catch (System.ListException e) {
            System.debug(‘Limits exceeded?’ + e.getMessage());
        }
        Integer num = 0;
        for(String line: lines) {
            // check for blank CSV lines (only commas)
            if (line.replaceAll(‘,’,”).trim().length() == 0) break;

            List<String> fields = line.split(‘,’); 
            List<String> cleanFields = new List<String>();
            String compositeField;
            Boolean makeCompositeField = false;
            for(String field: fields) {
                if (field.startsWith(‘”‘) && field.endsWith(‘”‘)) {
                    cleanFields.add(field.replaceAll(‘DBLQT’,'”‘));
                } else if (field.startsWith(‘”‘)) {
                    makeCompositeField = true;
                    compositeField = field;
                } else if (field.endsWith(‘”‘)) {
                    compositeField += ‘,’ + field;
                    cleanFields.add(compositeField.replaceAll(‘DBLQT’,'”‘));
                    makeCompositeField = false;
                } else if (makeCompositeField) {
                    compositeField +=  ‘,’ + field;
                } else {
                    cleanFields.add(field.replaceAll(‘DBLQT’,'”‘));
                }
            }

            allFields.add(cleanFields);
        }
        if (skipHeaders) allFields.remove(0);
        return allFields;      
    }

}

 

 

Advertisements
This entry was posted in Salesforce, Web/Tech and tagged , , , , . Bookmark the permalink.

2 Responses to Upload and Parse CSV via VisualForce

  1. Amit Kumar Singh says:

    Thank you.
    Great Work 🙂
    it works fine but i want something different from this,
    here you are displaying .csv files’s data in the same visual force page, i want to select csv file in this page and want to display its content in another vf page (renderas=”pdf”), so i can display csv’s data into pdf format,
    could you please help me.
    Thanks,
    Amit Singh

    Like

  2. Lynn G says:

    It is important to know that the .csv has to be UTF-8 or you will get a Blob error.
    I ran this and got a REGEX error which apparently you hit with too many lines in the file. So this will not work with large files. Has anyone added the financial force solution to this?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s