CODEWITHSUNDEEP
python
Tom
Tom

Reputation: 89

Change a fixed-width text file to delimited

I have a file that has 27 fields and is fixed-width. I would like to be able to run a script on it, that would go through and add a comma or something at the field end spots so that I can then easily import it into Excel without having to manually set the lengths each time.

I am able to read the file into Python, but am not sure how I would add the commas.

Field info:

Start Position: 1, 21, 41, 61, 81, 86, 89, 95, 99, 103, 183, 193, 243, 293, 343, 393, 423, 425, 435, 436, 437, 438, 440, 442, 445, 447, 450

Length: 20, 20, 20, 20, 5, 3, 6, 4, 4, 80, 10, 50, 50, 50, 50, 30, 2, 10, 1, 1, 1, 2, 2, 3, 2, 3, 1

Upvotes: 3

Views: 4775

Answers (3)

pbreach
pbreach

Reputation: 16997

Alternatively you could use the pandas library for this.

import pandas as pd

length = [20, 20, 20, 20, 5, 3, 6, 4, 4, 80, 10, 50, 50, 50, 50, 30, 2, 10, 1, 1, 1, 2, 2, 3, 2, 3, 1]

pd.read_fwf("input file.txt", widths=length).to_csv("output.csv")

You'll need to use either the colspec or widths keyword arguments in pd.read_fwf to specify how the column widths are laid out. More on that can be found here.

Upvotes: 4

C Panda
C Panda

Reputation: 3405

You could do as follows,

from itertools import chain
import csv

n = 27 # num of fields
with open('in.csv') as fr, open('out.csv', 'w') as fw:
    reader = csv.reader(fr)
    writer = csv.writer(fw)
    for row in reader:
        row_ = chain(row, [None] * (n - len(row))
        writer.writerow(list(row_))

Upvotes: 4

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798814

Use struct to read in the fixed-format file and csv to generate the CSV file.

Upvotes: 2

Related Questions