[Python] How to Read Informix UNL File in Python Dataframe or CSV Containing Multi-lined Values

Good Day,

I've been struggling to read the data exported from Informix DB into my Python code. I have tried both the CSV and pandas modules, but neither of them worked for me.

The data contains multiple lines in a single cell. The problem is, that Informix DB does not support quote characters,but rather puts the escape character (backslash: ) to escape the newlines. So the data becomes like this:

ID,IS_ACTIVE,COMMENTS
161,Y,This contains\, comma and\
newline here



I have tried the following code but didn't work.

import pandas as pd
import csv

df = pd.read_csv('1.unl', sep=',', escapechar='\\', quoting=csv.QUOTE_NONE, lineterminator='\n')
df


I have also tried to pro-process the file to make it proper CSV with qutoechar (") but couldn't make it work.

import re

def parse_csv(text, delimiter=",", escapechar='\\'):
# print(text)
# text = text.replace(escapechar + 'n', '\n') # Handle escaped newlines
# print(text)
# pattern = re.compile(r'(?<!' + re.escape(escapechar) + ')\n')
newline_pattern = fr'(?<!{re.escape(escapechar)})(?:{re.escape('\n')})'
pattern = fr'(?<!{re.escape(escapechar)})(?:{re.escape(delimiter)})'

# print(pattern)
lines = re.split(newline_pattern, text) # Split on non-escaped newlines
# print(lines)

def parse_csv_line(line):
pattern = rf'({escapechar}.|[^{delimiter}])+'
pattern = fr'(?<!{re.escape(escapechar)})(?:{re.escape(delimiter)})'
matches = re.findall(pattern, line)

fields = []
field = ""
escaped = False

for char in line:
if escaped:
field += char
escaped = False
elif char == escapechar:
escaped = True
elif char == delimiter:
fields.append(field)
field = ""
else:
field += char

fields.append(field) # Add last field
return fields

return [parse_csv_line(line) for line in lines]

# Example usage:
csv_text = open('1.unl').read()
parsed_data = parse_csv(csv_text, ',')
# parsed_data = open('1.unl').read()
for row in parsed_data:
print(row)


Any heads-up/ideas are really appreciated...!

Continue reading...