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

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
188
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    7.3 KB · Views: 3

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
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) ?
 

Forum statistics

Threads
1,141,863
Messages
5,709,053
Members
421,611
Latest member
Lisa W

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top