SOLVED

Imported dates not converting to correct format

Copper Contributor

I'm importing data from SAP to excel in csv file. Data has dates in MM/DD/YYYY format. 

When I take the data to excel and use 'Text to Columns' function, some of the dates are automatically converted to dd.mm.yyyy format and some remain in original MM/DD/YYYY format. Excel does this autoconversion because my system language is Finnish, and accordingly the default date format is dd.mm.yyyy. 

- These which remain as MM/DD/YYYY are not recognized as dates by excel and no conversion is done to them 

- all dates look similar in original csv file 

 

As a result I have column where some fields are dates and some plain text.

Basically date related analysis is not possible with this. 

 

Has anyone come across this type of a problem before and any workaround what I could try out? 

8 Replies
best response confirmed by LKO2022 (Copper Contributor)
Solution

@LKO2022 When you do Text-to-columns, make sure that you choose the correct setting for the date column is step 3. Select Date and MDY as the source contains dates in the Month, Day, Year format. Now, Excel will import the dates correctly.

 

Alternatively, use Power Query (a.k.a. Get & Transform Data). Much more powerful than text-to-columns.

Works beautifully, thank you!!!
Thankyouthankyouthankyou! This little tip just saved me all kinds of hassle! Seemed like there should have been more letters, but it works.

@Riny_van_Eekelen I am trying on query but I don't know how to fix. I am just newbie. Can help me with my case below?

 

Screenshot.PNG

@jmustany That's difficult based on a picture alone. I suspect that the dates are in fact texts and that you need to transform to real dates using a 'Locale'.

Riny_van_Eekelen_0-1677851651469.png

 

Hi @Riny_van_Eekelen, thanks so much.

 

I'd tried to using "Locale" and change to my respective country and also English US, the error remains the same.

 

Screenshot.PNG

Hi @Riny_van_Eekelen,

 

I've got answered to my problem. I spot my error when do delimiter option. I shouldn't thick any of delimiter even though "." is the delimiter here. That's solved my problem. Thanks again!

@jmustany You shouldn't change to your local setting but to a local setting that matches the format the dates are in. That tells PQ to take the data format from that other locale/country and transform it to your own.

1 best response

Accepted Solutions
best response confirmed by LKO2022 (Copper Contributor)
Solution

@LKO2022 When you do Text-to-columns, make sure that you choose the correct setting for the date column is step 3. Select Date and MDY as the source contains dates in the Month, Day, Year format. Now, Excel will import the dates correctly.

 

Alternatively, use Power Query (a.k.a. Get & Transform Data). Much more powerful than text-to-columns.

View solution in original post