Combine multiple CSV into one Excel

This notes takes you through all the parts of Microsoft Excel which would be helpful in analysing the data. We have meticulously designed this book and tried to keep it short and to the point. You will find only the basic description about any feature but the concepts are covered in depth. This book requires you learn through reflections. What that means is that you would be practicing and solving questions to gain a hands on understanding of the features, formulas and concepts as you learn.

Image placeholder
Figure 1: Crack the Data Science Interview

Code snippet to combine multiple CSV files

import pandas as pd
import os as os
import glob as gl


Excel_File = pd.ExcelFile("Excel_Practice_Data.xlsx")
Sheet_Name = Excel_File.sheet_names
length_of_Sheet = len(Excel_File.sheet_names)
print("List of sheets in you xlsx file :\n",Sheet_Name)

for i in range(0,length_of_Sheet):
    df = pd.read_excel("Excel_Practice_Data.xlsx", sheet_name = i)
    #making sure that number of coulmns in our output csv remais same
    df = df.iloc[:,0:3]
    df.to_csv(Sheet_Name[i]+".csv", index = False)
    print("Created :",Sheet_Name[i],".csv")
#Make sure that the file you are merging has equal number of columns and columns are in the order you want them merge.

filenames = [i for i in gl.glob('*.{}'.format('csv'))]
combined_csv = pd.concat([pd.read_csv(f) for f in filenames ])
combined_csv.to_csv( "combined_csv.csv", index=False, encoding='utf-8-sig')
Share Post
Anurag Deyasi

Anurag Deyasi has worked as a Business Analyst for Flipkart and as a Data Scientist for WorkIndia. He is now training and mentoring data science enthusiasts at Programink.

Share your opinion: