+ Start a Discussion
michael_mcmahonmichael_mcmahon 

Excel invalid date format on insert to custom object

I've tried Workbench, and at least two other methods to upload 324 rows of about 8 columns to sf.  There is a problem with the Excel date format which I cannot find a way to fix. I've been working on this for over ten hours, please, please, someone help me!  This is a primary feature for us and i have to figure this out.

 

I read about switching the Excel date format to ISO, but ISO no longer seems to be an option in Excel 2010.

 

I started installing Excel connector, but at this point I'm pretty sure I have to fix the data format in the csv file rather than spend even more time trying to install and learn yet another system.  But if people have experienced my date format issue and Excel Connector will fix it then i'll do it.  I just need some direction as I'm spinning my wheels now.

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
michael_mcmahonmichael_mcmahon

Excel Connector Works!  The setup and learning process was straightforward and it finally worked.  Thank you Mr. Excel Connector Creator.

All Answers

sfdcfoxsfdcfox

You can format the cells (Ctrl-1) using YYYY-MM-DD (for date fields) or YYYY-MM-DDTHH:mm:ss.000Z (for date/time fields), and just use the data loader. Note that if you're in a GMT+ time zone (on or east of London), you will need to use European Dates in the data loader settings or you may end up using the wrong dates by one day. The data loader is in Setup > Data Management; it's a desktop application that you can use to import CSV files. Make sure you use CSV and not Tab-delimited, and you should select the MS-DOS (ISO-8859-1) or Windows (UTF-8) format.

michael_mcmahonmichael_mcmahon

Thank you very much for your reply, but it did not work.  Interestingly, English - US as the date format does not offer a YYYY/MM/DD option, it's just not there!  So I found that format using Morocco as the country.  But it still didn't work.  Then I tried changing from Western - ISO to UTF 8 and neither worked.

 

It seems like Excel may be inserting single quotes around the date as I think I remember reading something like that, and also in the Workbench Data Loader the error shows the date in single quotes, but that may be the formatting of the error message, too.  '2010/10/01' is not a valid value for the type xsd:dateTime

 

I did try many times with the Data Management Custom Object Insert as well as the Workbench loader.  I've tried changing the custom field from Date to Date/Time (which is what I really need) and still did not work.

 

I REALLY need to get this to work.  Any other suggestions?  Would outputting the file in xsd be worth doing?  I need some direction because I'm just chasing my tail now.  

 

Thanks!

michael_mcmahonmichael_mcmahon

Excel Connector Works!  The setup and learning process was straightforward and it finally worked.  Thank you Mr. Excel Connector Creator.

This was selected as the best answer
sfdcfoxsfdcfox

That would be Ron Hess, who eventually ended up on the salesforce.com payroll. I have a lot of respect for the work he's done, too.