prevent CRLF in CSV export data

Question

Hi,

I have an export functionality that reads data from DB (entire records) and writes them in a .txt file, one record on a row each field being separated by ';'. the problem i am facing is that some fields contain CRLFs in it and when i write them to the file it goes to the next line thus destroying the structure of the file.

The only solution is to replace the CRLFs with a custom value, and at import replace back with CRLF. but i don't like this solution because these files are huge and the replace operation decreases performance....

Do you have any other ideas?

thank you!

Answer 1

Yes, use a CSV generator that quotes string values. For example, Python's csv module.

For example (ripped and modified from the csv docs):

import csv
def write(filename):
    spamWriter = csv.writer(open(filename, 'w'), quoting=csv.QUOTE_ALL)
    spamWriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamWriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam\nbar'])

def read(filename):
    reader = csv.reader(open(filename, "rb"))
    for row in reader:
        print row

write('eggs.csv')
read('eggs.csv')

Outputs:

['Spam', 'Spam', 'Spam', 'Spam', 'Spam', 'Baked Beans']
['Spam', 'Lovely Spam', 'Wonderful Spam\r\nbar']

Answer 2

If you have control over how the file is exported and imported, then you might want to consider using XML .. also you can use double quotes i believe to indicate literals like "," in the values.