I have more than 5000 text files, each with multiple lines of data. I want to merge all of them into one MS Excel file so that the first line of each file is entered into the first column and the remaining lines of each file are entered into the second column.
How can I do this using python?
Here's an example for you:
import csv
filename = "demofile.txt"
#Read the file into a list
with open(filename) as f:
content = f.readlines()
#strip out any spaces and new-line characters from the end of each row
content = [x.rstrip() for x in content]
#open a CSV file for writing
with open('output.csv', 'w', newline='') as csvfile:
#Setup the CSV File
csvwriter= csv.writer(csvfile)
#Label the Columns
csvwriter.writerow(['Column 1 Heading' , 'Column 2 Heading'])
#Write the Tricky bit where you transpose the first row
csvwriter.writerow([content[0],content[1]])
#Write the rest
for row in content[2:]:
csvwriter.writerow(['',content[1]])
demofile.txt
bob
1
2
3
4
5
6
gives
Column 1 Heading,Column 2 Heading
bob,1
,1
,1
,1
,1
,1
