OT - converting CSV to a structured text file?

Off Topic
1

This is a non-epicor request, and maybe a long shot. However, I’m on a short timeline and thinking maybe one of you smart people, might have done something like this before.

I’m trying to automate, or at least semi-automate turning a CSV file into a structured text file. Below is a screenshot of both the input file (CSV) and the desired output (TXT), where I’ve color-coded the fields. I’ve also attached the 2 sample files if anyone wants to ‘play’ with them, the actually CVS file will be bigger with more rows and columns.

image
image.png1681×981 125 KB

Any thoughts or pointers on the quickest most efficient way to do this would be helpful.

2

Have you tried using a Macro to do this?

3

You could try consuming the CSV into a Datatable, then performing your actions upon that datatable to write out your structured txt file

1 Like
4

Looks like you want to do the opposite of a Pivot table.

I’d do it all in Excel First I’d copy the data and paste Special into a new sheet - using Transpose option to get the data into to columns

5

see if this works…

  1. Put your data in cells B4… xxx5.
  2. Extend the table to the width of your data.
  3. Extend the length of lower table as necessary
  4. Update the formulas in the lower table that determine the Row and Col numbers (they reference a specific range from the upper table, and will need to change if the upper table’s width changes)

E10-helper.xlsx (15.6 KB)

image
image.png1349×555 50.3 KB

1 Like
6

Thanks, @ckrusenm, this is awesome! If I weren’t doing 4 projects all at the same time I most likely could have figured this out.

I definitely owe you a beer or 2. Are you attending Insights?

7

Glad to help. Please double check everything. Lots of places where Abs cell references(like A$5$) should be used but weren’t.

And I’m not attending Insights this year. Maybe next year.