+ Start a Discussion
sfdcdev.wordpress.comsfdcdev.wordpress.com 

Batch Insert of Records in CSV file

Hello,

 

I am planning to do a insert of a large number of records that are inside of a CSV file. In the past we have provided the client a solution of using a Java program that reads the CSV file and makes the Insert by making use of Salesforce API.

However, now that the batch mode is available I was wondering if we can make use of it in this perticular scenario. Is there a way that we can can parse a large CSV file and insert records into Salesforce using salesforce batch mode.

Right now it appears that the batch mode can only run on records that are already inserted into Salesforce. I want the other way round, I want batch mode to be operational on records that are yet to be inserted into Salesforce. 

It some one already did this, please post the code and I guess it woud be helpfull for lot of people who are running in this issue. 

aalbertaalbert

Have you used the Data Loader? Its a client-side tool that can import data into salesforce.com from a CSV file through the API.

 

 

sfdcdev.wordpress.comsfdcdev.wordpress.com

Hello aalbert,

 

Yes i have used data loader in the past ,and i am aware that we an do the APEX API calls using Flex, java Script sitting right inside salesforce app.  

But I thought Using apex is much cleaner solution and i think it would definitely be faster as everything is happening inside the server unlike - Dataloader or any application running in the Browser. Another main reason for not using dataloader is our client is a financial client and they are not keen on using any open source software due to their security concerns.

Please let me know if any one has an apex solution for my issue. 

Message Edited by Girish989 on 06-09-2009 07:31 AM
cvjcvj

For loading data from a local CSV you will need to use the web services API.  Batch Apex is for Batch processing records already stored in salesforce.  If your client is not comfortable using the Data Loader, we have a number of enterprise ETL partners with pre-built integrations to salesforce.

 

http://sites.force.com/appexchange/results?filter=a0L30000001Qp7TEAS&sort=6 

TgruTgru

As CVJ says, you could look at ETL tools that will be able to provide you with the technology you need. You could look at Talend, an open source solution: there are components that make it possible to work with salesforce.

 

----

 

Talend Open Studio is an open source ETL tool for data integration experts with a user-friendly GUI, easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component which allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/
 

ehrenfossehrenfoss
I had the same need and ended up writing a function to do it. This does not handle newlines. You might want to check the code sharing page to see if anyone has updated it. http://wiki.developerforce.com/index.php?title=Code_Samples



public static List> parseCSV(String contents,Boolean skipHeaders) {
List> allFields = new List>();

// 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 lines = new List();
try {
lines = contents.split('\n');
} 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 fields = line.split(',');
List cleanFields = new List();
String compositeField;
Boolean makeCompositeField = false;
for(String field : fields) {
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;
}
jev007jev007

ehrenfoss,

 

This is great.  What about testing this function?  Can you post what the code coverage looked like for this?

 

Thanks!

Add DryAdd Dry

I created my CSV via MS Excel and when I saved it it uses \r\n not \n as the new line char(s).

 

So the last value in the columns always had a \r char at the end which causes problems when trying to test the string value.

 

I change the code to this:

 

 try {
  contents = contents.replace('\r\n', '\n');
  lines = contents.split('\n');
 } catch (System.ListException e) {
 System.debug('Limits exceeded?' + e.getMessage());
 }