Dates missing or parse fail (UK format) in Deals import from CSV/XLSX

[js_lon]

New account.  Importing 300 Deals via .XLSX (and also tried .CSV) (along with Notes, Contacts and Companies)

Everything is UK English date format, i.e.:

- spreadsheet is dd/mm/yyyy

- Hubspot is also UK dd/mm/yyyy both in Settings>Account Defaults and also Settings>Basic Info

- Windows 10 in UK date format too

When I import the Deals spreadsheet (Start an Import>File from Computer>One file>Multiple Objects), as soon as I get to the Field Mapping screen, the three sample dates are already showing as US format (mm-dd-yyyy).

If I on the import wizard Final Details page, I pick the UK format from the drop-down (which is how the spreadsheet is formatted), every date produces a parsing error.

If I leave the 'Final Details' drop down in the wrong US format, the data imports without errors.

BUT the Close Date is MISSING on every imported Deal.  (Other dates, like Dreate Date are fine)

I even tried creating the Excel format with US date formats but the issue prevails.

1.  Why does Hubspot's field mapping preview screen show the wrong date format (wrong for the source CSV and wrong for system and user settings)?

And more importantly...

2.  Why is every Close Date missing from the import and how can I fix it?  (Is the UK/US date format issue/bug a red herring/unrelated to the missing Close Dates?)

Any help appreciated. Thanks

UK dates already wrongly USHave to pick wrong US format to avoid errorUK date format Excel (or CSV)

[georgeluparu]

I am in the exact same scenario right now, and I finally managed an import without errors by using UK format in the import file and US format in Hubspot. I also tried importing the close date with US data format and I also tried to import with US format mapped to UK format in the last step of the import, but neither worked.

In any case, I think this is a major aspect that should be solved.

[jennysowyrda]

Hi @js_lon,

Are other users experiencing this issue? 

Can you please send along screenshots of what the error message within the error file is? I also wanted to share this resource which outlines how to set up your files for import.

Thank you,

Jenny

[js_lon]

LESS IMPORTANT

The US/UK date issue is a side issue - the workaround is simply to only use US date formats in the import spreadsheet (otherwise you get error like: COULD_NOT_PARSE_DATE 0-4 Can't process date '24/12/2019' using MDY format (from column 'hs_timestamp') )

---

MORE IMPORTANT

The MAIN issue is that the Close Date never imports.   All other Deals information imports correctly (including all other date fields)

There is no error.  It is just empty in HubSpot.  I have tried the field as both date e.g. MM/DD/YYYY and datetime MM/DD/YYYY hh:mm - it makes no difference.  See this example from with a XLSX file with just one Deal record as a test file.  The xlxs test file (just one Deal) as an example1 Close Date shows correctly in import wizard preview2 Pick US Dates format to match .xlsx3 No errors4 Close date MISSING despite being in the preview

[herodotuseducat]

i agree, close dates field (deal property) is auto-generated by Hubspot unless manually edited afterwards. same goes with Deal Created date field (deal property) which not only cannot be imported, is auto-generated after import. same goes with lifecycle stage where if the deal stage imported is one classified by the user as the final stage (ie: contract won), then the contact property: Lifecycle stage, changes to Customer, and cannot move backwards automatically if the Deal Stage changes from final to something less than that.

[herodotuseducat]

ignore my previous comment. -> of course you can update the deal close date. i was mistakenly trying to update the close date CONTACT property instead of the DEAL property

[jennysowyrda · Accepted solution]

Hi @js_lon,

It looks like you are mapping the close date to a contact property as opposed to a deal property. Are you able to see a deal property for "close date" when you click into the fields to map to?

Thank you,

Jenny

[JVOGELSANG]

"Close Date" import field gets automatically mapped to Contacts:Close Date (contacts can open and close?)
Searching for a field to nominate as the target field will not find Deals:Close Date ...

Too bad for the user who has to import many deals information and then go through and manually update them.

[JVOGELSANG]

Workaround:

After importing the deals - export them to get the Deal ID column and add a Close Deal column to the file and import again as a single object (Deals), with update. The import process will match Close Date to Deals:Close Date.

[js_lon]

You are right - thank you. 

I didn't realise that HubSpot would use the SAME field name: "Close Date" for TWO DIFFERENT fields: Deals:Close Date and Contacts:Close Date and the other option would be kept hidden until you click a back arrow in a drop-down overlay.   I hand't noticed the icon difference.

I wasn't even aware of the concept of 'closing a contact' - which Hubspot sets as default import rather than 'closing a deal'.  

Similarly Company Name import defaults to Contact:Company Name rather than Company:Name.  Still not sure why there are two.

Quite confusing - but thank you for identifying.  

I'm remain unsure why UK dates fail to parse (even when set to UK in import) - but changing pre-import the spreadsheet to US dates format fixes the issue - which is an acceptable workaround.