python create lists from multiple text files and create csv file from those lists

Question

running into a stump, i have two legacy text files that i want to pull data from in order to create one csv file.

to keep things short here is my code exactly as it sits on my screen:

import csv, itertools

list1 = []
with open('D:/py_files/legacy_temp/REPORT_1.TXT', 'rb') as tf:
    for line in tf:
        if len(line) > 2:
            if line[17].isdigit():
                acctnum = str(line[16:31])
                custname = str(line[39:58])
                currbal = str(line[84:96])
                diffbal = str(line[102:114])
                list1.append(acctnum + '|' + custname + '|' + currbal + '|' + diffbal)

list2 = []
with open('D:/py_files/legacy_temp/REPORT_2.TXT', 'rb') as tf2:
    for line in tf2:
        if line[0].isdigit():
            acctnum = str(line[1:12])
            ourbal = str(line[80:90])
            alldnum = str(line[123:131])
            clntnum = str(line[132:152])
            list2.append(acctnum + '|' + ourbal + '|' + alldnum + '|' + clntnum)

the code below is just my scrapbook, things i was trying. i can create the csv file, but it either writes as one long continuous row, or it writes while appending a '|' after each char ie: a|b|c|d| etc...

#mlist = []
#if len(list1) == len(list2):
#   for i, j in map(None,list1,list2):
#       print i + '|' + j
def f1():
    clist = []
    outfile = csv.writer(open('D:/py_files/legacy_temp_/report_diff.csv', 'wb'))
    if len(list1) == len(list2):
        for i, j in map(None,list1,list2):
            clist.append(str(i + '|' + j + '\n'))
        outfile.writerow(clist)
        print '\n'.join(clist)

def f2():
    for x,y in zip(list1,list2):
        print list1+list2
def f3():
    output = list(itertools.chain(list1,list2))
    print '\n'.join(output)

two things, a) am i going about this the right way (opening both text files separately), and b)if i am, how can i write a csv file that will give me the following rows:

acctnum|custname|currbal|diffbal|acctnum|ourbal|alldnum|clntnum

with each element within the | above, in a separate cell..

PS. i only used pipe as a delimiter because the balances had commas in them. i do not need to use pipes as i can replace the commas in the balances.

all help is greatly appreciated, thanks

micebrain · Accepted Answer · 2014-10-29 19:33:09Z

1

Actually the original function will work with the second function with a small modification:

def f2():
    for x,y in zip(list1,list2):
        print list1+list2 <-- this should be print x+y

answered Oct 29, 2014 at 19:33

micebrain

5762 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chunpoon · Accepted Answer · 2014-10-21 06:36:14Z

0

should put the stuff you want to append in brackets.

list1.append([acctnum + '|' + custname + '|' + currbal + '|' + diffbal])

you could also do:

list1.append(['|'.join([acctnum, custname, currbal, diffbal])])

you will then get a bunch of lists within list1, which represent a row.

answered Oct 21, 2014 at 6:36

chunpoon

97010 silver badges17 bronze badges

Comments

jaredobr · Accepted Answer · 2014-10-21 09:04:39Z

If you want the quickest, easiest way to convert the data from txt to csv you could do something like the following:

import csv

header = ('acctnum,custname,currbal,diffbal,acctnum,ourbal,alldnum,clntnum\n')
with open('out.csv', 'wb') as fout:
    fout.write(header)

    with open('blah1.txt', 'rU') as fin1:
        fin1.next()

        for row in fin1:
            fout.write(row.replace('|',','))

    fout.write('\n')

    with open('blah2.txt', 'rU') as fin2:
        fin2.next()

        for row in fin2:
            fout.write(row.replace('|',','))

This will take your two files, and merge them into one single CSV, also while dealing with the pipe delimiter. If you already removed your pipes, then just get rid of the ".replace('|',',') bit so you are only passing "row" to the csv writer. You can then remove whatever additional columns you didn't want in excel or something.

jes516 · Accepted Answer · 2014-10-22 05:20:18Z

thank you, it was improper indentation.

import csv

path = 'D:/py_files/legacy_temp/'

outfile1 = csv.writer(open(path + 'REPORT_1.csv', 'wb'))
with open(path + 'REPORT_1.TXT', 'rb') as f1:
    for line in f1:
        lne = line.replace('\x0c','').replace('\x1c','').replace('\r','').replace('\n','')
        if len(lne) > 2:
            if lne[17].isdigit():
                list1 = []
                list1.append(str(lne[16:31].replace('-','').strip()))
                list1.append(str(lne[39:58].strip()))
                list1.append(str(lne[84:96].strip()))
                list1.append(str(lne[102:114].strip()))
                outfile1.writerow(list1)

outfile2 = csv.writer(open(path + 'REPORT_2.csv', 'wb'))
with open(path + 'REPORT_2.TXT', 'rb') as f2:
    for line in f2:
        lne = line.replace('\x0c','').replace('\x1c','').replace('\r','').replace('\n','')
        if len(lne) > 1:
            if lne[0].isdigit():
                list2 = []
                list2.append(str(lne[1:12].strip()))
                list2.append(str(lne[80:90].strip()))
                list2.append(str(lne[123:131].strip()))
                list2.append(str(lne[132:152].strip()))
                outfile2.writerow(list2)

now that im looking at the csv files, i rather just merge both lists and create a one csv file. they will always be the same length. if they are not, then something wrong with the reports. i will start working on this..

EDIT: here is it merged...

import csv

path = 'D:/py_files/legacy_temp/'

with open(path + 'REPORT_MERGE.csv', 'wb') as csvf1:
    writer = csv.writer(csvf1)

    lst1 = []
    with open(path + 'REPORT_1.TXT', 'rb') as f1:
        for line in f1:
            lne = line.replace('\x0c','').replace('\x1c','').replace('\r','').replace('\n','')
            if len(lne) > 2:
                if lne[17].isdigit():
                    list1 = []
                    list1.append(str(lne[16:31].replace('-','').strip()))
                    list1.append(str(lne[39:58].strip()))
                    list1.append(str(lne[84:96].strip()))
                    list1.append(str(lne[102:114].strip()))
                    lst1.append(list1)

                    #creates ['x', 'x', 'x', 'x']

    lst2 = []
    with open(path + 'REPORT_2.TXT', 'rb') as f2:
        for line in f2:
            lne = line.replace('\x0c','').replace('\x1c','').replace('\r','').replace('\n','')
            if len(lne) > 1:
                if lne[0].isdigit():
                    list2 = []
                    list2.append(str(lne[1:12].strip()))
                    list2.append(str(lne[80:90].strip()))
                    list2.append(str(lne[123:131].strip()))
                    list2.append(str(lne[132:152].strip()))
                    lst2.append(list2)

                    #creates ['y', 'y', 'y', 'y']

    for x, y in zip(lst1,lst2):
        writer.writerow(x + y)
        #creates ['x', 'x', 'x', 'x', 'y', 'y', 'y', 'y']
        #each element in merged list writes to its own cell *****

Collectives™ on Stack Overflow

python create lists from multiple text files and create csv file from those lists

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related