One Star

[resolved] seperating json data into different columns

hi, i have been trying to seperate the received json file data into different columns, but i cannot do it, i have used 'twritejsonfield' but the columns are getting the name of the column also..i just want the values to get loaded..some one please help..

This is what i have done so far..
17 REPLIES
Moderator

Re: [resolved] seperating json data into different columns

Hi,

Could you please elaborate your case with an example with input and expected output values?

Have you tried to use component TalendHelpCenter:tExtractJSONFields?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] seperating json data into different columns

hi,
i have used 'textractjson' to parse columns  for the first two columns id and meta its working fine but for 'payload' column i am getting a error as: 

'Oct 08, 2014 11:05:59 AM net.sf.json.xml.XMLSerializer getType 

INFO: Using default type string
Error on line 1 of document  : An invalid XML character (Unicode: 0x8) was found in the element content of the document. Nested exception: An invalid XML character (Unicode: 0x8) was found in the element content of the document.'

can you please help me with the error. The job screenshot is attached along with the screenshot of the components detail.
 op data expected
 ip data
Seventeen Stars

Re: [resolved] seperating json data into different columns

read the error and you got the answer :
An invalid XML character (Unicode: 0x8) was found in the element content of the document

if you look for information about Unicode : 0x8, it's the backspace caracter that cause trouble
regards
laurent
One Star

Re: [resolved] seperating json data into different columns

i used tmap to trim the columns which were giving error but now a new error is coming up kindly see the screenshot.
i tried using tmap after textractjson also but then the previous error used to come.


kindly help..
Seventeen Stars

Re: [resolved] seperating json data into different columns

you have to modified your source file (json) I guess. Trim your data after parsing json file is 'too late'.
So add a 'cleansing job' to normalize it and be able to parse it with talend component. (try an hexa editor to find this caracter in source)
hope it help
regards
laurent
One Star

Re: [resolved] seperating json data into different columns

i cant change my source(json) so is there any way i can do it with talend.
why the trim() function doesnt work in tmap to trim the data??
Seventeen Stars

Re: [resolved] seperating json data into different columns

trim() is for extra caracters at tstart and end string.
perhaps try replace carater ascii 0x8 by empty carater (string)

String.replaceAll("\\x08","")

here's some more explanation :
http://seattlesoftware.wordpress.com/2008/09/11/hexadecimal-value-0-is-an-invalid-character/
regareds
laurernt
One Star

Re: [resolved] seperating json data into different columns

i should be using this in tjava component right??
if yes where i should be placing it in my job...for reference screenshots are already attached in previous posts.
thanks for your help...
One Star

Re: [resolved] seperating json data into different columns

how can i decode b64 value in talend??
plz help..
Seventeen Stars

Re: [resolved] seperating json data into different columns

as far as I know you have to code encode/decode by yourself.
use org.apache.commons.codec.binary.Base64 library for that.
regards
One Star

Re: [resolved] seperating json data into different columns

i tried using this but its not supported in talend..so i downloaded a jar file...am not sure in what path it should be copied so that it supports it.....or is there any other way...
kindly help...
Seventeen Stars

Re: [resolved] seperating json data into different columns

use tLoadLibrary ... to load .jar:
https://help.talend.com/search/all?query=tLibraryLoad&content-lang=en
then in a tJavaRow , use the encode/decode method with the string you want to manage as parameter.
Seventeen Stars

Re: [resolved] seperating json data into different columns

have a look at thsi exchange component (not maintained  but it could work)
http://www.talendforge.org/exchange/tos/extension_view.php?eid=142
One Star

Re: [resolved] seperating json data into different columns

 it has been prompting me to download "ostermillerutils_1_07_00.jar" file, the download option is not working....when i tried to download it externally and load it its not taking and the error is coming to download the file....how to sort out please help
.....
One Star

Re: [resolved] seperating json data into different columns

try using tReplace it works for me Smiley Happy
One Star

Re: [resolved] seperating json data into different columns

i would like to truncate a oracle table at a interval of evry 7 days...and want that job to run automatically.....can someone help please!!!!
Moderator

Re: [resolved] seperating json data into different columns

Hi sailesh1809,
Please open a new topic for your issue or edit the related forum to add your requirment in forum :http://www.talendforge.org/forum/viewtopic.php?id=38835.
In that way, other community users have a chance to see your issue and give a hand to you.
The current topic is not related to your issue.
Many thanks.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.