0

I have a system that creates csv files nightly and sends them off to a SMTP server.

Customer has requested that we make a separate text file with differently formatted timestamps, this time to ISO format, and send it to a separate SMTP server. Due to some poor programming, constraints and prior technical debt it is much easier for me to let the system go on with it's normal process, but read the created CSV, modify the time stamp columns to the right format, and create the new csv like that and then send it off.

I almost have it working but for some reason my function is adding an additional empty row in between every row.

My function:

def convertFileToIsoFormat(read_filePath, write_filePath):
    import csv
    import datetime
    from __future__ import with_statement
    with open(read_filePath) as csvfile:
        reader = csv.reader(csvfile, delimiter='\t')
        for row in reader:
            modifiedRow = row
            columnsToModify = [4,7,11]
            for column in columnsToModify:
                modifiedRow[column] = datetime.datetime.strptime(modifiedRow[column], '%Y-%m-%d %H:%M:%S').isoformat()
            # Special column with different format
            modifiedRow[22] = datetime.datetime.strptime(modifiedRow[21], '%m/%d/%Y %H:%M:%S %p').isoformat()
            with open(write_filePath, 'a') as newcsv:
                writer = csv.writer(newcsv, delimiter='\t')
                writer.writewrow(modifiedRow)

This works in making the file, and in Notepad on this Windows Server 2012 the file looks right, but in my newer Windows 10 notepad I see the extra rows, and in NotePad++ on either computer I see the extra rows, so I suspect it has something to do with what the linebreak character is supposed to be for the os but I don't know how to work with that here.

Using python (technically jython) 2.5 for this. Any thoughts on what I am missing?

Brian Karabinchak
  • 207
  • 1
  • 9
  • 1
    Look at the file in Notepad++ and what the line ending chars are—turn on “hidden” characters, or something like that. The lineterminator option, https://docs.python.org/2.7/library/csv.html, for the writer defaults to ‘\r\n’. Do you see that sequence, but doubled up, or do you see some other sequence? Maybe try fiddling with the option? Also, I think it’s weird to be opening the output file for appending on every iteration of the reader. I’d recommend opening it once and creating the writer alongside the reader. Can 2.5 do multiple with-open statements? If not, just manually close the output. – Zach Young Jan 04 '23 at 03:38
  • 1
    Yea I realized I probably should not be doing my second `with` inside of the loop. You're right on that. In Notepad++ at the end of a line with data I see `CR` and then on the completely blank line two characters `CR` and then `LF`. I'm thinking perhaps it is the issue of my opening the file to write to every time. Thanks for the advice. – Brian Karabinchak Jan 04 '23 at 13:22
  • 1
    I'd like to believe that changing the structure of your open-for-writing will fix it But... I've got a feeling this runs deeper and reflects how Python and the csv module interpret line endings. See my answer for my best guess as to what might be wrong, and what might fix it. – Zach Young Jan 04 '23 at 18:43
  • 1
    Per the docs, use `newline=''` with csv.reader/csv.writer on Python3 and `wb` or `rb` with Python 2. See the duplicate. – Mark Tolonen Jan 04 '23 at 19:44
  • @MarkTolonen Thanks for the link, changing it to `wb` instead of `a` fixed the issue and then I didn't have to mess with the line terminator. Appreciated! – Brian Karabinchak Jan 05 '23 at 15:27

0 Answers0