Same file behaves differently across machines

**AzzKickr** · 08-30-2019, 08:45 AM

So I have a CSV file that is downloaded from a particular application. When I open it up, there's columns where Excel automatically picks up or recognizes a date and puts it in the date format. However, when about half my staff open it up, they stay as text. This is causing my formulas to not work.

Some have Win7 + Excel 2010, others have Win10 + Excel 2016 (myself included). There's no line that can be drawn at first sight. Behaviour A and behaviour B happens on both.

If there's anyone interested or intrigued, I'll attach the file in question.

Thanks in advance

**Pete_UK** · 08-30-2019, 08:59 AM

You can open a .csv file directly into Excel by double-clicking it. In doing so, Excel tries to make sense of the stream of text coming in (effectively like typing very quickly on the keyboard). Sometimes it makes the wrong decision about whether it is text, or numbers in each field.

To avoid this, you should use the Text Import wizard, i.e. use Data | From Text then navigate to the file and double-click it, then the Text Import Wizard will open automatically. On this you can tell Excel exactly how it should react to the file, if it is delimited or not on panel 1, on panel 2 you can specify the delimiter, and on the third panel you can tell it to treat each column in a certain way, either Text, numbers, dates (and in what order), and so on. This does away with the uncertainty that your staff are experiencing, although it does take a bit longer to open the file. Once opened and set up, you should save the file as a genuine .xlsx file, so other people should not face the same problems.

Hope this helps.

Pete

**AzzKickr** · 08-30-2019, 09:10 AM

I'll look into it. However, it gets complicated then because I'm opening it and extracting data via VBA code.
I can also add that the behavior is completely reproducible. So on one machine it is always behavior A, on another it is B.

**Pete_UK** · 08-30-2019, 09:16 AM

Your macro could also include a snippet of code to import the data from the .csv file so that it is always in the correct format, and then save it as an .xlsx file.

Hope this helps.

Pete

**AzzKickr** · 08-30-2019, 09:26 AM

True. I was thinking about going that route too but am just really curious to know why it would do this

**MrShorty** · 08-30-2019, 10:19 AM

You mention that the inconsistency appears across computers. I'm pretty sure that, when you leave date/time detection to Excel's defaults, it will use each computer's regional settings (part of the OS) when deciding how to interpret dates. My guess is that your regional settings are such that Excel can correctly interpret the dates, but those computers where it doesn't work, are using a different setting that prevents Excel from recognizing the dates.

This isn't the kind of programming I do, so I am not sure how best to make this consistent across machines. One way or another, I think you need to import the text data in a way that allows you to control how Excel interprets date text. From my limited experience, I like the Import Data -> From text command, because I can set up the data import once. As long as I remember to check the "save query definition" option, I never need to redo this part. I can just refresh the data to get new data in. The refresh step can include a "prompt for file on refresh" option if I want to use a different file each time and various other options. That's what I know.

I see some indication that Excel's programmers are moving away from these import commands in favor of Power Query/Get and Transform. I have no experience with Power Query, but I would not be at all surprised to learn that it has tools/commands/statements that allow you to control the interpretation of date text.

**AzzKickr** · 08-30-2019, 10:21 AM

You are spot on. I came to the same conclusion just 10 minutes ago. The machines on which it fails, are all incorrectly set to US format. The ones that do work (like mine) are correctly set to European.