Converting multiple .csv files with JSON text into usable data

[psanchez]

I was hoping for some help for an activity that I am working on, specifically the Escaping the Digital Dungeon activity created by E&Y. I currently have 53 .csv files on excel that I managed to pull into Alteryx using the directory tool. These files all contain JSON text, and I need to sort through the data contained on them. I can view this data if I open the files individually with notepad, but aren't sure how to put all of them into alteryx so I can use the data cleaning tool. Any help would be really appreciated!

[psanchez]

I've realized that perhaps the directory tool may not be the right tool to use there either, as I need to input the data, not just view its location, help there would be appreciated as well!

[apathetichell]

directory tool is correct to get the fullpath of your 53 files. You will need to feed the paths into a batch macro. Are they .json? are they text? if they are .json you'll use a text input tool in your batch macro set up for \0 (no delimiter). You will then need to concatenate the json in a single field (via summarize tool) - and then use a json parse tool. You will attach this to a macro output. Place your batch macro on your canvas.

[psanchez]

I'll attach one of the 53 files with this post so you can see. I'll try what you said for now, but let me know if you can help at all with the Summarize tool and Concanating. I tried to do this with one file earlier and could not figure out how to get all the text to enter a single cell. Thanks again!

[Qiu]

@psanchez 

I import the file as CSV with \t (Tab) as delimter then a JSON Parse tool can parse it without any issue.

[jdminton]

@Qiu is correct. I added a couple of extra tools to make the data more like how you probably want it. I also added the requirement of multiple file input you were needing. You will need to point the directory to your dataset location.

 

[apathetichell]

@psanchez follow these instructions - and post if you are having more issues. Sounds like a fun project. I personally never use dynamic input (as used by @jdminton ) - but if it works - it works.