csv to fixed width file

[mdauria]

Does anyone know the process to change a csv to a fixed width file?

Parameter's below:

[trice602]

Hi @mdauria ,

If those are the requirements at the end there, fixed width, ASCII only, alignment -- that is referring to an Excel file not CSV.  I do not believe what you are asking is technically possible. My recommendation is to communication/convince your project sponsor the difference between a CSV file and an Excel file. 

Please mark as a solution and/or give me a thumbs up for replying and offering help!  Always glad to help! Tom 👍🏽

[mdauria]

The final output is a fixed with file with no extension.

[trice602]

Hi @mdauria ,

But still, I am assuming you are referring to an Excel file, which is .xlsx and column widths and alignment is only available (in this context) as an Excel file, not a CSV file.  That said, if you are looking to align and auto-adjust your column widths in Excel, as your final output from a starter file that is csv, here's a quick solution to do that:

Run a quick desktop flow from your cloud flow and open your csv in Excel.  Move your cursor to cell A1.  Press control T to create a table.  This is going to automatically give you 99.9% of what you may be referring to, if you need more such as a specific column 250 pixels wide or a text column centered, you can grab that column and make your changes.  Keep in mind, we are still talking about an excel file, to save your width and alignment changes, you have to save the file as an excel file; these are features that only exist in Excel as .xlsx and you cannot save your changes in a csv file.  That's why if you take any .xlsx file and try and save as a csv, you get a warning message stating if you do this you will lose your formatting if you save as .csv

Please mark as a solution and/or give me a thumbs up for replying and offering help!  Always glad to help! Tom 👍🏽

[mdauria]

Thank You.

[hrangel-gg]

Just to clarify @mdauria's question, the file mentioned is indeed a text files similar to CSV but instead of comma separated the fields have fixed length and can be aligned to left or right using space, see example below.

Field ABC, Length 5:

|123  | -> two zeros to the right to make 5 in total, so left aligned.

|  123| -> two zeros to the left to make 5 in total, so right aligned.

For suggestion to solve the question:

1. To read the CSV use this tutorial, but instead of an apply to each to create the final array use a select (it's faster):
   Power Automate: How to parse a CSV File to create a JSON array (tachytelic.net)
2. On the properties of your select, concat spaces (as many as your field size), then substring to keep the field size. Ex.: substring(concat(item()['PDCCode'],'   '), 0, 3)
3. To join all fields in one line use either a concat in the select (like I did) or an apply to each after it appending all fields to a string variable.

Formulas I used in the example (maybe the join is not needed, need to test in your case):

• concat(substring(concat(item()['ID'],'     '), 0, 5),substring(concat(item()['Name'],'     '), 0, 5))
• join(body('Select'),decodeUriComponent('%0D%0A'))