Formatting Values in CSV

[DanHoward]

Hi all,

I've got a range of CSVs that are ~200 columns, with multiple discrepencies in the formatting in them (i.e., some have £ symbols...others use GBP, some use k, others use exact figures, others use ',000').

Was hoping someone could point me in the direction of a tool/process that'll amend these en masse. I've tried using RegEX and Find Replace, but these both seem to be aimed for specific columns, whereas I'm looking across a much wider dataset, and don't fancy copy & paste that many times.

Thanks for any help,

Dan

[MarqueeCrew]

@DanHoward,

You'll want to either use a Multi-Field formula or you can use a Transpose tool so that you'll get the data into NAME + VALUE pairs and act on the data in a single formula.  This transposition of data would be followed by a cleanup and then a CROSS TAB to get the data from rows to columns.

Not seeing the data and not being able to look for patterns makes this rather difficult to "solve".  There was a recent blog post that referenced a number cleansing macro that a few ACEs helped with:  https://gallery.alteryx.com/#!app/Number-Cleansing-Macro/5b6df1870462d71090803ac9.  This might help.

Cheers,

Mark

[danilang · Accepted solution]

Hi Dan(you can never have too many)

Check out the Multi-field Formula tool.  It allows you to a apply a formula to multiple fields at the same time.  Select the type of fields, string, numeric, etc and then the individual ones.  The expression then applies to all the selected fields

Dan