API data to talend open studio

[modara]

Hi there,

I am trying to get API calendar data to JSON format , here the configuration i have done. but it gives me error

in trestclient configuration,

in textractjsonfield configation

in textractjsonfield i eidt schema and do this,

error i got,

Starting job calendar_API_Test at 08:55 31/05/2023.

[statistics] connecting to socket on port 3731

[statistics] connected

Could not parse token starting at position 19. Expected ?, ', 0-9, * 

[statistics] disconnected

Job calendar_API_Test ended at 08:56 31/05/2023. [Exit code = 0]

but i am well connected using post man, when i do postman it give me apt data like this,

{

"meta"

: {

"code"

: 

200

    },

"response"

: {

"holidays"

: [

            {

"name"

: 

"New Year's Day"

,

"description"

: 

"New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar."

,

"country"

: {

"id"

: 

"bd"

,

"name"

: 

"Bangladesh"

                },

"date"

: {

"iso"

: 

"2005-01-01"

,

"datetime"

: {

"year"

: 

2005

,

"month"

: 

1

,

"day"

: 

1

                    }

                },

"type"

: [

"Common local holiday"

                ],

if you need any help please do not hesitate , i really need to take data using api and convert to json then export to data base. this is my goal.

please help me to achieve

thanks

[modara]

Hi Shong,

I have came acrosse this with your help. Please guide me last step.

From talend i want to give instrustion to create table and column as per my output from xml

my xml output is,

statusCode|body                                                                                               

|200    |<?xml version="1.0" encoding="UTF-8"?>

<root>200New Year's DayNew Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.bdBangladesh2005-01-01200511Common local holidayOptional Holidayhttps://calendarific.com/holiday/bangladesh/new-year-daybangladesh/new.........................

 

 

i dont understand can i create mysql table and column from talend or i have to create into mysql data base then point table from talend?

how can i map or identify column output from tXMLMAP to tMYSQL

please give me an example

thanks

[modara]

how can icreate table and map txmlmap with tmysql output as my data from postman is like this in xml format

{"meta":{"code":200},"response":{"holidays":[{"name":"New Year's Day","description":"New Year\u2019s Day is the first day of the year, or January 1, in the Gregorian calendar.","country":{"id":"bd","name":"Bangladesh"},"date":{"iso":"2005-01-01","datetime":{"year":2005,"month":1,"day":1}},"type":["Common local holiday"],"primary_type":"Optional Holiday","canonical_url":"https:\/\/calendarific.com\/holiday\/bangladesh\/new-year-day","urlid":"bangladesh\/new-year-day","locations":"All","states":"All"},

or in JSON

{

    "meta": {

        "code": 200

    },

    "response": {

        "holidays": [

            {

                "name": "New Year's Day",

                "description": "New Year’s Day is the first day of the year, or January 1, in the Gregorian calendar.",

                "country": {

                    "id": "bd",

                    "name": "Bangladesh"

                },

                "date": {

                    "iso": "2005-01-01",

                    "datetime": {

                        "year": 2005,

                        "month": 1,

                        "day": 1

                    }

                },

                "type": [

                    "Common local holiday"

                ],

                "primary_type": "Optional Holiday",

                "canonical_url": "https://calendarific.com/holiday/bangladesh/new-year-day",

                "urlid": "bangladesh/new-year-day",

                "locations": "All",

                "states": "All"

            }

[modara]

hello,

please help me to sort this last part to create table in data base and map column

thc

[Anonymous]

Hi

You can create the table in DB and then specify the table name on tMysqlOutput or create the table directly on tMysqlOutput if the table does not exist. In the basic setting panel, select 'create table if not exist' option from the Action on table list, the table structure depends on the schema of tMysqlOutput component, so open the schema, define the fields, DB type and length.

Do you want to extract data from the returned string and store the data into DB table or store the returned string in DB table?

Regards

Shong

[modara]

Hi shong,

i can create a mysql table but need to know what column name i should give

and how the structude would be in talend so i can map column to mysql to send data from https://calendarific.com

I just want to take data from https://calendarific.com and import to table and column into mysql

iso please help me column maping for XML data

thanks

[modara]

I just want to take each each column data from xml to mysql in a table format

[Anonymous]

It depends on what data you want to store in DB, and extract these data from the returned string, I don't know which values you want to extract! Define the schema of tExtractXMLfields and link tExtractXMLfields to tMysqlOutput, the data extracted from the returned string on tExtractXMLfields will be inserted into DB table.

[modara]

Hi shong,

I want to import all filed or data points into mysql

i dont know how to structure or what component to use for XML format to mysql import

holidays,

name,

description,

country

id

name

date

iso

datetime

year

month

day

type,

primary_type,

canonical_url,

urlid,

locations,

states

what about left side? there be statuscode, body, string, response be same as there?

on right side should give all above data points or column name for mysql?

if so then how i can do heirachy type column name like

date

iso

datetime

year

month

day

 

and in textractxmpfield there is under basic there is a place call look xml filed " " what i should put in there?

thanks

[modara]

Hello,

I think i am almost closer to convert api data to output

here is my picture attached.

in tRESTClient component

 

intExtractXMLField

intXMLMap component

 

and log file or output

 

i jsut try to take two column name and discription to log or out. here is my JSON format

"response": {

    "holidays": [

      {

        "name": "New Year's Day",

        "description": "New Year’s Day is the first day of the year, or January 1, in the Gregorian

}}

please help me where i hav eot do loop and how can i take values in column. as you seen column names are there but no values.

if you need more info please do not hesitate. i need to complete this. please help or guide me.

thankx

[Anonymous]

tRestClient return a Document type be default, you don't need tExtractXMlField component, the job looks like:

tRestClient --main--tXMLMap--main--tLogRow.