How to Convert Excel to CSV in Python?An Excel file is a spreadsheet that is made by Excel or other programs that make spreadsheets. The type of the file is an Excel binary file format. The format of the Excel file is xlsx, and the data stored in the Excel file is a binary stream, a compound file. The information about the data and structure of an Excel workbook is contained in the streams and sub-streams. The xlsx format is defaulted after the version of Excel 2007 since it is a more open and structured format. Many types of data can be exported in formats that contain PDF, TXT, Hypertext markup language, XPS and XLSX. There are many competitors of Excel in the market, such as Google Sheets, Numbers and Apache Open Office Cal. Excel was released by Microsoft in 1985 under the name Microsoft Multiplan. On the other hand, the CSV files, also known as comma-separated values, in which the data is stored in the comma-separated format and newlines to separate records. CSV files are commonly used in spreadsheets and databases. The csv files can be used to move data between programs that are not ordinarily able to share data. CSV files give a way to share structured information, like the contents of a spreadsheet, among programs that can't necessarily be shared. Let's see how to open a csv file and an Excel file in Python: Opening Excel file in Python: Code: Output: Date Day Topic Words 0 2024-04-01 Monday Algorithm to Solve sudoku 734.0 1 2024-04-02 Tuesday Algorithm to Solve sudoku 790.0 2 2024-04-03 Wednesday Algorithm to Solve Sudoku 723.0 3 2024-04-04 Thursday Algorithm to Solve sudoku 941.0 4 2024-04-05 Friday NaN NaN 5 2024-04-06 Saturday Split Pandas DataFrame by Rows in Python 720.0 6 2024-04-07 Sunday NaN NaN 7 2024-04-08 Monday Split Pandas DataFrame by Rows in Python 510.0 8 2024-04-09 Tuesday NaN NaN 9 2024-04-10 Wednesday Python requests - SSL Certificate Verification 772.0 10 2024-04-11 Thursday Python requests - SSL Certificate Verification 701.0 11 2024-04-12 Friday Python requests - SSL Certificate Verification 518.0 12 2024-04-13 Saturday NaN NaN 13 2024-04-14 Sunday NaN NaN 14 2024-04-15 Monday How to call a Variable from Another Function i... 713.0 15 2024-04-16 Tuesday How to call a Variable from Another Function i... 700.0 16 2024-04-17 Wednesday Convert a datetime to a UTC timestamp in Pyth... 728.0 17 2024-04-18 Thursday How to access the serial (RS232) port in Pytho... 702.0 18 2024-04-19 Friday How to create a .pyc file in Python?(264) + Py... 547.0 19 2024-04-20 Saturday How to convert a string to a Python class object? 768.0 20 2024-04-21 Sunday NaN NaN 21 2024-04-22 Monday How do you convert a string to a Python class object? 538.0 22 2024-04-23 Tuesday NaN NaN 23 2024-04-24 Wednesday NaN NaN 24 2024-04-25 Thursday NaN NaN 25 2024-04-26 Friday NaN NaN 26 2024-04-27 Saturday NaN NaN 27 2024-04-28 Sunday NaN NaN 28 2024-04-29 Monday NaN NaN 29 2024-04-30 Tuesday NaN NaN 30 NaT NaN NaN NaN 31 NaT NaN NaN NaN 32 NaT NaN Total Word counts 11105.0 33 NaT NaN Total files 8.0 Explanation: In the above code, an Excel file is opened with the help of the panda's library, the read Excel method is used, and the Excel file is passed to the function. The data in the Excel file is printed. Opening CSV file in Python:The following code demonstrating how to open CSV file in Python: Code: Output: Category Message 0 ham Go until Jurong point, crazy. Available only ... 1 ham Ok lar... Joking with u oni... 2 spam Free entry in 2 a wkly comp to win FA Cup fina... 3 ham U dun say so early hor... U can already then say... 4 ham Nah I don't think he goes to usf, he lives aro... 5 spam FreeMsg Hey there darling it's been 3 week's n... 6 ham Even my brother is not like to speak with me. ... 7 ham As per your request 'Melle Melle (Oru Minnamin... 8 spam WINNER!! As a valued network customer, you have... 9 spam. Had your mobile 11 months or more? U R entitled... 10 ham, I'm gonna be home soon, and i don't want to talk... 11 spam SIX chances to win CASH! From 100 to 20,000 po... 12 spam URGENT! You have won a 1 week FREE membership ... 13 ham I've been searching for the right words to tha... 14 ham I HAVE A DATE ON SUNDAY WITH WILL!! 15 spam XXXMobileMovieClub: To use your credit, click ... 16 ham Oh k...I'm watching here:) 17 ham Eh u remember how 2 spell his name... Yes, i do... 18 ham Fine if thats the way u feel. Thats the way ... 19 spam England v Macedonia - dont miss the goals/team... Convert Excel to CSV in Python:Consider the Excel file that contains some random data. The task is to convert the Excel file into csv file. File: Daily and Weekly Report April.xlsx There are many methods to convert Excel files into csv files. 1. Converting Excel to CSV with the help of Pandas Module:Let's see how to convert the above excel file into csv using pandas module. Code: Output: Date Day Topic Words 0 2024-04-01 Monday Algorithm to Solve sudoku 734.0 1 2024-04-02 Tuesday Algorithm to Solve sudoku 790.0 2 2024-04-03 Wednesday Algorithm to Solve sudoku 723.0 3 2024-04-04 Thursday Algorithm to Solve sudoku 941.0 4 2024-04-05 Friday NaN NaN 5 2024-04-06 Saturday Split Pandas DataFrame by Rows in Python 720.0 6 2024-04-07 Sunday NaN NaN 7 2024-04-08 Monday Split Pandas DataFrame by Rows in Python 510.0 8 2024-04-09 Tuesday NaN NaN 9 2024-04-10 Wednesday Python requests - SSL Certificate Verification 772.0 10 2024-04-11 Thursday Python requests - SSL Certificate Verification 701.0 11 2024-04-12 Friday Python requests - SSL Certificate Verification 518.0 12 2024-04-13 Saturday NaN NaN 13 2024-04-14 Sunday NaN NaN 14 2024-04-15 Monday How to call a Variable from Another Function i... 713.0 Explanation: In the above code, the pandas module is imported and the Excel file is input and the Excel file is read with the help of the read_excel function by the passing the Excel file name. The excel file is converted to the csv file with the help of an object.to_csv() function, and in the function, the option index is set to none, and the header option is also set to none. The converted csv file is read, and the dataframe object is printed. 2. Converted Excel File to CSV with the help of openpyxl and CSV Modules:Code: CSV file data Explanation: In the above code, openpyxl and csv modules are imported, and the Excel file data is input in the load_workbook function. The active sheet is fetched, and with the help of the writer function, the data of the Excel file is converted to csv file with the delimiter comma ',' and each row of the Excel file is saved to the csv file. Conclusion:Excel files and csv files are both used in saving or storing the data, but in the Excel file, the data can be stored in sheets, and in a csv file, also known as comma-separated values, the data is stored separated by commas and can be used to analyze the data. There are many methods to convert Excel data into csv files. With the help of the pandas module and openpyxl module, the excel file can be converted to csv files. The openpyxl module works and is set to use the active sheet. So, this is the way the excel files can be converted to csv files. |
We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks
G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India
Home
Python
Java
JavaScript
HTML
SQL
PHP
C#
C++
DS
Aptitude
Reasoning
Selenium
DBMS
C
Andriod
Interview Q
