how to convert csv to a nested json format with dynamic columns?

Question

how to convert csv to a nested json format with dynamic columns?

Praveen S 5

I am trying to convert a csv file into a nested json file. so I created a data flow, and used derived column to create a new nested column with sub columns as shown in image. now what I am trying to do is that instead of mapping each column manually, it needs to automatically map each column based on condition eg: nesting all columns that are not listed in a array variable, these columns are nested in one column

expected result:


{
  other_cols:"sgarg",
  nested_col:{
    col1 : col1val,
    col2 : col2val,
    col3 : col3val
  }
}

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-02-09T10:20:03.9133333+00:00

Hi Praveen S ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your issue, you are trying to convert csv data into nested JSON using Mapping dataflow. Please let me know if that is not the ask here.

You have mentioned that you shared image of the dataflow, however, It's not visible in the question you have posted. Could you please reshare the image once.

Also, in case you need to perform auto-mapping of columns , then you can use select transformation and enable auto-mapping option there.
Praveen S 5 Reputation points

2023-02-10T09:15:24.32+00:00
So right now each columns are mapped using derived columns transformation using expression given below, the columns which needs to be included as a sub column varies each time a pipeline runs. Is there any possibilities to make this mapping automatically for dynamic columns.

@( cf_id=cf_id, cf_color=cf_color, cf_age=cf_age, cf_grade=cf_grade )
Praveen S 5 Reputation points

2023-02-10T09:33:04.24+00:00

So, yes I am trying to convert CSV data into nested JSON using mapping dataflow (derived columns). In the derived column, each column need to be nested is manually mapped as a sub column, But the thing is, no of the columns need to be nested varies for each pipeline run. Is there any possibilities to achieve this, mapping dynamic columns as sub column.
Praveen S 5 Reputation points

2023-02-15T09:55:38.46+00:00

Oh okayy, thank you @AnnuKumari-MSFT . Thanks for your help. I will try that if necessary.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-02-17T06:44:52.8533333+00:00

Hi Praveen S ,

Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

1 answer

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-02-09T10:20:03.9133333+00:00

Hi Praveen S ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your issue, you are trying to convert csv data into nested JSON using Mapping dataflow. Please let me know if that is not the ask here.

You have mentioned that you shared image of the dataflow, however, It's not visible in the question you have posted. Could you please reshare the image once.

Also, in case you need to perform auto-mapping of columns , then you can use select transformation and enable auto-mapping option there.
Praveen S 5 Reputation points

2023-02-10T09:15:24.32+00:00

So right now each columns are mapped using derived columns transformation using expression given below, the columns which needs to be included as a sub column varies each time a pipeline runs. Is there any possibilities to make this mapping automatically for dynamic columns.

@( cf_id=cf_id, cf_color=cf_color, cf_age=cf_age, cf_grade=cf_grade )
Praveen S 5 Reputation points

2023-02-10T09:33:04.24+00:00

So, yes I am trying to convert CSV data into nested JSON using mapping dataflow (derived columns). In the derived column, each column need to be nested is manually mapped as a sub column, But the thing is, no of the columns need to be nested varies for each pipeline run. Is there any possibilities to achieve this, mapping dynamic columns as sub column.
Praveen S 5 Reputation points

2023-02-15T09:55:38.46+00:00

Oh okayy, thank you @AnnuKumari-MSFT . Thanks for your help. I will try that if necessary.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-02-17T06:44:52.8533333+00:00

Hi Praveen S ,

Just checking in to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi Praveen S ,

Apologies for delay in response. Unfortunately, it's not possible to create nested JSON if your source schema is not defined and not constant. I tried to check if Column patterns in derived column and aggregate could be helpful , but in your case it wont be since schema is not defined. You need to write your own code in C# or python using custom function to achieve that.

Hope it helps. Please accept the answer if it was helpful. Thanks .

Share via

how to convert csv to a nested json format with dynamic columns?

1 answer

Your answer