In the company where I work, a persistent source of headaches is the fact that Excel interprets some text values in CSV files as numeric or as date. It takes more vigilance and foresight than our users have to prevent this from happening, at least in the ways they know.
Under some circumstances (e.g. if one clicks on Data > Text to Columns...), Excel displays wizard-like dialog sequences to ask the user how different columns should be interpreted. This becomes increasingly time-consuming as the number of columns that need to be dealt with increases. In addition, having to go through this interaction anew for each CSV opens the door for inconsistencies to creep in from one CSV file to the next.
I am looking for ways to encapsulate the data type specification for the columns of a recurring, standard CSV-formatted report. The goal is to be able to avoid the interactive wizard for specifying the properties of columns, and instead have Excel read them from a the data type spec. Is there a way to do this?
Power Query
) to import the CSV file, rather than just double-clicking on it, you could pre-define the data types for each column.