United Kingdom import date / time from excel into a power apps entity

[mholden]

I am trying to import data from a CSV file into an entity using a data flow.  The problem is that I am getting multiple errors as the date / time format in my CSV file is in UK format, and power apps seems to want it in US format.  

Hence many of my records are not being imported due to errors, and those that are imported have the wrong date and time.  

Example: One record in my CSV file has the date 11/03/2020 13:50 - when it is imported to the entity it has the date 03/11/2020 13:50:11.

Out of 580 records, 108 were imported, 472 were note imported and the flow error given was:

Reason: Bad Request, Error code: 0x80040239, Message: DateTime is less than minumum value supported by CrmDateTime. Actual value: 01/01/0001 00:00:00, Minimum value supported: 01/01/1753 00:00:00

Any ideas?

[WarrenBelz]

Hi @mholden ,

Strangely, I had almost the same issue (I am in Oz), but with bulk-updating an Excel reference file linked in OneDrive with a copy/paste from a local file.

In the end I took the "if ya can't beat'm join'm" view and formatted the Excel date as mm/dd/yyyy and all worked well.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @mholden 

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[mholden]

The problem with that solution is that the app I am developing will require a regular (once per month) input of the same format of data - and other data with a standard excell UK date format. Sure, I can adjust the excel sheet, but it means time taken to rework quite a lot of different Excel sheets each month to get the data into the database, where I was anticipating that I could set up power query to do that work for me once I had saved the query. With the other little niggles I have been experiencing, I'm beginning to think that my original thought of holding all of the data in an excel spreadsheet on a sharepoint site might be better option. 

[WarrenBelz]

Thanks @mholden ,

I could not agree more. Excel as a data source has a lot of other limitations and you could soon outgrow it. I only use Excel for a couple of static reference tables.

One tip for you - avoid creating the SharePoint list with the New App > Import Spreadsheet option. It does not create the right type of Custom List to enable a PowerApps edit/view/new integrated form. You are better creating a custom list, producing a datasheet view with the fields in the same order as the spreadsheet and then pasting the spreadsheet in.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Max44]

Prepare the file template.

a. Export the entity data to the CVS file. Follow the steps in Export data to CSV.
b. Define a plan to make sure data is unique. Use either primary keys or Alternate Keys.
c. Refer to the next section for instructions to make sure data is unique before you import it into an entity.

Modify the file with your data.

Copy data from your Excel or CSV file into the template that you just created.
Import the file.
a. On powerapps.com, expand the Data section. Select Entities in the left navigation pane.
b. Select the entity that you want to import data into.
c. Select the ellipsis or menu at the top. Select Get Data. Select Get data from Excel.

d. On the Import Data screen, choose whether to import data from an Excel or a CSV file.
e. Select Upload.
f. Choose your file. Follow the prompts to upload your file.

Admin

clouddesktoponline

[mholden]

I have done that Max, but the problem I get is that my dates in Excell and in Power Query are in UK format and when I import then into the CDS they seem to be taken in USA format (when they import without errors) which means that the dates are wrong.  I can't seemto sort this through Power Query, another respondant (Maxx) has suggested sorting the problem in excell which I can do but it means an extra step on the data import.

[WarrenBelz]

Hi @mholden ,

I have been through this one as per my previous post.

Despite spending many hours looking for another solution, we simply spent the 10 seconds changing the Excel to USA format.

[mholden · Accepted solution]

OK after a LOT of digging I have solved this issue.  The issue lies with the Environment setting in Microsoft Dynamics - as a default it appears to be set to US settings. You need to go into the Dynamics Admin, choose system settings then the format tab. Change from US to UK format: