importing a csv file periodically gets treated as a time value or a date/time value

[JeffGrant]

Hi All,
for some reason, when excel imports a csv file, it periodically treats some data as either a time value or as a Date/time value. I have checked the source csv data an it looks fine. Nothing stands out.

In this particular area of the import data, the data is in this format 1: 0-0-0 and should be read is as such. But for some reason it treats some data as a date/time value and I end up with 01-01-2021 9:00:00 AM or as a time value and I get 44259.375

For example: this is an extract from the csv file
Matthew Dale,59,0,0x32454422x3,13:0-3-3,3: 0-2-0,3: 0-1-2,0: 0-0-0,0:0-0-0

The attached image shows how the 13:0-3-3 is being imported incorrectly.

Unfortunately it happens too much to be ignore.

I have looked at PQ as a means of work around and it will do the job for sure, but seeing that this import is that start of my model, the entire AutoOpen process has been abandoned to doing it manually.

Since the data files come from a web based API, perhaps the export feature is adding some extra hidden characters ???????

Maybe I could do something in PQ outside of my model to get the data corrected.

All suggestions graciously received

Jeff

 

[Alex Blakenburg]

On the standard setting, the very 1st step after Source is Changed Type.
What happens if you either remove that step altogether and reapply when you have cleaned up the data OR click on that step and change that column's data type to ABC (it should then offer to replace the existing step in which accept that) ?