1

I am writing a code that converts .txt file to .xls file and I need for every 4th column of my .txt file to go into, say "Sheet 1" and every third file to go in, say "Sheet 2" and so forth. The text file has headers in the first 2 rows, and the actual data starts in the 3rd column. I am not sure how do this and any guidance or help would be very appreciated. Here is my current code:

import xlwt
import six
import itertools

def is_number():
try:
    float(s)
    return True
except ValueError:
    return False

def txt_to_xls():
    f=open('textfile.txt', 'r+')
    row_list=[]
    for row in f:
        row_list.append(row.split())
    column_list=map(list, siz.moves.zip_longest(*row_list, fillvalue=' '))
    workbook=xlwt.Workbook()
    worksheet=workbook.add_sheet('Sheet 1')

    i=0
    for column in column_list:
        for item in range(len(column)):
            value=column[item].strip()
            if is_number(value):
                worksheet.write(item, i, float(value))
            else:
                worksheet.write(item, i, value)
        i+=1
    workbook.save('test.xls')
Briana Holton
  • 83
  • 1
  • 1
  • 6
  • 2
    Please do not add the code as a photo. Please [edit] your post to include the code in text form. – Scott Craner Jun 22 '17 at 20:45
  • Also, it's helpful if you can explain why your code doesn't do what you expect. What goes wrong? – roganjosh Jun 22 '17 at 20:50
  • @ScottCraner Sorry about that, I have fixed it. – Briana Holton Jun 22 '17 at 20:56
  • @roganjosh I am very new to python and I am unsure of what exactly to do to edit the code. I have tried a few things, but have not accomplished much. I feel like it has something to do with making a for loop to iterate through the columns, but I am not sure how to do that. – Briana Holton Jun 22 '17 at 20:58

1 Answers1

1

Have you considered using Visual Basic for Applications? It's basically a language designed for manipulating excel spreadsheets. Here's some sample code that I think would accomplish what you're describing:

Sub WriteToFile()
    Dim FileNum As Integer
    Dim DataLine As String
    Dim Counter As Integer

    Counter = 0

    FileNum = FreeFile()
    Open "C:\Users\...\yourDocument.txt" For Input As #FileNum


    While Not EOF(FileNum)
        Line Input #FileNum, DataLine
        Counter = Counter + 1

        If Counter > 2 Then
            Dim splitString As Variant
            splitString = Split(DataLine, " ")
            Worksheets("Sheet1").Cells(Counter - 2, 1).Value = splitString(3)
            Worksheets("Sheet2").Cells(Counter - 2, 1).Value = splitString(2)
        End If
    Wend
End Sub

Here's a good link on how to get started with VBA in excel https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx To reformat this for exactly what you need, you can change the path to yourDocument.txt to go to your txt file, and rename Sheet1 and Sheet2 to whatever the names are of the sheets you want. Also, you can add a few more lines like them if you want to add stuff to other sheets or to different columns in the same sheets. Additionally, based on what your delimiter is, you should change the second parameter in the Split() method. I think the syntax is fairly self explanatory, but there's plenty of documentation online if you get stuck. Hope this helps! Also I based the file IO here off of this other post here Read/Parse text file line by line in VBA if you want to look into that more.

StrongmanLin
  • 11
  • 1