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. 

Patrick2788's avatar
Patrick2788
Silver Contributor
Do you have a sample you can share?
achow1565's avatar
achow1565
Copper Contributor
Jul 06, 2023

Patrick2788 this would be the copy/paste from the email body. Every other row is a header. You'll see the headers repeat for quantity, customer PN, MN PN, and MN SN. The headers only repeat once in this example, but the form can allow it to repeat as many times as entries are present.

Company Name
test
Buyers Name
testy mctesty
Buyers email
email address removed for privacy reasons
Country
United States
Product Line
Mechanical
Parts Description
rod
Shipping Tracking Numbers(s)
fedexnumberhere
Customer Return Order #
C123456
Reason for Returning Parts
Overhaul / Repair
Quantity
2
Customer Part Number
123456
MN Part #
A1001
MN Serial #s
126; 128
Quantity
1
Customer Part Number
234567
MN Part #
A1002
MN Serial #s
226
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    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. 

    • achow1565's avatar
      achow1565
      Copper Contributor
      Jul 07, 2023
      This worked! I will need to get on 365 but it worked fine on the online 365 version!
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Jul 07, 2023
        Glad it worked!
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jul 07, 2023

    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;

Resources