If there such a need for a flat file generating lambda. Able to make csv/tsv (tilde)/pipe/tab delimited, files, etc. In my case a need arises to concatenate the header with the cell. This was a more monumental task until I found out that the old "&" (thee ole Concatenate function) can handle arrays. Joining the result and you get a flat line for your data exchange needs.
FORMATDATE (credits ADATE, reduced to a smaller function, It could improve yet with newer functions).
I'm thinking of ways to improve this, as it is for my specific use-case at a software where I work. This is a simple way to send rows of data to the system where the columns map again, but we don't have to worry about copy-pasting headers, finding the rows, and then copy-pasting the rows.
We could:
- hiding columns, blanking out headers
- erases header and data from output
- except last column, in case you need to append something.
- column is competely empty
- also erases header and data form output
- sample output
Excel Formula:
=FILTER(TOFILE(Table2[#Data],OFFSET(Table2[#Headers],-1,0),"~",1), Table2[Email]="r")Nome~c~email~r~ID~3~Date~06/27/2021 Nome~c~email~r~ID~6~Date~06/27/2024 Nome~c~email~r~ID~~Date~06/27/2027
a & ""
Excel Formula:
=LAMBDA(a,header,delimiter,[transformdates],
LET(
transformxdate, IF(
OR(ISOMITTED(transformdates), NOT(transformdates)),
a & "",
FORMATDATE(
a & "",
XLOOKUP("*Date", header, COLUMN(header), 0, 2),
"MM/DD/YYYY"
)
),
cleancolumns, IF(ISERROR(transformxdate), "", transformxdate),
cellwidth, BYCOL(header, LAMBDA(l, LARGE(CELL("width", l), 1))),
filteronlyvisible, FILTER(cleancolumns, cellwidth <> 0),
columnisnotempty, NOT(BYCOL(filteronlyvisible, LAMBDA(x, AND(x = "")))),
headersvisible, FILTER(FILTER(header, cellwidth <> 0), columnisnotempty),
columnsoutput, FILTER(filteronlyvisible, columnisnotempty),
concatwithcolheader, headersvisible & delimiter & columnsoutput,
omitblankheaderexceptlast, IFNA(
IF(ISBLANK(DROP(headersvisible, , -1)), "", concatwithcolheader),
IF(
ISBLANK(CHOOSECOLS(headersvisible, -1)),
columnsoutput,
concatwithcolheader
)
),
BYROW(omitblankheaderexceptlast, LAMBDA(join, TEXTJOIN(delimiter, , join)))
)
)FORMATDATE (credits ADATE, reduced to a smaller function, It could improve yet with newer functions).
Excel Formula:
=LAMBDA(a,cl,df,
LET(
c, MAX(0, cl),
l, COLUMNS(a),
sl, SEQUENCE(, l),
IF(sl = c, TEXT(a, df), a)
)
)I'm thinking of ways to improve this, as it is for my specific use-case at a software where I work. This is a simple way to send rows of data to the system where the columns map again, but we don't have to worry about copy-pasting headers, finding the rows, and then copy-pasting the rows.
We could:
- Allow output of standard tables by stacking the headers instead of concatenating with each cell. This is simple with an optional parameter.
- Stacking tables. You can already see Stacking The Beatles in Excel: An Exercise in 3D stacking for using multiple tables as a source.
- If two rows of headers are used for input, handle those
Upvote
0
