I’ve been following this answer : How do I extract data from multiple text files to Excel for merging multiple .txt files to one .xlsx spreadsheet, but this answer is giving me the data in multiple worksheets with no distinct column names.
What I’m trying to do is to extract the data of 3 .txt files(each file contains data in one column without a column name) into one spreadsheet and each column should have a distinct column name(‘col1’, ‘col2’ and ‘col3’) respectively.
Any help or guidance is appreciated, thanks!
>Solution :
IIUC:
data = {}
for i, filename in enumerate(['data1.txt', 'data2.txt', 'data3.txt'], 1):
data[f"col{i}"] = pd.read_csv(filename, squeeze=True, header=None)
pd.concat(data, axis=1).to_excel('output.xlsx')
Don’t worry about number of records on each file, missing rows will be filled with NaN.