Convert Json to CSV with space

Six Stars

Convert Json to CSV with space

Hi,

 

I have a json payload with 50 fields. I need to convert the same and upload to an endpoint.

The twist is the field names have space between them. e.g First Name.

I am using tExtracJson and tFileOutputdelimited. This component doesnt accept space.

Can someone suggest how to resolve this issue without having to replace the space with no space or underscore.

 

Thanks


Accepted Solutions
Community Manager

Re: Convert Json to CSV with space

You cannot put spaces in headers because they are essentially variables. This can be pain sometimes. However, there is an easy solution (hack) for this. You have a process where you are moving data from JSON to CSV. I assume that works. What you need to do is change the config of the file output component to NOT include the header and to "Append". Then before this process(subjob) takes place, create another subjob with the sole purpose of writing the header. Use a tFixedFlowInput with one row and hard code your header names. You can have whatever you want. Connect this to a file output component (not the one you are already using) and make sure "Append" is unticked and"Include Header" is unticked. 

 

What this does is write a single row at the beginning of a new file....this is your header row. You then append to that file with the flow of data you already have.

pic1.png

 

 


All Replies
Forteen Stars

Re: Convert Json to CSV with space

do you have Json file,which you wan tot convert right? first try to create for metedata for Json file,when you try to create metadata it will replace space with '_',please below screenshot for your information.Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Six Stars

Re: Convert Json to CSV with space

Hi,

I dont want to replace space with underscore. I want the Field name as is with space. Json has it with space and my target also has it with space.

So I need a csv file with space in field name

Forteen Stars

Re: Convert Json to CSV with space

if you are getting from database,first create a metedata for the Json format and call the metadata in tExtractJsonfield of schema.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Convert Json to CSV with space

In Talend will not allow you with space,!,@,#,$,%,^,&,*,(,),- ,+ and = column names.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Convert Json to CSV with space

Hmm..any other way? 

 

This is my scenario:

I get a json file with data. I need to convert to csv. I need to map the fields to a survey. The survey fields has space. so i must have space between fields in csv too.

Can I create a metadat with space

Forteen Stars

Re: Convert Json to CSV with space

you need to change the space to _as i mention in sceenshot.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Convert Json to CSV with space

But when I map the field, it errors our right?

Forteen Stars

Re: Convert Json to CSV with space

correct,so thats why first you need to create a metadata ,while creating metadata it will convert space with '_' so you can use the same metadata.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Convert Json to CSV with space

This is exactly what I am telling. The space is getting converted to _ . But the field that I must map to does not have _ . So how do I do it?

I am using tHttpRequest to send the data. The output schema must have field name with space. But I am not able to give it. How do I proceed

Community Manager

Re: Convert Json to CSV with space

You cannot put spaces in headers because they are essentially variables. This can be pain sometimes. However, there is an easy solution (hack) for this. You have a process where you are moving data from JSON to CSV. I assume that works. What you need to do is change the config of the file output component to NOT include the header and to "Append". Then before this process(subjob) takes place, create another subjob with the sole purpose of writing the header. Use a tFixedFlowInput with one row and hard code your header names. You can have whatever you want. Connect this to a file output component (not the one you are already using) and make sure "Append" is unticked and"Include Header" is unticked. 

 

What this does is write a single row at the beginning of a new file....this is your header row. You then append to that file with the flow of data you already have.

pic1.png

 

 

Six Stars

Re: Convert Json to CSV with space

Hi,

I tried the approach. It populates the header. But I need the csv file to be generated like this:

 

Field1,Field2,Field3,Field4, etc

Currently I am getting it as

Field1

Field2

Field3

Field4

Screen shot of my setting is attached. I tried various approaches and apart from hardcoding, nothing seems to work.

 

Six Stars

Re: Convert Json to CSV with space

Got the solution. Thanks

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read