Weird Time Situation

Hello, I am importing schedule data that has been exported from a scheduling system. The data is pulled by individual location, meaning I pull data for location A, then B, then C, etc. That data is automatically downloaded to my computer once completed in an .xls format. however, when I add them to the converter as the initial file, some have start times and some do not. Please see images below. these images are taken internally to the converter and the files have been treated identically from download, to pointing to them as the source of the beginning of the transformation.

Screen Shot 2022-05-09 at 9.36.33 PM
Screen Shot 2022-05-09 at 9.36.12 PM

somehow its stripping off the time on just some of the files, but not all. 2 of 7 to be precise. makes absolutely no sense to me what is happening.

hard to say wtihout seeing the files. Can you email us the .transform and a set of input files?

I can. I thought about that, but sometimes the simple act of opening the file can cause formatting to occur so I didnt know what to do there. the data is technically protected so I have to manipulate it before I send it. I wont be touching the fields in question tho when I do.

It is difficult to know what the issue is, if we can’t replicate it here.

I imported datetimes from .xls and .xlsx and Easy Data Transform does what I would expect:

At what point is the difference in the 4 files happening, when they are:

  1. read-in
  2. stacked
  3. datetime format changed

?

Are you sure the problem isn’t with the input files?

it could very well be the input files. it would not make sense because our data pull process is identical for all locations, but that doesnt mean the core program doesnt have some weird backend stuff happening. its just odd that 5 of 7 are 1 way and the other 2 the other way. thats why this one has me a bit baffled. ill figured out a way to share a file with you. do I just email the support line?

Yes, just send it to the email address here:

I believe it is the input data that is the problem (I won’t show any details, as data is confidential).

I found out what the issue is.

Easy Data Transform reads down each Excel column looking for date times and deciding whether to put them in YMDHMSM, YMDHMS, YMDHM or YMD format (it would be annoying to put them in YMDHMSM if there is no time component in any of them ). If it doesn’t find anything in the first 50 rows it assumes YMD . Some of your Excel files have no datetime values in the first 50 rows. Some do. Hence the apparent randomness.

I have tweaked it so it looks down the first 1000 rows (rather than 50) and if it can’t find any date values it assumes YMDHMSM (rather than YMD).