6

I am looking for an approach to perform the below:

[1] Read an CSV file from the local drive and insert the contents into SFDC (having a typical Browse button) [2] Once the read is complete upload the CSV into Content [3] Locally create an Error Log with the failed rows and error message (or) Write into the same CSV file the Error against each failed row

The design I am thinking off is to have a VF Page with Java Script to locate and read the rows while the Controller would do the insert. Is this design viable? If not what would be the best approach?

  • 2
    Sounds exactly like dataloader. Why not use dataloader? – Phil Rymek Mar 15 '13 at 12:42
  • Yes I think, as per the summary of my answer this option should be considered against the requirements stated. Dataloader will not retain the CSV file, nor Log the errors in another objects or attempt to update the CSV file with errors. Despite this, it is still worth considering. However... both tools require a higher barrier of entry for end users to config (indeed they require admin access for the web version and desktop tool for the other). So often custom CSV uploaders give a better user experience depending on your user type. – Andrew Fawcett Mar 15 '13 at 13:40
  • Dataloader hits every req mentioned in the question. Its a fair point that it might not provide the easiest user experience, but training users would be significantly less expensive than rewriting data loader. – Phil Rymek Mar 15 '13 at 14:09
  • Yep it does depend on the users a lot, for which we have no bearing here. I've been successful with both approach for varying types of users. Dataloader has a commandline as well, which helps preconfigure the mappings so the users don't get those wrong each time they run it. UX is very important to some. Anyway unless I've missed something, point 2) the upload of the CSV file to Content is not done automatically after the import. – Andrew Fawcett Mar 15 '13 at 17:58
  • Ah, I was reading it such that asker was performing an insert into the content object of the rows of the csv, not the csv itself. – Phil Rymek Mar 15 '13 at 18:59
7

Good question, I personally would not try to handle this in the client, it's to fragile an environment, better to upload this file and handle at the server side. Or consider adjusting your requirements / user expectations to fit Salesforce Data Loader tools, see my summary below.

Client Side

  • Client Library and Communications. You can find likely find a number of CSV parsing libraries online and then interface the results of those back to the server using JavaScript Remoting. This would avoid hitting the daily API governor or attempting to handle this via apex:actionFunction (which would run you into view state issues).
  • Scaleability and Robustness. While this has the advantage that you can handle larger files. It will likely be slower and is dependent on the user keeping the page open during the processing. Keep in mind you still need be able to upload the CSV file to store it, as per your requirements, so...

Server Side

The following should get you started on a number of things you need to address here.

  • Uploading. You can indeed use many of the file upload examples available such as the one jkraybill references here. To avoid view state governor issues, make sure you pay attention clearing the Body of the document or as I prefer simply use the 'transient' modifier on the member variable in your controller.
  • Parsing. You can also find here a good CSV parser library written in Apex by Marty Y. Chang. In terms of actually parsing a CSV of any reasonable number of columns and rows you will likely hit the Apex statement governor before anything else (upload or heap size limits).
  • Scalability. To get the most out of the platform you might want to consider parsing your CSV file using Batch Apex. There is a good example of how to combine Marty's library with Batch Apex to accomplish this here from Agustina García. With this approach you can see, depending on your data, around 5000 rows of 100 columns being process for an approximate 2MB file. If you implement this you might also be interested in this recent question and answer on monitoring Batch Apex jobs from your UI.

Summary and Salesforce Data Loaders

Parsing CSV files can be very expensive on Apex statements, which will limit the size and complexity of the files you can consume. So you should consider if you really need to build this yourself vs either of the Salesforce Data Loaders (there is a desktop and web based version). Neither of these however will retain the CSV file uploaded unfortunately, so if that is a strict requirement then rolling your own is the way to go.

Hope the above helps give you the pointers you need, enjoy!

2

Unless this file needs to be massive (several MB or higher), you can process 100% of it on the server side using a normal VisualForce file upload component. The user selects the file, submits it to the server, then you parse it in the controller and do everything you need to do, including error reporting. There are a lot of examples of VF file upload around the web, here's one of them.

  • We might not use DL because of the below reasons: [1] Users have only Platform License, to use Data Loader Users need Salesforce License. [2] The CSV file contains data pertaining to several custom Objects (~5). [3] The size of the CSV will not exceed 500 rows (for all the Objects). Given the above considerations and the approaches you guys have putforth I would stick to custom controllers. Thank you all for the insights, was indeed very useful.... – sHiBuKaLiDhAsAn Mar 19 '13 at 9:07
  • I did not mention data loader, maybe this comment was meant for someone else? – jkraybill Mar 20 '13 at 3:37
  • Oh Yes, I meant to say that we are going to follow the Custom Controller Approach that you suggested. I wanted to know if the below is feasible. The CSV file contains data pertaining to several custom Objects (~5). The size of the CSV will not exceed 500 rows (includes rows for all the custom Objects) of varying Columns (say 50 Columns Max for each Object, no TextArea type). – sHiBuKaLiDhAsAn Mar 20 '13 at 7:23

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.