Python CSV to Excel: Avoiding Scientific Notation Rounding – Python Tutorial

When you convert a csv file to excel using python pandas, you may find the numerical text displayed with scientific notation rounding.

For example:

In this tutorial, we will introduce how to make these numerical texts display correctly.

CSV to Excel

It is easy to convert a csv file to excel, you can use pandas library.

Convert CSV to Excel in Python Pandas

However, it is hard to make you control the data type of each column.

Convert CSV to Excel without Pandas

To manage the data type of each column when converting csv file to excel, we will not use pandas.

By this way, we can avoid scientific notation rounding.

For example:

import os

def get_lines(file):
    data = []
    with open(file, "r", encoding = "utf-8") as f:
        for line in f:
            data.append(line)
    return data

csv_file = "test.csv"
csv_data_lines = get_lines(csv_file)
rows = {}
header = []

for i in range(len(csv_data_lines)):
    line = csv_data_lines[i]
    line = line.strip()
    dx = line.split("\t")
    if i == 0:
        for h in dx:
            rows[h] = []
            header.append(h)
    else:
        for j in range(len(header)):
            h = header[j]
            rows[h].append(dx[j])

excel_file = "test.xlsx"
save_data_to_excel(excel_name=excel_file, sheet_name="data", data = rows)

In this example, save_data_to_excel() function will be found in this tutorial:

Add Hyperlink to Excel Using Python Pandas: A Step Guide

You can change the data type of each column easily in this code:

rows[h].append(dx[j])

Run this example, we will find the numerical text is displayed correctly.