Have an automation that catches a csv file with employee data on it, parse, load, etc. typical
There are a two fields that will both only be available every so often and the rest of the time they will not exist on the file. I was going to split the automation with a condition looking at if a field exists but that isnt an option on conditions. Any thoughts on how to handle this?
I would try “length equals 0” as a test to know if the value is present or not.
the catch with that is even when the column exists there are times where the individual cell will be blank so that would give a false negative
I don’t understand how that is. A CSV has fixed columns. Rows either have data for a column, or an empty value in which case length will be zero.
The three possibilities are the (1) column exists with a value, (2) column exists without a value, (3) and column doesnt exist.
testing by field length wouldnt work as option 2 & 3 would yield the same result but i need them to be separate. on 2 i would want to use the empty value as it is a true null. on 3 i would want to separate the path so im not even trying to use any value there as then i could potentially send a null on the final action (which could end up overwriting true data with null)
Sounds like you have an assumption that a single automation can handle different CSV’s with different schemas, which I don’t think is possible. That is why there is no “exists” logic tests within automation logic, it is expected that triggers always emit data rows having the same data fields.
I think you will need separate automations for each CSV.
If you have lots of common processing among them all, then maybe the “execute automation” action can be of use for that.
Another option may be to build a modified CSV trigger. It would need to emit the “logical AND” of all possible columns for all possible CSV’s. Plus it would need to emit one or more sentinel fields to help you determine which schema is being emitted, e.g. it looks at the columns received and emits a sentinel value that is used in conditional logic within the automation.
It was less of an assumption and more of a “man this would be easy if i could do it this way” kind of thing. I will find a work around to handle it.