Text To Column How to align data in the Correct column while fetching data from an API

[Ashwin1]

Hi All, 

         I am fetching data from an API. Then i use Text to Column to order the values in column. 

         The issue is the row of values does not match. For e.g. Row 1 might have 20 Columns, Row 2 might have 22 Columns, Row 3 might have 21 columns. So the values are not under the right column. Is there anyway to work with this. Find Attached sample Sheet 

	ColA	ColB	ColC	ColD
ROW1	A	B	C	D
ROW2	A	B	C	D
ROW3	A	C	D
ROW4	A	B	C	E

In the Above sheet you can see that in Row3 ColB has a value 'C' when it has to be 'B' or 'null' but since the API does not have a null or anything it is fetching the next record and putting in the wrong column.

Any Suggestions would help

Thanks 

Ashwin

[Jonathan-Sherman]

Hi @Ashwin1,

Can we see the output response of the API call? Are there any column headers returned?

Regards,

Jonathan

[Ashwin1]

Hi Jonathan,  

                     I am afraid i wont be able to share that but yes it does return headers and values in this format. 

ROW1 -"COLA":"A" , "COLB":"B", "COLC:C","COLD":"D"

ROW2- "COLA":"A", "COLC":"C", "COLD":"D"

ROW3- "COLA":"A", "COLB":"B", "COLC":"C","COLE":"E"

Regards

Ashwin

[Jonathan-Sherman · Accepted solution]

Hi Ashwin,

Is this what you're looking for? You can remove the RecordID if needed with a select tool, i simply used it as a row number to group the fields together.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workbook for you to download if needed.

Regards,

Jonathan

[Ashwin1]

Fab... Thanks Much Jonathan-Sherman..