Importing Tasks errors with time values in Description field

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.

Hi,

I am facing some type of similar issue. I have already tried to clean the data (we work with google spreadsheets). So I wanted to make sure that there are no strange comma's or other punctuation marks in the sheet. 

However the first 20 records are uploaded without any problems, but the next 21 to 50 records give this exact same error as the example above. The data comes from the exact same excel sheet, so I just thought it is a bit odd. I already checked our date and time format In Sugar. This is the exact same format as in our Sugar environment. We don't work with AM or PM in our Sugar, but I do get this same error message. 

We are migrating data from one Sugar environment to another. But to be sure, we are first migrating the data to a Sugar sandbox. Below I shared a screenshot of the matter. 

Thank you in advance for your answer.

Best regards,

Maike 

Hi Maike,

Importing dates into Sugar can be very troublesome, especially if you are using Excel. Excel loves to format dates even if you never touched the data.

We have a support article about this: https://support.sugarcrm.com/Knowledge_Base/Import_Export/Troubleshooting_DateTime_Format_Issues_During_Import/

Sounds like you've done the first three steps, but step 4 shows how to force Excel to use the date format Sugar is looking for. I'd start there and see if that helps.

If it doesn't help, it might be worth trying to use another spreadsheet program, such as Google Sheets, as it is a lot less aggressive at modifying dates.

Hope this helps!

-Raye