Flat File Output with Fillers and Spaces

[aqilkurji]

Hi everyone, 

I am trying to output a flat text file at the end of my Alteryx workflow to match a specific data dictionary provided to me. This has a few fillers and the field length of each column is provided - see example below: 

My plan of action here was to create a column for each of the fillers and insert whitespace and pad right on rest of the values where there are no nulls. At the end of my workflow I have a select tool that specifies the max field sizes again and then I output a fix delimited txt file.

The output here is not matching with what I expected. I have certain fields overlapping within the txt file and the order is off: Here is an example:

How can I prevent this from happening. I have attached the flat output as well.

Also - how can i add headers and trailers in this file with different separation lengths than the main data?

Thanks!

[FreeRangeDingo]

Can you show a screenshot of your Select tool to see the data types?

[aqilkurji]

Yes - They are all string types with the specified lengths I want them to be. Maybe the txt file only can werite a certain number of characters in one row? The order is correct but it moves onto the second row. 

Also I have the trailer and header in two different select tools and would want to include them in the flat file. 

[FreeRangeDingo]

Try changing them from V_WString to String or String: Forced.  That should generate the whitespace.  

[aqilkurji]

It is generating the whitespace but the output is going to line 2.

[JimmyV]

I have two workflows to build out the what you're asking for and attached both workflows and output file to this reply.

• Create_Flate_Files: This will create 3 different types of flat files for different separation for the differing Header, Main, and Trailer file structures
  • Header Flat File:
    • To add in the spacing(filler), just add up the spacing needed between the fields + the field length
    • I like to add in the description the field length + all of the spacing to the next field
  • 
  • Main Flat File:
    
    • For the Main flat file output, I'll change up the spacing(filler) to help illustrate the different file types in the final output.
  • 
  • Trailer Flat File:
    • Again, same as the Main flat file output, I'll change up the spacing(filler) to help illustrate the different file types in the Final output.
  • 
• Merge Flat File: The final workflow to merge the three (in this example) different flat file structures (Header, Main, & Trailer) into one final output
  • We'll need a way to structure(order) our output file so that our 3 ProductsIDs have a Header, Main, and Trailer and in that order.
     
    1. For each flat file type (Header, Main, & Trailer), Input that data as a Flat File and Read it as a fixed width text file
       
    2. Use a Formula tool to create a couple of fields to order the final flat file output. After we sort on these fields, we'll remove them right before the final output.
       
    3. Use a Union Tool to merge all of the flat files into one.
       
    4. Use the Sort Tool to order how you want the final output to look.
       1. 
       2. 
    5. Use a Select Tool to remove the ProductID and RecordType fields
       1. 
       2. 
    6. Output the file as a Flat file and you should have a file that looks like the following