Excel PowerQuery - Best way to import SQL Server Logs with correct DateTime conversion?

1
votes

In SQL Server I often check error logs directly in SSMS, but when I export them as CSV files, the DateTime component is converted from Australian to US format.

This is what it shows - in AUS format:

enter image description here

But this is how it's imported into Excel - US format:

enter image description here

I've tried to import the LOG file using PowerQuery, but it doesn't detect the column is a DateTime column, and when I try and convert it, I get Error.

enter image description here

enter image description here

I thought PowerQuery was "smart" to know the data types of columns. If I have to Add Columns by stripping the text into its date components, I might as well have just done it in the vanilla File Open CSV wizard.

Thanks

1
exceldatetimepowerquery

1 Answers

2
votes

In Power query, right click on the column and select

  • Change Type
  • Using Locale

enter image description here

  • Pick Date/Time and English(Australia)

enter image description here

It will then be converted into a "real" date/time. Of course, it will be displayed in PQ in accordance with your Windows Regional Settings short-date format.

Once you load it into Excel, you can change the cell numberformat to display it in the date/time format of your choice.