将CSV文件转换为xlsx文件Python [英] Convert CSV file to xlsx file Python

问题描述

大家好，我正在寻找一种代码解决方案，尝试将CS​​V文件转换为XLSX文件，然后将所有数据缩小为用;分隔的一列. (请参见下面的图片)

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)

能否请我解决两个代码之一，以便在转换为csv文件时使数据表示为准? (参见图片)

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)

以下两个代码给出的结果相同:(重要的是，我正在Jupyter Notebook上使用Python 3.6 env):

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')

---

这是我的CSV文件数据组织:

---

This is my CSV file data organization:

这是将其转换为XLSX时得到的结果:

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

从评论中编辑

好的，所以我使用了@DeepSpace的程序:

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:

确定解决方案: 转换非常棒.但是在我的情况下，第一列以某种方式移动了.数据num字符串为空，第一列为其值...(请参见下图)

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)

推荐答案

文件存在问题.重命名或先将它们另存为.txt文件.然后，如注释中所述，使用pandas(@DeepSpace)并指定定界符(@Marichyasana).

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).

给予

以分号分隔的列的重命名文本文件(例如LOGS1.txt)，例如:

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
...

代码

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)

对第二个文件(LOGS2.txt)应用相同的过程.

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

这篇关于将CSV文件转换为xlsx文件Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！