Feature image for article on how data from CSVs imports into Alteryx.

When data is first imported into Alteryx from a CSV, all fields and their data are imported with the data type “V_String”. This includes fields of data in your dataset that may actually contain only numeric values. Having numeric values imported as strings causes problems further down the road in your workflow when you want to perform any kind of mathematical operation, such as with the Summarize Tool.

Complications Related to Import as V_String

Because the fields in your dataset have the datatype “V_String” after importing from a CSV file, the Summarize Tool will not allow you to use the “Sum” function on the field, and other functions like “Max” and “Min”, which are typically used on numeric fields, will not yield expected results:

Screenshot of Alteryx illustrating how data imported from a CSV, including numeric fields, are imported as "V_String" data type fields and therefore result in the "Sum" aggregate function of the Summarize Tool being disabled.

Screenshot of Alteryx illustrating how data imported from a CSV, including numeric fields, are imported as “V_String” data type fields and therefore result in the “Sum” aggregate function of the Summarize Tool being disabled.

Leading/Trailing Spaces

Depending on the style and formatting of the numeric fields, the values on import may also be imported with leading and/or trailing spaces. If you intend to use the data imported as a numeric field, this presents a further complication:

Screenshot of Alteryx illustrating how numeric data imported from CSVs often will be imported with leading and/or trailing spaces.

To check whether numeric values in your dataset are being imported with leading and/or trailing spaces, click on the outgoing connector of the Input Data tool after running a workflow.

Converting from V_String to Numeric Data Type

In order to get the data imported back to a proper numeric data type in Alteryx after importing from a CSV, we must use the Select Tool to reset the data type of the numeric fields to a numeric data type. Before we convert the fields to a numeric data type, however, we must “cleanse” the values while still in their V_String format of any special formatting or problems in the data that will prevent Alteryx from properly converting it. Such special formatting or problems could include the following:

  • Commas delineating units of ‘000s in a number (e.g. “1,000.00” instead of “1000.00”)
  • Parenthetical Negatives (e.g. “(1000.00)” instead of “-1000.00”)
  • Trailing Negatives (e.g. “1000.00-” instead of “-1000.00”)
  • Leading or Trailing Spaces

Only after each of the above “impurities” are removed can all the field’s values be properly and accurately converted to a numeric data type by Alteryx. Fortunately, each of these impurities can be consistently dealt with using various tools in Alteryx.

Blake is a CPA and a law school graduate specializing in taxology, tax and finance process automation and optimization, and cloud accounting systems.

Leave a Reply