Search code examples
pythonpandascsvexport-to-csvtxt

Converting Text file to CSV with Columns


I'm trying to convert a text file to a CSV to ease my workflow for other applications. The issue I'm having is that the provided text file has an excess amount of space in the file and reads into one column when exported to a CSV using pandas. I've tried specifying column names as an argument but it doesn't work.

The text file reads like this,

85350   5211 APPLE LN               O                                                                                                                                                                     
85805   201 ORANGE ST               I                                                                                                                                                                     
84412   1313 BANANA RD              I 

It is exported into a CSV like this,

85350 5211 APPLE LN O,
85805 201 ORANGE ST I,
84412 1313 BANANA RD I

I want the exported CSV to have columns and look something similar to this, with the columns being Number Address In_Out,

Number,Address,In_Out
85350,5211 APPLE LN,O
85805,201 ORANGE ST,I
84412,1313 BANANA RD,I

Solution

  • pandas has a method to read a fixed-width text file. There are additional parameters to indicate the width of columns if its default to infer columns isn't correct, but in this case it works:

    import pandas as pd
    
    df = pd.read_fwf('input.txt', header=None)
    df.to_csv('output.csv', index=False, header=['Number','Address','In_Out'])
    

    output.csv:

    Number,Address,In_Out
    85350,5211 APPLE LN,O
    85805,201 ORANGE ST,I
    84412,1313 BANANA RD,I