Text to Tabular form for excel

[Mavericks334]

Hi,

I'm trying to convert a text file into a tabular form and send it to an excel file.  The text file has multiple rows i have tired to use multi row formula was able to create only a single column tried adding another multi row row formula to get the other details i get only the 1st row. Below is the screenshot  of how my data looks and what i am trying to achieve

This how my raw data looks.

This how i require the final output file

I have tried this has my multi row formula

if startswith('ICS1500-4','REPORT_NAME') then replace('ICS1500-4','REPORT_NAME','') else('ICS1500-4') endif

I tried the similar option for date by using

if startswith('ICS1500-4','REPORT_DATE') then replace('ICS1500-4','REPORT_DATE','') else(DateTimeAdd(DateTimeToday(),-1,"days")) endif

It does not seem to work. Any suggestion how i could get the complete file to be converted?

Regards,

Ren

[mceleavey]

Hi @Mavericks334 ,

Can you provide a data sample so we can help?

Thanks.

M.

[Mavericks334]

Hi mceleavey, I'm attaching the sample file. Please review and let me know

[mceleavey · Accepted solution]

Hi @Mavericks334 ,

this was a blast from the past! A horrible old non-deliminated text file.

The best way to fix this is to go back to the people running the source system providing the file and tell them to sit in the corner and think about what they've done.

However, attached are the tools you'll need. I've built the workflow, including a batch macro I built, a CReW macro you'll need (Shoutout to @MarqueeCrew  ) Save the .yxmc files in your macro folder, then run the workflow.

Hope this helps.

M

[Mavericks334]

Hi mceleavey,

Thanks for the solution. Will try to get it to work at my side.