[Tut] Convert CSV to Excel xlsx in Python

Convert CSV to Excel xlsx in Python

<div><div class="kk-star-ratings kksr-valign-top kksr-align-left " data-payload="{&quot;align&quot;:&quot;left&quot;,&quot;id&quot;:&quot;424181&quot;,&quot;slug&quot;:&quot;default&quot;,&quot;valign&quot;:&quot;top&quot;,&quot;reference&quot;:&quot;auto&quot;,&quot;count&quot;:&quot;1&quot;,&quot;readonly&quot;:&quot;&quot;,&quot;score&quot;:&quot;5&quot;,&quot;best&quot;:&quot;5&quot;,&quot;gap&quot;:&quot;5&quot;,&quot;greet&quot;:&quot;Rate this post&quot;,&quot;legend&quot;:&quot;5\/5 - (1 vote)&quot;,&quot;size&quot;:&quot;24&quot;,&quot;width&quot;:&quot;142.5&quot;,&quot;_legend&quot;:&quot;{score}\/{best} - ({count} {votes})&quot;}">
<div class="kksr-stars">
<div class="kksr-stars-inactive">
<div class="kksr-star" data-star="1" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="2" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="3" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="4" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="5" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
<div class="kksr-stars-active" style="width: 142.5px;">
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
</div>
<div class="kksr-legend"> 5/5 – (1 vote) </div>
</div>
<h2>Problem Formulation</h2>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Challenge</strong>: Given a CSV file. How to convert it to an excel file in Python?</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="576" src="https://blog.finxter.com/wp-content/uploads/2022/06/csv_to_excel-1024x576.jpg" alt="csv to excel in Python" class="wp-image-424196" srcset="https://blog.finxter.com/wp-content/uploads/2022/06/csv_to_excel-1024x576.jpg 1024w, https://blog.finxter.com/wp-content/uplo...00x169.jpg 300w, https://blog.finxter.com/wp-content/uplo...68x432.jpg 768w, https://blog.finxter.com/wp-content/uplo..._excel.jpg 1280w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>We create a folder with two files, the file <code>csv_to_excel.py</code> and <code>my_file.csv</code>. We want to convert the CSV file to an excel file so that after running the script <code>csv_to_excel.py</code>, we obtain the third file <code>my_file.csv</code> in our folder like so:</p>
<div class="wp-block-image">
<figure class="aligncenter size-full"><img loading="lazy" width="626" height="124" src="https://blog.finxter.com/wp-content/uploads/2022/06/image-127.png" alt="" class="wp-image-424197" srcset="https://blog.finxter.com/wp-content/uploads/2022/06/image-127.png 626w, https://blog.finxter.com/wp-content/uplo...300x59.png 300w" sizes="(max-width: 626px) 100vw, 626px" /></figure>
</div>
<p>All methods discussed in this tutorial show different code snippets to put into <code>csv_to_excel.py</code> so that it converts the CSV to XLSX in Python.</p>
<h2>Method 1: 5 Easy Steps in Pandas</h2>
<p>The most pythonic way to convert a <code>.csv</code> to an <code>.xlsx</code> (Excel) in Python is to use the <a href="https://blog.finxter.com/pandas-quickstart/" data-type="post" data-id="16511" target="_blank" rel="noreferrer noopener">Pandas</a> library.</p>
<ol>
<li>Install the <code>pandas</code> library with <code><a href="https://blog.finxter.com/how-to-install-pandas-in-python/" data-type="post" data-id="35926" target="_blank" rel="noreferrer noopener">pip install pandas</a></code></li>
<li>Install the <code>openpyxl</code> library that is used internally by pandas with <code><a href="https://blog.finxter.com/fixed-modulenotfounderror-no-module-named-openpyxl/" data-type="post" data-id="413754" target="_blank" rel="noreferrer noopener">pip install openpyxl</a></code></li>
<li>Import the <code>pandas</code> libray with <code>import pandas as pd</code></li>
<li>Read the <a href="https://blog.finxter.com/read-and-write-flat-files-with-pandas/" data-type="post" data-id="62847" target="_blank" rel="noreferrer noopener">CSV file into a DataFrame</a> <code>df</code> by using the expression <code>df = pd.read_csv('my_file.csv')</code></li>
<li>Store the <a href="https://blog.finxter.com/pandas-dataframe-to_excel-method/" data-type="post" data-id="344278" target="_blank" rel="noreferrer noopener">DataFrame in an Excel</a> file by calling <code>df.to_excel('my_file.xlsx', index=None, header=True)</code></li>
</ol>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd df = pd.read_csv('my_file.csv')
df.to_excel('my_file.xlsx', index=None, header=True)
</pre>
<p>Note that there are many ways to customize the <code>to_excel()</code> function in case </p>
<ul>
<li>you don’t need a header line, </li>
<li>you want to fix the first line in the Excel file, </li>
<li>you want to format the cells as numbers instead of strings, or </li>
<li>you have an index column in the original CSV and want to consider it in the Excel file too.</li>
</ul>
<p>If you want to do any of those, feel free to read our full guide on the Finxter blog here:</p>
<p class="has-base-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f30d.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Tutorial</strong>: <a rel="noreferrer noopener" href="https://blog.finxter.com/pd-to_excel-saving-data-to-excel/" data-type="URL" data-id="https://blog.finxter.com/pd-to_excel-saving-data-to-excel/" target="_blank">Pandas <code>DataFrame.to_excel()</code> – An Unofficial Guide to Saving Data to Excel</a></p>
<p>Also, we’ve recorded a video on the ins and outs of this method here:</p>
<figure class="wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio">
<div class="wp-block-embed__wrapper">
<iframe loading="lazy" title="pd.to_excel() – An Unofficial Guide to Saving Data to Excel" width="780" height="585" src="https://www.youtube.com/embed/y9vWjGpas2g?feature=oembed" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</div>
</figure>
<p>Let’s have a look at an alternative to converting a CSV to an Excel file in Python:</p>
<h2>Method 2: Modules csv and openpyxl</h2>
<p>To convert a CSV to an Excel file, you can also use the following approach:</p>
<ul>
<li>Import the <code>csv</code> module</li>
<li>Import the <code>openpyxl</code> module</li>
<li>Read the CSV file into a <a href="https://blog.finxter.com/python-list-of-lists/" data-type="post" data-id="7890">list of</a><a href="https://blog.finxter.com/python-list-of-lists/" data-type="post" data-id="7890" target="_blank" rel="noreferrer noopener"> </a><a href="https://blog.finxter.com/python-list-of-lists/" data-type="post" data-id="7890">lists</a>, one inner list per row, by using the <code>csv.reader()</code> function</li>
<li>Write the list of lists to the Excel file by using the workbook representation of the <code>openpyxl</code> library.</li>
<li>Get the active worksheet by calling <code>workbook.active</code></li>
<li>Write to the worksheet by calling <code>worksheet.append(row)</code> and append one <a href="https://blog.finxter.com/python-lists/" data-type="post" data-id="7332" target="_blank" rel="noreferrer noopener">list</a> of values, one value per cell.</li>
</ul>
<p>The following function converts a given CSV to an Excel file:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import csv
import openpyxl def csv_to_excel(csv_filename, excel_filename): # Read CSV file csv_data = [] with open(csv_filename) as f: csv_data = [row for row in csv.reader(f)] # Write to Excel file workbook = openpyxl.workbook.Workbook() worksheet = workbook.active for row in csv_data: worksheet.append(row) workbook.save(excel_filename) if __name__ == "__main__": csv_to_excel("my_file.csv", "my_file.xlsx")</pre>
<p>This is a bit more fine-granular approach and it allows you to modify each row in the code or even write additional details into the Excel worksheet.</p>
<h2>Where to Go From Here?</h2>
<p>Enough theory. Let’s get some practice!</p>
<p>Coders get paid six figures and more because they can solve problems more effectively using machine intelligence and automation. </p>
<p>To become more successful in coding, solve more real problems for real people. That’s how you polish the skills you really need in practice. After all, what’s the use of learning theory that nobody ever needs?</p>
<p><strong>You build high-value coding skills by working on practical coding projects!</strong></p>
<p>Do you want to stop learning with toy projects and focus on practical code projects that earn you money and solve real problems for people?</p>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f680.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /> If your answer is <strong><em>YES!</em></strong>, consider becoming a <a rel="noreferrer noopener" href="https://blog.finxter.com/become-python-freelancer-course/" data-type="page" data-id="2072" target="_blank">Python freelance developer</a>! It’s the best way of approaching the task of improving your Python skills—even if you are a complete beginner.</p>
<p>If you just want to learn about the freelancing opportunity, feel free to watch my free webinar <a rel="noreferrer noopener" href="https://blog.finxter.com/webinar-freelancer/" target="_blank">“How to Build Your High-Income Skill Python”</a> and learn how I grew my coding business online and how you can, too—from the comfort of your own home.</p>
<p><a href="https://blog.finxter.com/webinar-freelancer/" target="_blank" rel="noreferrer noopener">Join the free webinar now!</a></p>
</div>


https://www.sickgaming.net/blog/2022/06/...in-python/