I tried to merged all excel files and all of them only contain 1 column. However, the merged data was moved the next column. Does anyone know how to fix that? Here is the Python code:

import glob
import pandas as pd
file_path = "c:\merge-excel"
files = (file_path "/*.xlsx")
all_files = glob.glob(files)
merged_data = pd.concat([pd.read_excel(a) for a in all_files])
merged_data.to_excel (file_path "/Merged_files.xlsx", index=False, encoding='utf-8-sig')

file 1.xlsx:

1
2
3
4
5
6
7    

file 2.xlsx:

8
9
10
11
12
13
14
15

file 3.xlsx:

16
17
18
19
20
21
22
23

Merged_files.xlsx:

1 | 8 | 16
2
3
4
5
6
7
   9
   10
   11
   12
   13
   14
   15
        17
        18
        19
        20
        21
        22
        23

CodePudding user response：

Your issue is that the default value of the header parameter to read_excel is 0, which means it is taking the first row in each excel file and using it as a column header. So you have 3 columns 1, 8, and 16 which were the first row's value in each file. Use

pd.read_excel(a, header=None)

to work around this.