Convert CSV file to xlsx file Python

Question

Hello guys I'm looking for a solution to my code where I try to convert a CSV file into an XLSX file and all my data gets reduced into one column separated by ;. (see the pics below)

Could you please help me to solve one of the two codes in order to make the data representation when converting equal to the csv file?? (see pictures)

The two following codes give the same result: (important, I am using Python 3.6 env on Jupyter Notebook):

---

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


for csvfile in glob.glob(os.path.join('.', 'LOGS.CSV')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'r') as f:
        reader = csv.reader((line.replace('\0','-') for line in f))
        for r, row in enumerate (reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

---

import os
import csv
import sys

from openpyxl import Workbook

data_initial = open("new.csv", "r")
sys.getdefaultencoding()
workbook = Workbook()
worksheet = workbook.worksheets[0]
with data_initial as f:
    data = csv.reader((line.replace('\0','') for line in data_initial), delimiter=",")
    for r, row in enumerate(data):
        for c, col in enumerate(row):
            for idx, val in enumerate(col.split('/')):
                cell = worksheet.cell(row=r+1, column=c+1)
                cell.value = val
workbook.save('output.xlsx')

---

This is my CSV file data organization:

And this is what I get when I convert it into an XLSX:

Edit from comments

Okay, so I used @DeepSpace's program:

 import pandas as pd

 pd.read_csv('C:/Users/Pictures/LOGS.CSV')
   .to_excel('C:/Users/Pictures/excel.xlsx')

and I am still getting this:

OKAY SOLUTION: The conversion is GREAT. But in my case the first column gets moved somehow. The Data num String is under nothing and the first column is its values... (see the pictures below)

 import pandas as pd
    filepath_in = "C:/Users/Pictures/LOGS.csv"
    filepath_out = "C:/Users/Pictures/excel.xlsx"
    pd.read_csv(filepath_in, delimiter=";").to_excel(filepath_out)

Solution

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("foo.xlsx", index=False)

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

Comments

[MarcoPolo11]

Thx for Helping, by the way My first Column gets moved and its just a white cell under my Title string do you know why?= (@pylang)

[pylang]

@MarcoPolo11 I'm not sure. I've tested it, and it should still would without extra parameters. Your csv seem odd. Can you post your csv as a plaint text file, i.e. open it in a good text editor and post the results. You should see plain text with semicolons between columns.

[MarcoPolo11]

Done hope you are able to solve my problem. Thx for fast responding :) @pylang

[pylang]

Your files are not true csv files. Notice in excel, all of your data collects in the first column. You are better off renaming them LOGS1.txt and LOGS2.txt. See the update.

[MarcoPolo11]

Cool @pylang thx for help its working. One last thing, do you know how to delete a whole column and specified column by me, in csv format or xlsx using python?

[pylang]

When you read in the file, it becomes a pandas DataFrame. If you search the net for pandas related questions, you'll find many answers from here forward, e.g. "How to delete a column?", via .drop(column). Then write it to xslx.