From tFileFetch (delimited file) to JSON

Six Stars

From tFileFetch (delimited file) to JSON

I'm using tFileFetch to pull a .txt file in the following format. (The column names will not be known ahead of time)

 

Employee SSN|Member Number|Patient Date of Birth|Patient Sex|Relationship to Employee|Product Code|Claim Type|Claim Number|Line Number|Adjustment Number|Adjustment Type|Bill Type Code|Principal Diagnosis|Secondary Diagnosis|Procedure Code Indicator|Procedure Code 1|Procedure Code Modifier 1|Place of Service|Filler_1|Paid Date|First Date of Service|Last Date of Service|Discharge Status|Provider Tax ID (unscrambled)|Provider Tax ID|Provider Zip Code|Filler|Servicing Provider Type|In/Out-of-Plan Indicator|Capitated Service Indicator|Paid Amount|Copay|Deductible|Coinsurance|Medicare Indicator|Account Number|Subgroup Number|Department Number|Package Number|Person ID|COB|COB Code|Employee Date of Birth|Employee Sex|Employee Coverage Type|Employee Zip Code|Billed Charges|Excluded Amount|Excluded Reason Code|Undiscounted Covered Amount|Discounted Covered Amount|Tertiary Diagnosis|PCP Indicator|PCP Tax ID|PCP Zip Code|Facility Place of Service Code|Secondary Procedure Code|Secondary Procedure Code Indicator|Provider Specialty Code|Quantity of Services|Dx Code 4|Dx Code 5|Dx Code 6|Dx Code 7|Dx Code 8|First name|Middle name|Last name|Employment Status|Primary Street|Secondary Street|City|State|Postal code|Home Phone|Work Phone|Units
333221548      |Z6097          |07251978|F|76   ||||||||||||||||07282017|07282017||||||||||||||||||Z6097                                     ||||||37209|725.68         ||||||||53719||||9         |||||||Ambthree||Patron||123 my street||NASHVILLE|43|37209|615-234-5678||
333221548      |Z6097          |07251978|F|76   ||||||||||||||||07282017|07282017||||||||||||||||||Z6097                                     ||||||37209|725.68         ||||||||53719||||9         |||||||Ambthree||Patron||123 my street||NASHVILLE|43|37209|615-234-5678||

I need a way to convert this to JSON data. Any help would be appreciated.


Accepted Solutions
Six Stars

Re: From tFileFetch (delimited file) to JSON

I ended up parsing the file with the following:

 

while ((line = br.readLine()) != null) {
	JSONObject item = new JSONObject();
	
	// gank the headers and move on
	if (headers == null) {
		headers = line.split(delimiter, KEEP_EMPTY_STRINGS);
		continue;
	}
	
	// build JSON
	String[] data = line.split(delimiter, KEEP_EMPTY_STRINGS);
	for(int i = 0; i < headers.length; i++) {
		item.put(headers[i], data[i].trim());
	}
	
	items.add(item);
}     

output_row.body = items.toString();

All Replies
Moderator

Re: From tFileFetch (delimited file) to JSON

Hi,

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

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.
Six Stars

Re: From tFileFetch (delimited file) to JSON

I ended up parsing the file with the following:

 

while ((line = br.readLine()) != null) {
	JSONObject item = new JSONObject();
	
	// gank the headers and move on
	if (headers == null) {
		headers = line.split(delimiter, KEEP_EMPTY_STRINGS);
		continue;
	}
	
	// build JSON
	String[] data = line.split(delimiter, KEEP_EMPTY_STRINGS);
	for(int i = 0; i < headers.length; i++) {
		item.put(headers[i], data[i].trim());
	}
	
	items.add(item);
}     

output_row.body = items.toString();
Highlighted
Moderator

Re: From tFileFetch (delimited file) to JSON

Hello,

Thanks for sharing your solution with us and marked your solution as accepted.

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.

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

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

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