Hello, I need to run reconcile-csv across a bazillion columns and would rather not do it one column at a time. Do you know of a way run this across all columns at once? Maybe you have a script you can share that interacts with the API? Thank you in advance for any advice you can provide.
1 Like
I could also use such a system/tool/script.
I have similar cases where I have similar columns “material1”, “material2”, “material3”, etc. and I basically do the same reconciliation on each columns.
1 Like
OpenRefine has a function called transposing.
Using this you can transform a dataset like this
| ID | material1 | material2 | material3 |
|---|---|---|---|
| 1 | mat11 | mat21 | mat31 |
| 2 | mat12 | mat22 | mat32 |
| 3 | mat13 | mat23 | mat33 |
to a dataset like this
| ID | key | value |
|---|---|---|
| 1 | material1 | mat11 |
| 1 | material2 | mat21 |
| 1 | material3 | mat31 |
| 2 | material1 | mat12 |
| 2 | material2 | mat22 |
| 2 | material3 | mat32 |
| 3 | material1 | mat13 |
| 3 | material2 | mat23 |
| 3 | material3 | mat33 |
You can then use the second dataset with reconcile-csv or csv-reconcile using the value column.
I am currently not sure whether the csv reconciliation services need a unique ID. So you may need to add an id column just for reconciliation by using rowIndex as GREL expression.
| Recon-ID | ID | key | value |
|---|---|---|---|
| 1 | 1 | material1 | mat11 |
| 2 | 1 | material2 | mat21 |
| 3 | 1 | material3 | mat31 |
| 4 | 2 | material1 | mat12 |
| 5 | 2 | material2 | mat22 |
| 6 | 2 | material3 | mat32 |
| 7 | 3 | material1 | mat13 |
| 8 | 3 | material2 | mat23 |
| 9 | 3 | material3 | mat33 |
This is very helpful, thank you!