Handling JSON Data in CSV Files During Pipeline Execution in Fabric

[pafnuty]

I need to upload data to S3 in CSV format, which I ingest from Fabric DWH. Produced CSV file contains JSON data within a field.

Occasionally, the pipeline copy activity breaks the data in this scenario. Each JSON field and JSON key is parsed for a not corresponding column.

Changing the delimiter in settings don't solve the issue.

Any ideas how it can be solved?

[v-nuoc-msft · Accepted solution]

Hi @pafnuty 

There is a problem with data interruption during the pipeline replication process which may be related to the data format. You can consider doing some pre-processing on json data. For example,

Base64 encoding. The JSON data is encoded in base64 format to ensure that it does not interfere with the CSV structure.

import base64
import csv

# Sample JSON data
json_data = '{"key1": "value1", "key2": "value2"}'

# Encode JSON data in base64
encoded_json = base64.b64encode(json_data.encode()).decode()

# Write to CSV
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['id', 'json_data'])
    writer.writerow([1, encoded_json])

Escape special characters. Escape special characters in JSON data to prevent parsing problems.

import csv
import json

# Sample JSON data
json_data = '{"key1": "value1", "key2": "value2"}'

# Escape special characters
escaped_json = json.dumps(json.loads(json_data))

# Write to CSV
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['id', 'json_data'])
    writer.writerow([1, escaped_json])

Flatten the JSON data. Convert JSON data to a flat structure before writing it to CSV.

import csv
import json

# Sample JSON data
json_data = '{"key1": "value1", "key2": "value2"}'
json_dict = json.loads(json_data)

# Write to CSV
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['id'] + list(json_dict.keys()))
    writer.writerow([1] + list(json_dict.values()))

Hopefully this gives you some ideas. 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.