Convert DLY files to CSV using Python

Question

I have DLY files from ncdc and want to convert them to CSV files. I've tried this code from another post and get no errors, but also fails to write a CSV file. The code is reading the DLY files but is not writing the CSV files. I am a novice Python programmer so any help is appreciated.

import glob

list_of_files =glob.glob(r'F:\testfolder\*.dly', recursive = True) #create the list of file
for file_name in list_of_files:
    FI = open(file_name, 'r')
    FO = open(file_name.replace('DLY', 'CSV'), 'w') 
for line in FI:
    FO.write(line)

    FI.close()
    FO.close()

Answer 1

The DLY file appears to use a fixed width text format. The specification gives how many characters are in each field. To convert this to CSV format, the correct number of characters needs to be read out for each value and then written using a comma instead. Spaces are also removed.

This can be done as follows:

import glob
import csv
import os

# fields as given by the spec

fields = [
    ["ID", 1, 11],
    ["YEAR", 12, 15],
    ["MONTH", 16, 17],
    ["ELEMENT", 18, 21]]

offset = 22

for value in range(1, 32):
    fields.append((f"VALUE{value}", offset,     offset + 4))
    fields.append((f"MFLAG{value}", offset + 5, offset + 5))
    fields.append((f"QFLAG{value}", offset + 6, offset + 6))
    fields.append((f"SFLAG{value}", offset + 7, offset + 7))
    offset += 8

# Modify fields to use Python numbering
fields = [[var, start - 1, end] for var, start, end in fields]
fieldnames = [var for var, start, end in fields]

for dly_filename in glob.glob(r'F:\testfolder\*.dly', recursive=True): 
    path, name = os.path.split(dly_filename)
    csv_filename = os.path.join(path, f"{os.path.splitext(name)[0]}.csv")

    with open(dly_filename, newline='') as f_dly, open(csv_filename, 'w', newline='') as f_csv:
        csv = csv.writer(f_csv)
        csv.writerow(fieldnames)    # Write a header using the var names

        for line in f_dly:
            row = [line[start:end].strip() for var, start, end in fields]
            csv.writerow(row)

So the first of your example DLY files would start as:

ID,YEAR,MONTH,ELEMENT,VALUE1,MFLAG1,QFLAG1,SFLAG1,VALUE2,MFLAG2,QFLAG2,SFLAG2,VALUE3,MFLAG3,QFLAG3,SFLAG3,VALUE4,MFLAG4,QFLAG4,SFLAG4,VALUE5,MFLAG5,QFLAG5,SFLAG5,VALUE6,MFLAG6,QFLAG6,SFLAG6,VALUE7,MFLAG7,QFLAG7,SFLAG7,VALUE8,MFLAG8,QFLAG8,SFLAG8,VALUE9,MFLAG9,QFLAG9,SFLAG9,VALUE10,MFLAG10,QFLAG10,SFLAG10,VALUE11,MFLAG11,QFLAG11,SFLAG11,VALUE12,MFLAG12,QFLAG12,SFLAG12,VALUE13,MFLAG13,QFLAG13,SFLAG13,VALUE14,MFLAG14,QFLAG14,SFLAG14,VALUE15,MFLAG15,QFLAG15,SFLAG15,VALUE16,MFLAG16,QFLAG16,SFLAG16,VALUE17,MFLAG17,QFLAG17,SFLAG17,VALUE18,MFLAG18,QFLAG18,SFLAG18,VALUE19,MFLAG19,QFLAG19,SFLAG19,VALUE20,MFLAG20,QFLAG20,SFLAG20,VALUE21,MFLAG21,QFLAG21,SFLAG21,VALUE22,MFLAG22,QFLAG22,SFLAG22,VALUE23,MFLAG23,QFLAG23,SFLAG23,VALUE24,MFLAG24,QFLAG24,SFLAG24,VALUE25,MFLAG25,QFLAG25,SFLAG25,VALUE26,MFLAG26,QFLAG26,SFLAG26,VALUE27,MFLAG27,QFLAG27,SFLAG27,VALUE28,MFLAG28,QFLAG28,SFLAG28,VALUE29,MFLAG29,QFLAG29,SFLAG29,VALUE30,MFLAG30,QFLAG30,SFLAG30,VALUE31,MFLAG31,QFLAG31,SFLAG31
USC00011084,1926,01,SNOW,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,T,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6,0,,,6
USC00011084,1926,01,SNWD,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,0,,,6,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,,-9999,,,