0

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?

Improve this question
1
  • 1
    If you could get your users to run a query (eg Power Query) to import the CSV file, rather than just double-clicking on it, you could pre-define the data types for each column.
    – Ron Rosenfeld
    Commented Sep 21, 2020 at 1:45

1 Answer 1

Reset to default
3

I know of no way to specify the datatype of columns in a CSV file.

Alternative 1:
Make sure to have a leading ' for the fields (columns) which you find troublesome (this makes them be TEXT unconditionally, in Excel).
Then with the data imported; use a VBA macro to set them according to your wishes. (Not verified!)

Alternative 2:
Define the data using EXCEL-syntax, e.g. have =TEXT("2020-09-20","YYYY-MMM-DD") in the CSV-file, instead of a plain 2020-09-20 (untested!)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .