Python Convert CSV to Xlsx

Python convert csv to xlsx

Here's an example using xlsxwriter:

import os
import glob
import csv
from xlsxwriter.workbook import Workbook

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

FYI, there is also a package called openpyxl, that can read/write Excel 2007 xlsx/xlsm files.

Hope that helps.

Convert CSV file to xlsx file Python

There were issues with your file. Rename or save them as .txt files first. Then as mentioned in comments, use pandas (@DeepSpace) and specify the delimiter (@Marichyasana).

Given

A renamed text file (e.g. LOGS1.txt) of semi-colon delimited columns, example:

0;2;DT#1970-01-01-00:46:09;55;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
1;2;DT#1970-01-01-00:46:25;71;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
2;2;DT#1970-01-01-00:46:28;74;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
3;2;DT#1970-01-01-00:46:30;76;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
4;2;DT#1970-01-01-00:46:32;78;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
5;2;DT#1970-01-01-00:46:34;80;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
...

Code

import pandas as pd

filepath_in = "C:/Users/Pictures/LOGS1.txt"
filepath_out = "C:/Users/Pictures/excel.xlsx"
pd.read_csv(filepath_in, delimiter=";").to_excel(filepath_out, index=False)

Apply the same process to the second file (LOGS2.txt).

How to convert a folder full of .csv files into .xlsx files with Python?

As an alternative you could use glob.glob() which would then let you just match .csv files. os.path.splitext() can be used to help with creating a suitable output filename:

import pandas as pd
import os
from glob import glob

for csv_file in glob(r"M:\example_path\csv\*.csv"):
    print(csv_file)
    df = pd.read_csv(csv_file)
    xlsx_file = os.path.splitext(csv_file)[0] + '.xlsx'
    df.to_excel(xlsx_file, index=None, header=True)

Also don't forget to add r before the file path to stop Python parsing the \ characters.

python csv to xlsx split file excel

The following approach will split your k.csv into chunks of n rows each. Each chunk is given a number e.g. new_file001.xslx

import pandas as pd

n = 100000  # number of rows per chunk
df = pd.read_csv("k.csv")

for i in range(0, df.shape[0], n):
    df[i:i+n].to_excel(f"new_file{i:03}.xlsx", index=None, header=True)

I am trying to convert csv into xlsx fileI am getting an error No columns to parse from file

First of all you need to check what are your separators.
https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html

When using pandas separator parameter of read_csv fuction is by default ',' what are separators in your csv file? if they are for example semicolons, you need to specify it

How to convert multiple sheets in an excel workbook to csv files in python

If you want to get all of the sheets, you can pass sheet_name=None to the read_excel() call. This will then return a dictionary containing each sheet name as a key, with the value being the dataframe. With this you can iterate over each and create separate CSV files.

The following example uses a base filename with the sheetname appended, e.g. output_sheet1.csv output_sheet2.csv:

import pandas as pd

for sheet_name, df in pd.read_excel(r"input.xlsx", index_col=0, sheet_name=None).items():
    df.to_csv(f'output_{sheet_name}.csv', index=False, encoding='utf-8')

It assumes that all of your sheetnames are suitable for being used as filenames.

Copy half of the rows of the .csv file and save in .xlsx (in Python, Pandas or other module)

size_file = read_file.shape[0]
read_file1 = read_file.iloc[:int(size_file/2), :]
read_file2 = read_file.iloc[int(size_file/2):, :]
read_file1.to_excel(r'D:\Назар\Бізнес\Реалізація\test1.xlsx', index=None, header=True)
read_file2.to_excel(r'D:\Назар\Бізнес\Реалізація\test2.xlsx', index=None, header=True)

---

Related Topics