Forum Discussion

achow1565's avatar
achow1565
Copper Contributor
Jul 06, 2023

Creating table from email body and repeating columns

I want to convert an email body from a website form to an easy to read table so we can enter it into our Access database. This is what would come in after copy/pasting the email body into Excel. ...
Macros and VBA
Power BI
  • Patrick2788's avatar
    Patrick2788
    Jul 06, 2023

    achow1565 

    This will do it. I hope you have 365!

    =LET(
    header, INDEX(list, SEQUENCE(, 13, 1, 2)),
    organized, DROP(
        REDUCE(
            "",
            header,
            LAMBDA(a, v,
                LET(
                    r, FILTER(ROW(list), list = v) + 1,
                    IFERROR(HSTACK(a, INDEX(list, r)), "")
                )
            )
        ),
        ,
        1
    ),
    VSTACK(header, organized)
)

     I created a function to do this. You'll find it all in the workbook attached. 

peiyezhu's avatar
peiyezhu
Bronze Contributor

achow1565 

 

create temp table aa as 
select udf_fillna_m(iif(F_A like 'Company Name' ,rowid,''),'x')  grp,udf_fillna_m(iif(F_A like 'Quantity' ,rowid,''),'y')  grp2,iif(lag(F_A) over() like 'Company Name',F_A,'') f01,iif(lag(F_A) over() like 'Buyers Name',F_A,'') f02,iif(lag(F_A) over() like 'Buyers email',F_A,'') f03,iif(lag(F_A) over() like 'Country',F_A,'') f032,iif(lag(F_A) over() like 'Quantity',F_A,'') f04,iif(lag(F_A) over() like 'MN Part #',F_A,'') f05,iif(lag(F_A) over() like 'MN Serial #s',F_A,'') f06 from Creating_table_from_email;
create temp table bb as 
select  grp,grp2,group_concat(f01,'') f01,group_concat(f02,'') f02,group_concat(f03,'') f03,group_concat(f032,'') f032,group_concat(f04,'')
||'</td><td>'||group_concat(f05,'')||'</td><td>'||group_concat(f06,'') f06 from aa group by grp,grp2;
//select * from bb;
select * from ((select grp,f01,f02,f03,f032 from bb where grp2='') join (select grp,f06 from bb where grp2!='') using(grp));
//select * from Creating_table_from_email;
achow1565's avatar
achow1565
Copper Contributor
Jul 07, 2023
where would I put this code in Excel?

Resources