Question

Sperate Columns


Badge +5

I have excel file where i need to separate all columns please help me out


12 replies

Userlevel 4
Badge +25

That looks like JSON, so a JSONFragmenter / JSONFlattener would be the way to go.

Badge +5

i have data in Excel

Badge +5

how can i seprate them

Userlevel 4
Badge +25

i have data in Excel

Yes, the data is in Excel.

 

But the content of the attribute locations_affected is encoded in JSON. So if you use a JSONFragmenter on that you can pull it apart to separate attributes:

Screenshot 2022-10-13 at 09.12.38 

Badge +5

Yes, the data is in Excel.

 

But the content of the attribute locations_affected is encoded in JSON. So if you use a JSONFragmenter on that you can pull it apart to separate attributes:

Screenshot 2022-10-13 at 09.12.38 

okay how can i expose the values and write in Excel file

Userlevel 4
Badge +25

Yes, the data is in Excel.

 

But the content of the attribute locations_affected is encoded in JSON. So if you use a JSONFragmenter on that you can pull it apart to separate attributes:

Screenshot 2022-10-13 at 09.12.38 

Have you tried the Attributes to Expose parameter in the JSONFragmenter? The downside of that is that you need to know the attribute names.

Badge +5

Yes, the data is in Excel.

 

But the content of the attribute locations_affected is encoded in JSON. So if you use a JSONFragmenter on that you can pull it apart to separate attributes:

Screenshot 2022-10-13 at 09.12.38 

why geometry is in sepetr row

 

image

Userlevel 4
Badge +25

Yes, the data is in Excel.

 

But the content of the attribute locations_affected is encoded in JSON. So if you use a JSONFragmenter on that you can pull it apart to separate attributes:

Screenshot 2022-10-13 at 09.12.38 

That's how the JSON is structured, you'll need to ask whoever designed that. However, the xlsx_row_id has the original row number from Excel so you can still match up all the data. It does look like there's multiple objects (2 it seems) per Excel row. That makes me think you really need some background information on this dataset to make informed decisions. Again that's something you need to check with whoever is supplying the data.

Badge +5

I did this how can i bring json_index in columns

 

image

Userlevel 4
Badge +25

I did this how can i bring json_index in columns

 

image

Honestly, I think you've maybe gone one step too deep into the JSON (that's controlled by the number of [*] in the JSONFragmenter) and one step higher would yield more useable results.

 

Without seeing your workspace, knowing what you have already tried or knowing what you want to do exactly it's hard to give you good advice.

Badge +5

Please find the attached

Userlevel 4
Badge +25

Please find the attached

Have you tried [*][*] in the JSONFragmenter (like I initially did and hinted at with my last answer) and if so, why did that not yield the results you want?

 

Please note that on a forum like this people are sharing their knowledge freely, but do so either on their own time or by taking time out of their work day. A good way to acknowledge that is to show commitment by providing the information needed to answer your question and to also try things yourself. We want you to learn FME, but the best way you can learn it is by actually doing it yourself, by experimenting (and sometimes failing).

 

I can't help you any further today, I need to work on other stuff.

Reply