Importing Tasks errors with time values in Description field

Sorry but these do not fix the issue.

Thanks for the responses

Hi Lyle  Hookom,

Would you be willing to share a screenshot of the error or copy and paste the exact error information you are receiving into this thread?

I have attached two files to my original request. One is a screenshot of the Invalid Date error and the other is the actual import file for one record that caused the error. As I note in the screenshot, if 10:30 AM is in the description field of the first record it will fail, however if I make this record the 2nd or later record in the file it will load fine, as long as the first record does not contain the 10:30 AM or PM value.

Lyle

Hi Lyle  Hookom,

The screenshot shows the error pointing to the Start Date format as the source of the error, not the Description.

To confirm, I imported your file twice into my test 8.2.0 instance set to Create New Records Only.

In the first attempt, I did not map any of the date fields, but I left the mapping to Description in place and unedited. This attempt created the record without error.

In the second attempt, I did not map Description, but mapped the date fields, and I received this exact error.

Sugar needs the date and dateTIme formats to reflect the exact format of the database format for these fields in the imported file: yyyy-mm-dd hh:mm:ss

Your date format (yyyy-mm-dd hh:mm) is documented as not working in product defect 65461.

You mentioned the import working after editing the row's description field. Can you provide an edited file that works for import in which the Start Date is still in this invalid format in order to help confirm it is actually the Description change impacting this symptom?

HI Patrick,

If you take my previous file, open it in a text editor, and  copy the first record and paste it back in is as the 2nd record, then change the “AM” value to “TM” in the description of the first record, both records will import successfully.

I am attaching a file that I did exactly that to it.

Lyle

Hi Lyle  Hookom,

The issue appears to be how the import file impacts the Import File Properties shown here:

It appears from my testing that the first occurrence of a recognized time format regardless of the field type it appears in, changes this Time Format.

When Description contains "11:12 AM", this field is set to that format, therefore causing error for the actual dateTime fields in the file not formatted that way.

I have filed defect 81962 to report this to SugarCRM developers.

As a workaround, instead of needing to edit your import files, you can manually change this format in the import wizard to the format of your actual dateTime fields.

I hope this helps!

I assumed that is what was happening, but to me that is bad programming as it should just use the time format that is setup and should be checking if it is a date field.

I cant just change the import date format because my actual date fields are in a 24 hour format from Salesforce, this is one of the values I am importing “4/10/2015 21:11”

My plan is to just fix the first records before importing.

Thanks again,

Lyle

Hi Lyle  Hookom,

Changing the import date format is a matter of toggling the value in the dropdown shown in the screenshot and requires no modifications to the imported file. Can you clarify why that is a less desirable workaround than fixing the first record in the file before importing?

Hi Lyle  Hookom,

Changing the import date format is a matter of toggling the value in the dropdown shown in the screenshot and requires no modifications to the imported file. Can you clarify why that is a less desirable workaround than fixing the first record in the file before importing?

I will try changing the import date format. The reason I was not doing this is that my actual import dates are in 24 hour format and I figured that making the change would mess them up, I have had lots of issues trying to get my dates in the correct format.

Will let you know the results tomorrow.

Thanks,

Lyle

Hi Lyle  Hookom,

I look forward to hearing how it goes.

When you import the file with the first Description changed to TM, this property setting is set by default to reflect your Start Date format, which as you stated accurately reflects your date field format. When the Description of your first record has a date format in it, this value is dynamically changing in the importer to erroneously reflect that first format it sees. This is the nature of Defect 81962.

Since the issue is how the default value of this dropdown property setting is the abstracted behavior causing the issue, manually checking/setting this value during import to ensure they reflect the dateTime format in your dateTime fields is the same result as modifying the Description of that first record.

I will be interested if the issue you described about record 710 changing the property reoccurs in your testing as well. If it does, that will be a layer of symptom I need to test and add to the defect.

Hi Patrick,

I changed the my date format to be something like 10:32 PM instead of the 24 hour format.

The result were the same, it errored if the first record had an “AM or PM” in it.

Lyle

Hi Lyle  Hookom,

If the format of the content in your actual date fields is in the 24 hour format, as you have previously reported, then that is the format you need to set at this stage of the import.

The reason the importer is throwing the error is because the content of the date fields does not match the format designated in this setting in the early step of the import wizard.

The nature of Defect 81962 is that if the Description has a date in its content, it will dynamically set this import format to a different format than the date fields that appear later in the import.

The recommended action is, at this step of the import wizard process, to set the import date and time formats to the consist format of the content of the dateTime fields in your import file, not trusting the dynamic default setting that can be influenced by the content of non-date type fields that appear left of the first date type field in the first record of the import file.

Hi Patrick,

My solution for the AM /PM problem is to make sure I do not have that value in my first record.

I have a couple of other issues that do not make sense to me, I will mention one of the issues here.

I have attached two import files, one imports fine, creating the record, and the other errors out.

•   The CaseBad.csv file errors out with an invalid Date / Timestamp, see screenshot. However if I remove a bunch of data from the description field it will import fine.

•   The CaseGood.csv file imports fine. It has 2 records in it and the second record in this file is a copy of the bad record from the bad import file.

I am really getting desperate to figure out why my import is failing as I can’t identify a logical cause to the problem. I can import 2000 records fine and then on the next import of 1000 records I run into this problem where the record would have loaded fine if it was not the first record.

I am importing 60,000 plus case records into a test system and will be repeating this in our production environment in a couple of weeks and this problem is making it impossible.

So far I have imported 30,000  accounts, 7000 leads, and  55,000 Contacts successfully. I have also imported 2,000 cases successfully.

I really appreciate the support you have given so far and I hope I am not being a burden.

Thanks,

Lyle

Hi Lyle  Hookom,

I have sent you a private message with my email address and requesting from you an email address or phone number where we can discuss this offline using a different communication method. Let me know!

I am happy to help however I can.