如何在Talend中从CSV提取JSON键值并转换数据格式

First, let's clarify your input structure (formatted for readability):

Column1,Column2,Column3
{"name":"Steve Jobs"},{"first_name":"Steve"},{"last_name":"Jobs"}
{"name":"Mark Zuckerberg"},{"first_name":"Mark"},{"last_name":"Zuckerberg"}
{"name":"Steve Jobs"},{"first_name":"Steve"},{"last_name":"Jobs"}

Your goal is to flatten this into a clean, flat CSV with direct value columns:

name,first_name,last_name
Steve Jobs,Steve,Jobs
Mark Zuckerberg,Mark,Zuckerberg
Steve Jobs,Steve,Jobs

Here's how to build the Talend job to make this happen:

1. Read the raw CSV with tFileInputDelimited #

• Drop a tFileInputDelimited component onto your job canvas and point it to your input file.
• Set the Field Separator to , (comma plus space) — this matches the spacing between your JSON fields in each row.
• Define three output columns, all of type String:
  • Column1
  • Column2
  • Column3
• Check the "Header" box if your input file includes the initial Column1,Column2,Column3 header row.

2. Parse JSON and extract values with tMap #

Connect the tFileInputDelimited component to a tMap. Inside the tMap editor:

• Create an output schema with three columns:
  • name (Type: String)
  • first_name (Type: String)
  • last_name (Type: String)
• Map each input column to the output using Talend's JSONPath function. If your input uses single quotes (like {'name':'Steve Jobs'}), first replace them with double quotes (required for valid JSON):
  • For the name column: JSONPath(Column1.replace("'", "\""), "$.name")
  • For the first_name column: JSONPath(Column2.replace("'", "\""), "$.first_name")
  • For the last_name column: JSONPath(Column3.replace("'", "\""), "$.last_name")

3. Write the flattened data to a new CSV #

Connect the tMap to a tFileOutputDelimited component:

• Configure it to write to your desired output file path.
• Set the Field Separator to , (or your preferred delimiter).
• Ensure the output schema matches the one you defined in the tMap.
• Check the "Header" box to include the name,first_name,last_name header in your output.

4. Run the job #

That's all! When you execute the job, it will parse each JSON string, extract the values, and output the clean, flat CSV you need.

Quick Troubleshooting #

• If you hit JSON parsing errors, double-check that your input strings are valid JSON. The replace("'", "\"") fix is critical if your source uses single quotes instead of standard double quotes.
• If your input has inconsistent spacing around commas, enable the "Regex Separator" option in tFileInputDelimited and use \s*,\s* as the separator — this handles any number of spaces before/after commas.

内容的提问来源于stack exchange，提问作者pavithra