Python Forum
copy content of text file with three delimiter into excel sheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
copy content of text file with three delimiter into excel sheet
#1
I have a text file n numbers of rows and columns separated by three delimiters [ , ], - .
I need to import this into excel using python.

sample data of my text file is:

AM[38070] 22220-22-0-0-0-0-1-126-0-0-1-0-0-255-0
AM[38070] 22-0-0-1-1-126-0-0-0-126-0-4095-2047-2047-1
Note there are n numbers of rows and columns i need to write all into excel file

I have tried below code but it only takes one delimiter in consideration

I am not sure what to use instead of row = data[i].split('[')

Below is my code
# mypath should be the complete path for the directory containing the input text files
mypath = raw_input("Please enter the directory path for the input files: ")

from os import listdir
from os.path import isfile, join
textfiles = [ join(mypath,f) for f in listdir(mypath) if isfile(join(mypath,f)) and '.txt' in  f]

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

import xlwt
import xlrd

style = xlwt.XFStyle()
style.num_format_str = '#,###0.00'

for textfile in textfiles:
    f = open(textfile, 'r+')
    book = xlwt.Workbook()
    ws = book.add_sheet('First Sheet')  # Add a sheet
    data = f.readlines()  # read all lines at once
    for i in range(len(data)):
        row = data[i].split('[')

        for j in range(len(row)):
            ws.write(i, j, row[j])  # Write to cell i, j

        i+=1
    book.save(textfile.replace('.txt', '.xls'))
    f.close()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy xml content from webpage and save to locally without special characters Nik1811 14 617 Mar-26-2024, 09:28 AM
Last Post: Nik1811
  Python openyxl not updating Excel file MrBean12 1 252 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 349 Feb-07-2024, 12:24 PM
Last Post: Viento
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,316 Nov-09-2023, 10:56 AM
Last Post: mg24
  Search Excel File with a list of values huzzug 4 1,148 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 756 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  How to copy work sheet data one workbook to other? sayyedkamran 2 646 Nov-03-2023, 09:10 AM
Last Post: Larz60+
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,697 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,926 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  Context-sensitive delimiter ZZTurn 9 1,394 May-16-2023, 07:31 AM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020