Fixed width txt to csv

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • talpol00
    New Member
    • Mar 2012
    • 1

    #1

    Fixed width txt to csv

    Dear users and helpers,
    I searched online, but could not find the answer to my question. I need convert a txt file to a csv. I have figured out how to do this with delimeters, however, the txt file does not have any delimeters or headers so I must set fixed width numbers. The file has millions of records. The widths for the columns are 10, 60, 60, 60, 30, 2, 3, 6, 9, 5, 6, 12, 12, 5, 3, 12, 12, 5, 3.

    My two challenges are:
    1. To convert the file to csv with the fixed widths listed above.
    2. To insert headers.

    The data looks something like this:
    0000000626ISOCK E BBBB ZZZZZ TW DARTMOUTH 10 FDSAF DR DARTMOUTH CASN 7H44DR SAAB -11.111111 22.2222222 000 -33.333333 44.4444444 000
    0000000627ISOCK E FFFF TTTTT TW HALIFAX 3367 FDSAF RD HALIFAX CASN 8C5ASE SAAB -55.555555 66.6666666 000 -77.777777 88.8888888 000
    0000000628ISOCK E RE CHARLOTTETOWN 449 UYRNT ECSARW RD CHARLOTTETOWN CAPE CSE8HR SAAB -99.999999 11.1111111 000 -22.222222 33.3333333 000

    Again, all I've been able to is convert the file to csv but not in the correct format using this code:

    Code:
    import csv
    rf = open(r'C:\Users\...New Folder\practice.txt', 'r') #input file handle
    wf = open(r'C:\Users\...New Folder\Book1.csv','w') #output file handle
    writer = csv.writer(wf)
    
    for row in rf.readlines():
        writer.writerow(row.split())
    rf.close() # close input file handle
    wf.close() # close output file handle
    Any help would be greatly appreciated.
  • bvdet
    Recognized Expert Specialist
    • Oct 2006
    • 2851

    #2
    The field widths do not appear to match the data. For example, the sum of the field widths is 315 but line 1 of your data is 131 characters including spaces. Anyway, I would think something like the following would work:
    Code:
    fieldwidths = [10, 60, 60, 60, 30, 2, 3, 6, 9, 5, 6, 12, 12, 5, 3, 12, 12, 5, 3]
    data = """0000000626ISOCKE BBBB ZZZZZ TW DARTMOUTH 10 FDSAF DR DARTMOUTH CASN 7H44DR SAAB -11.111111 22.2222222 000 -33.333333 44.4444444 000
    0000000627ISOCKE FFFF TTTTT TW HALIFAX 3367 FDSAF RD HALIFAX CASN 8C5ASE SAAB -55.555555 66.6666666 000 -77.777777 88.8888888 000
    0000000628ISOCKE RE CHARLOTTETOWN 449 UYRNT ECSARW RD CHARLOTTETOWN CAPE CSE8HR SAAB -99.999999 11.1111111 000 -22.222222 33.3333333 000"""
    
    outputList = []
    
    for line in data.split("\n"):
        idx = 0
        lineList = []
        for i in fieldwidths:
            upper = idx+i
            lineList.append(line[idx:upper])
            idx = upper
        outputList.append(lineList)

    Comment

    • dwblas
      Recognized Expert Contributor
      • May 2008
      • 626

      #3
      I don't think you can do that with the csv module, but will have to add the comma yourself. Note that there is a discrepancy between the lengths of the output format and the fields of data.
      Code:
      data=["0000000626ISOCKE BBBB ZZZZZ TW DARTMOUTH 10 FDSAF DR DARTMOUTH CASN 7H44DR SAAB -11.111111 22.2222222 000 -33.333333 44.4444444 000",
       "0000000627ISOCKE FFFF TTTTT TW HALIFAX 3367 FDSAF RD HALIFAX CASN 8C5ASE SAAB -55.555555 66.6666666 000 -77.777777 88.8888888 000",
       "0000000628ISOCKE RE CHARLOTTETOWN 449 UYRNT ECSARW RD CHARLOTTETOWN CAPE CSE8HR SAAB -99.999999 11.1111111 000 -22.222222 33.3333333 000"]
      
      fieldwidths = [10, 60, 60, 60, 30, 2, 3, 6, 9, 5, 6, 12, 12, 5, 3, 12, 12, 5, 3]output_format = ["%"+str(x)+"s," for x in fieldwidths]
      ## strip the comma on the last element
      output_format[-1] = output_format[-1][:-1]
      print output_format
      
      fp=open("./test1.csv", "w")
      for rec in data:
          data_list = rec.split()
          print len(data_list), "-->", len(fieldwidths)
          for offset in range(len(data_list)):
              fp.write(output_format[offset] % data_list[offset])
          fp.write("\n")
      fp.close()

      Comment

      Working...