Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parsing Array Into Columns and Rows from a text file

ScottDexter
6 - Meteoroid

This may have already been addressed before so feel free to post suggestions to other posts. In plain English, I have used a lot of other tools but not Alteryx, this is my first experience with Alteryx, I am sure a solution is out there, just not sure the best way to get it done. 

See the 2 files attached. The first a text file is an example of a text file that I have to parse into columns and rows the actual has around 8K rows. Everything is in a single row in the file. However, if you notice there is a header as well that is not of the same length as the data I would like to import into a database table. My lack of knowledge of the Alteryx product has me trying to get this data programmed in and pivoting to form columns and rows. See the second as my data in the format I would like, excluding the first 2 rows of course I only want to import the second section. My general file, not the one supplied to this post is about 8000 rows when parsed as denoted in the first row of the excel file. the data is variable length by row so a fixed width file doesn't work, I am not sure how to get this started. 

 

In the file I have noticed that there are items that can help parse out the rows such as the { and } braces, additionally the actual data does not begin until the word "resultArray" and a [ bracet is used to denote the beginning of the complete data feed. The end of the file ends with }]}. 

 

To summarize the purpose of this post is to take a single lined text file and get the rows and columns out of the data after the header.

 

Help: Please be as explicit as possible when providing me with information that will be helpful, once the data is pivoted, I can get it in from there so that part will not be an issue for me. 

 

Any suggestion will be taken graciously and is very much appreciated! Thank you so much! 

 

2 REPLIES 2
LordNeilLord
15 - Aurora

Hi @ScottDexter

 

The file you have provided is actually in JSON format, which handles very well. However, the JSON in the sample you provided is not properly formed which means Alteryx is having trouble with it. Your full file maybe be fine or you may have to fix it like I have done in this example.

 

  • I used the formula tool to replace the single quotes with double quotes
  • Strip off everything before "resultsArray"
  • add a "{" to the start
  • Use the JSON parse tool
  • Split JSON name into columns
  • Crosstab the results

JSON parse.PNGWhen you import your txt file pay close attention to the settings I have used on my input tool

ScottDexter
6 - Meteoroid

@LordNeilLord Thank you so much for the solution. I knew there was a way to do this, just don't have that much experience with JSON file types. It took me a couple of tries with the original file but all of those records eventually parsed as well. Thank you so much very grateful for the time you took to help!

 

Scott

Labels