⨳ shifty metadata ⨳
July 18, 2017
I’ve got a set of Excel files (.xlsx) containing weekly spend breakouts. These files are supposed to be formatted identically, but they’re not. Across these files, a couple numeric variables are sometimes read as text. That makes stacking data impossible.
I could probably develop some overly-complicated bit of code to extract the schema from the dictionary tables and then adjust the import code on the fly. But that’s work.
Here’s what I did instead:
a_number=input(compress(vvalue(any_variable), ' $,'), 16.8);
Let’s unpack that:
vvaluetakes a variable (numeric or character, doesn’t matter) and outputs the formatted value as character,
compressthe resulting string to remove spaces, commas, and $s, and
inputto convert character to numeric.