function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Akira007Akira007 

Import Attached Excel file in Email to Salesforce custom object Automatically.

Hi, every one...
I'm thinking about making automatic proccess to import Excel file attached in Email to Salesoforce:

1. Invoke some APEX class when email comes to specific mail address.
2. Upload the attached Excel file in the email to document folder in Salesforce.
3. Invoke APEX code to read the data in Excel file and import the data to Salesforce Custom Object.
4. Notify the salesmanager that new custom list imported to Salesforce.

I appreciate so much if some one help me to know how I can do it.

Regards,

Akira Fujiwara



JimRaeJimRae
An excel file could be difficult as it is a binary object, and there is no internal apex method to decode it. If you had an excel based csv file, that is a different story.
1) You could set up an inbound email service with a specific address to send the csv files to. (look at documentation on Inbound email)
2) you could right code to extract the attachment from the message for processing (you wouldn't need to save it as a document first, that would be an extract step). (look at documentation for binary email attachments and converting blob to string)
3) you would need to write a parser to extract the rows out of the CSV file, don't forget to account for things like names with commas in them, they can reek havoc on a parser. (this one will take some effort, depending on the complexity of the data, could be a simple string split, could require much more)
4) write code to insert or upsert the extracted records to your custom object. (create an array list of your custom object and new instances of your custom object for each record you parse get loaded to it, then insert or upsert the list in batch).
Akira007Akira007
Thank you for your responce!

I will try it.
But in the case we cannnot change Excel file to csv file, I might have to change the plan to use excel connector.
But the process will not be full automation.

Regards,

Akira Fujiwara
JimRaeJimRae
Good Luck. As I experienced, the biggest challenge is that there is no facility to "decode" the Excel binary file into usable data.  That is why I had to switch to CSV files instead.
If you figure out a way to do it, please post the solution back on this thread, I am sure there are many others interested in how to do it.
Rasmus MenckeRasmus Mencke
You can also consider saving the excel in an xml format, the newer versions of excel supports that - you can parse the XML document in Apex.

if you send in text files like csv they size of the document will have to be smaller, max size for a text attachment is 100KB. Where binary files can be a lot larger.
Akira007Akira007

Thank you for your comment.

I think it is very good approarch to use xml format.

I will try to negotiate to change the version of office software ( I believe they have to upgrade to office2007).

 

Regards

 

Akira Fujiwara

kool_akool_a

@Jim

 

I was wondering if you could provide code sample for parsing the csv file.

Example csv is below (Where the first line corresponds to field names)

 

"first name", "last name"

"john", "doe"

"jane", "doe"

 

Thanks

 

K

JimRaeJimRae

It isn't possible to post a sample set of code, this is a very sophisticated set of classes, and contain a lot of proprietary information.

 

Basically, this is the things you will need to consider:

 

1) getting the file into SFDC so it is visible to apex.  We used an inbound email service, and mailed our csv's to ourselves.

2) size limit of CSV, remember you have about 100k limit to the file.

3) converting the csv into records and cells, use string split on the \n\r to break out the lines as records, then use string split again to split based on the comma into an array.  also, watch out that you can handle the fact that a description or note field might have a comma in it, our account names do as well, this needs to be accounted for.

4) you will need to handle the delimiters if there are any, and remove them before loading

5)  form your good records into a an array of your object type to import and then insert them

 

There are lots of good hints and clues out on this site, and others that can get you started.

 

Good Luck!

kool_akool_a

Thanks Jim.

 

Sounds easier now. 

 

I will be creating a VF page which will allow the client to uplaod the csv file (about 1500 records). 

This will involve updating 3 custom objects (object1, object2 and a junction object)

This is not a simple dataloader solution, using the dataloader we found that the client would have to import, export, import again to get the desired solution...very tedious for the client (non-tech client at that)... 

 

This is going to be fun, I don't want to spend more than half a day hacking this out.

 

Thanks

 

 
kumokumo
I would keep in mind that XML parsing has its own limitations in SFDC as an example the file size in memory for APEX class is restricted to 1MB, Parsing CSV or XML that will use up more than 1MB will throw and Exception. I checked with support nad this limit was not possible to increase at this point as of Summer 09 release
ArunaAruna

i do have same problem but different

 

I need to import Excel/csv file into salesforce obect auto matically

 

can you please send soultion to this problem ,i will be thank full to you if send me  sample code of this.

 

Thank you
sunil_kumarsunil_kumar

Hi,

 

I also want to create a VF page which will allow to upload csv file containing records and then i want to insert those records in custom object automatically through apex coding.

 

So can you please help me in acheiving this by providing sample code for this purpose?

 

Thanks.
ArunaAruna

Hi Sunil

 

please gothrough this site i hope this link answers your question.

 

if you have any more question please post me

 

 

http://www.forcetree.com/2010/08/read-and-insert-records-from-csv-file.html

 

 

Thank you