SAP text files input on alteryx and the format changes

[John_Sar-1937]

Hello to everyone,

I am dealing with a problem whenever i am trying to input SAP text files on Alteryx. While the primary TEXT FILE is ok, whenever i input it on alteryx the format changes. Substring does not help because columns are not straight.

I am giving you an example of how the primary text sap file looks and how it is in alteryx. 

My problem is that i cannot make them in particular columns. 

Thanks everyone,

[ChrisTX]

Posting 2 pictures inside an Excel file does not adequately show the actual input and output data.

Can you post the actual text input file with just a few records, the Excel output file, and your workflow?

[John_Sar-1937]

Thank you very much for your response.

I have not been able to make a workflow or an output (actually i have done one but i do not think it is gonna help).

My problem is that whenever i input the txt file the format is different (numbers are going left or right and are not straight in a line one below the other). I uploaded the txt file. 

I want to find a way to make them all in line (one below the other just like the primary txt file). If i am able to do this then it will be easy to make a workflow.

Thanks again for your response,

[ChrisTX]

@John_Sar-1937  the attached workflow likely won't be perfect, but it should get you started.

The major challenge is that your report columns are separated by spaces.  I made the assumption that at least 6 spaces are between each column.  The first tool (Input Data) skips the first few blank rows, and has the option de-selected for First Row Contains Field Names (which will bring in all data as [Field 1]), and has Delimiters set to \0 (no delimiters) (which will import all data into one field).

Then the workflow needs to figure out the different sections of the report... Header, Detail, and Footer.

Hope this helps.

Chris

[oly]

Hi @John_Sar-1937 ,

Please find a sample workflow attached. I imported file without delimeters (make sure length of the field is sufficient, default is 254 characters, I changed to 2540) and then parsed with RegEx tool.

You can filter empty records from here and use rename tool to pick first line values as column names.

Best,

Oly

[Amol_Telore]

Hey @John_Sar-1937 

Here is my solution. Another way of parsing the data using Regex similar to other two solutions. 

Workflow :-

Output :-

Regards,

Amol Telore

[John_Sar-1937]

Thank you very much for your response!

[John_Sar-1937]

Thanks a lot for your response!

[John_Sar-1937]

Thank you for your response!

[nveliv]

Hi @Oly

Im facing a similar problem to John Sar-1937. I tried to use your sample workflow on "2.isozygio.txt" (please find attached) and im getting the output presented in "Picture1.jpg" (numbers and words are not separated properly / Field_11&12). Then i slightly change your Regular expression to (.*?)(?:[\n\t\f\v]*?\s\s*)(?:\s\s*) and i get the output presented in "Picture2.jpg" (numbers are fairly presented in Field_11 but now words are presented in Field_13 (some even in Field_14)). Would you be kind enough to help me with this problem please?

Thank you in advance!