Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

read JSON file to database

Highlighted
6 - Meteoroid

Hi

 

Just downloaded the trial of alteryx as I want to prove that this is the tool for us.

First, simple task I hope, is to read a json fila as input and simply store it to my database. (netezza)

 

I tried to just use input data and then output data, but get an error like this:

Error: Output Data (5): Error creating table "MBC1": ERROR: Invalid datatype - 'NTEXT'
CREATE TABLE "MBC1" ("JSON_Name" ntext,"JSON_ValueString" ntext) DISTRIBUTE ON RANDOM

If I transform the json to csv I get to read it. But now I also get a lot of stuff I do not want. LIke HTML stuff. (look at the description value in the picture below)

Also it should be about 100 columns in the json, but the create table statement indicates that it only try to store two?

 

Here is a sample from the JSON

JSON_SETUP.jpg

 

any tips and tricks would be appreciated

 

BR 

DJ

Highlighted
ACE Emeritus
ACE Emeritus

Hi


The JSON file needs parsing and the data preparing for output into your database. 


Start by using a JSON parse tool after the input. 

 

kr

 

Alteryx Everything, Leave no one behind.
Highlighted
6 - Meteoroid

Hi GavinAttard

 

Thanks for your input,

I tried using the JSON Parse but I`m obviously doing something wrong.

Please find below the printscreen result

 

json_2.jpg

Highlighted
ACE Emeritus
ACE Emeritus

Hi

 

Bit difficult to diagnose from just the screenshot. 


can you   upload the workflow?

 

kr

 

Alteryx Everything, Leave no one behind.
Highlighted
6 - Meteoroid

Like this?

 

Highlighted
ACE Emeritus
ACE Emeritus

almost there, i will need the json file you aretrying to parse. (if you can share it that is :-) ) 

 

Also, the easiest way to share workflows is to package them. This is found in options export workflow. It will package all related files into one. 

 

kr

 

 

 

Alteryx Everything, Leave no one behind.
Highlighted
ACE Emeritus
ACE Emeritus

almost there, i will need the json file you aretrying to parse. (if you can share it that is :-) ) 

 

Also, the easiest way to share workflows is to package them. This is found in options export workflow. It will package all related files into one. 

 

kr

 

 

Alteryx Everything, Leave no one behind.
Highlighted
6 - Meteoroid

Hope I did it right now

 

 

DJ

Highlighted
ACE Emeritus
ACE Emeritus

HI

 

Perfect, Thanks. had a look. 

 

Firstly, my bad, the INPUT tool is already parsing the JSON. So that's all good. 

 

From here you need to make decision as to how you want to prepare the date to store in the database. The JSON String contains loads of information pertaining to the data source, the fields, field values etc... 

 

So you need determine what data you want from the string. 

 

Also i notice the data is from Sharepoint or Azure, there may be connectors within ALTERYX ready made allowing you do download the data you need without going through this trouble

 

See attached. 

 

kr

 

 

Alteryx Everything, Leave no one behind.
Highlighted
6 - Meteoroid

at first glance this looks nice.

I will test it with a larger file.

 

Thanks for your time and patiance 

 

 

BR
DJ

Labels