在将数据库记录写入csv之前从数据库记录中返回/换行 [英] Strip carriage returns / line feeds from database records before they are written to csv

问题描述

我有以下代码从数据库读取行并将匹配结果写入csv文件。我遇到的问题是在不同行的某些字段中偶尔会有回车符/换行符导致csv文件无法使用，因为存在虚假行。



例如，下面是SQL数据中有回车符/换行符及其对文件的影响时会发生什么的示例。 ...混淆文件的示例内容：



field1 | field2 | field3 | field4 | field5

value 1 | value 2 | value 3 |值4 |值5

值1 |值2 |值3 |值4 |值5

值1 |值2 | val

ue 3 | value 4 | value 5

value 1 | value 2 | value 3 | va

lue 4 | value 5





这是将SQL查询的结果写入输出文件的代码。我想要做的是剥离任何有回车/换行的结果。

I have the following code that reads lines from a database and writes the matching results to a csv file. The problem I am running into is there are occasionally carriage returns / line feeds in some of the fields in different rows which cause the csv file to be unusable because there are bogus rows.

For example, here is a sample of what happens when there are carriage returns / line feeds in the SQL data and the affect it has on the file. ... Sample content of messed up file:

field1|field2|field3|field4|field5
value 1|value 2|value 3|value 4|value 5
value 1|value 2|value 3|value 4|value 5
value 1|value 2|val
ue 3|value 4|value 5
value 1|value 2|value 3|va
lue 4|value 5


Here is the code that writes the results of a SQL query to the output file. What I am trying to do is strip any results that have carriage returns / line feeds.

<pre lang="vb">'''
    While loop to read each row.  if compares row[2] (updated) against the last record processed
    '''
    latest = params #Declare 'latest' variable for consumption by while loop
    while row:
        if row[2] > latest:
            latest = row[2]
        logger.debug("[%s] - Writing row %s", correlationId, row)
        writer.writerow(row)
        row = cursor.fetchone()

    logger.info("[%s] - last letter date %s " % (correlationId, lastProcessed))
    lastProcessedLog = open(LAST_PROCESSED_LOGFILE , 'wt')
    lastProcessedString = str(latest)
    lastProcessedString = lastProcessedString[0:19]
    lastProcessedLog.write(lastProcessedString)
    lastProcessedLog.close()

    conn.close()
    ofile.close()
    logger.info("[%s] - Copying %s to root for loadBackflow as %s", correlationId, writeFile, outfile)
    shutil.copyfile(writeFile, outfile)
    logger.info("[%s] - Moving %s to completion folder %s", correlationId, writeFile, completionFolder)
    shutil.move(writeFile, completionFolder)

我尝试更改writer.write（row）行以包含替换但我收到错误。同样，我在尝试使用row = row.replace（\\\\ n，）时遇到错误...我已经粘贴了我的尝试和下面相应的错误。



非常感谢有关如何在将SQL查询结果读入数据文件时删除回车符/换行符的任何见解。



提前致谢！ :)

I have tried changing the writer.write(row) line to include a replace but I get an error. Similarly, I get errors when trying to use replace with row = row.replace("\r\n", "") ... I have pasted my attempts and the corresponding errors below.

Any insights on how I can strip carriage returns / line feeds at the time they are being read from the SQL query results into the data file are much appreciated.

Thanks in advance! :)

# Attempt1:
writer.writerow(row).replace("\r\n", "")
# Error:
Unexpected error: 'NoneType' object has no attribute 'replace'

# Attempt2:
row = row.replace("\r\n", "")
#Error:
Unexpected error: 'tuple' object has no attribute 'replace'

#Attempt3:
row = row.replace("\r", "")
row = row.replace("\n", "")
#Error:
Unexpected error: 'tuple' object has no attribute 'replace'

#Attempt 4:
        '''
        While loop to read each row.  if compares row[2] (updated) against the last record processed
        '''
        latest = params #Declare 'latest' variable for consumption by while loop
        def preprocess_item(item):
            if isinstance(item, str):
                return item.replace("\r\n", "")
                #return item.replace("\n", "").replace("\r", "")
            return item

        def preprocess_row(row):
            return tuple(preprocess_item(item) for item in row)

        while row:
            if row[2] > latest:
                #row = cursor.fetchone()
                
                latest = row[2]
            logger.debug("[%s] - Writing row %s", correlationId, row)
            row = cursor.fetchone()
            row = preprocess_row(row)
            writer.writerow(row)

推荐答案

尝试使用

Row = row.replace（/ [\ n \\\ r] / g，''）;

Try using
Row= row.replace(/[\n\r]/g, '');

这篇关于在将数据库记录写入csv之前从数据库记录中返回/换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！