转换为csv时在文本列中保留输入空格

kx5bkwkv 于 2023-02-10 发布在其他

关注(0)|答案(2)|浏览(227)

我在Excel中有数据，其中是文本，在最后一列输入空格。下面是我的数据的例子：

如果我使用python转换为csv，我的数据如下所示：

我需要文本列如下所示：

这是我的剧本：

import pandas as pd
import os
import numpy as np

WD = r'XXX'
os.chdir(WD) 

for file in os.listdir(WD):
    if file.endswith('.xlsx'):
        FILE = file

        sheet_names = pd.ExcelFile(FILE).sheet_names  

        for sn in sheet_names:
            OUTPUT_FILE = '{}_{}'.format(sn,FILE.replace('.xlsx','.csv'))
            df = pd.read_excel(FILE,)
            print(FILE, sn)

            for col in df.columns.to_list():
                df[col] = df[col].map({True: '', False: ''}).fillna(df[col])

                cn = ['IN', 'NAME', 'TEXT']
                df = df.reindex(columns = cn)

                df.to_csv(OUTPUT_FILE,sep='|',encoding='utf-8-sig',index=False)

你知道吗？

csv

来源：https://stackoverflow.com/questions/75385206/keep-enter-space-in-text-column-when-converting-to-csv

2条答案

按热度按时间

e4yzc0pl1#

我希望这适用于您的解决方案，（pip install xlsxwriter）在执行之前

- Excel格式转换为csv：**

import pandas as pd
df = pd.read_excel('./keep_enter.xlsx')
def replace_custom_func(x):
    new_str = ''
    if len(x) > 0:
        for i in x.split('\n'):
            new_str += f'"{i}"&CHAR(10)&'
        return "=" + new_str[:-10]
    else:
        return x
df['Text'] = df['Text'].apply(lambda x: replace_custom_func(x))
df.to_csv('keep_enter1.csv', sep='|', index=False)

- CSV到Excel：**

df = pd.read_csv('./keep_enter1.csv', sep='|')
writer = pd.ExcelWriter('new_excel_replace12345.xlsx', engine='xlsxwriter')

# # Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# # Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format = workbook.add_format({'text_wrap': True})
worksheet.set_column('C:D', None, format)
worksheet.write_formula(1, 2, df['Text'][0])
# # Close the Pandas Excel writer and output the Excel file.
writer.save()