英文:
Keep enter space in text column when converting to csv
问题
以下是要翻译的内容：
I have data in excel where is text with enter space in the last column. Here is examples of my data:
If I convert using python to csv, my data looks like this:
I need the TEXT column will be like this:
This is my script:
import pandas as pdimport osimport numpy as npWD = r'XXX'os.chdir(WD)for file in os.listdir(WD):if file.endswith('.xlsx'):FILE = filesheet_names = pd.ExcelFile(FILE).sheet_namesfor 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)
Do you have any idea?
英文:
I have data in excel where is text with enter space in last column. Here is examples of my data:
If I convert using python to csv, my data looks like this:
I need the TEXT column will be like this:
This is my script:
import pandas as pdimport osimport numpy as npWD = r'XXX'os.chdir(WD)for file in os.listdir(WD):if file.endswith('.xlsx'):FILE = filesheet_names = pd.ExcelFile(FILE).sheet_namesfor 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)
Do you have any idea?
答案1
得分: 1
以下是您要翻译的内容：
Excel to csv:
import pandas as pddf = 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 xdf['Text'] = df['Text'].apply(lambda x: replace_custom_func(x))df.to_csv('keep_enter1.csv', sep='|', index=False)
CSV to 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.bookworksheet = 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()
Output:
英文:
I hope this works for your solution, (pip install xlsxwriter) before executing
Excel to csv:
import pandas as pddf = 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 xdf['Text'] = df['Text'].apply(lambda x: replace_custom_func(x))df.to_csv('keep_enter1.csv', sep='|', index=False)
CSV to 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.bookworksheet = 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()
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论