Forum Discussion

Mike Varley's avatar
Mike Varley
Copper Contributor
Sep 24, 2020
Solved

Importing dates from tab delimited file

Hi   I have a TAB delimited file that contains date\time info in the following format e.g:   mm/dd/yyyy hh:mm PM   9/17/2020 5:33 PM   I've tried various methods of importing using the legacy...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 24, 2020

    Mike Varley 

    Legacy wizard doesn't convert datetime, but Power Query does. Select the column and from right click menu Change type using locale, here

    Result is datetime

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 24, 2020

Mike Varley 

Legacy wizard doesn't convert datetime, but Power Query does. Select the column and from right click menu Change type using locale, here

Result is datetime

Mike Varley's avatar
Mike Varley
Copper Contributor
Sep 24, 2020

SergeiBaklan 

 

Hi Sergei

Thanks for replying. Some progress! Seems to work for some cells but not others even though they look the same...

Input:

 

 

 

 

 

 

 

 

Settings:

 

 

 

 

 

 

 

 

 

 

 

Result:

 

 

 

 

 

 

 

 

 

Error:

 

 

 

I can't see any difference in cell contents. Any idea what causes this?

 

Thanks

Mike

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 24, 2020

    Mike Varley 

    You shall use locale of origin (in your case US), not one into which you'd like to transform (I guess UK).

    • Mike Varley's avatar
      Mike Varley
      Copper Contributor
      Sep 25, 2020

      SergeiBaklan 

       

      Hi Sergei

       

      Yes, working now.

       

      Many Thanks

      Mike

Resources