Remove line breaks from an excel cells using python

Question

I am trying to converting excel file to csv file. the data in the excel file is like below:

My code to convert to csv:

import pandas as pd
import glob
for excel_file in glob.glob('C:/Talend/DEV/MARKET_OPTIMISATION/IMS/*Extract*.xls'):
    print(excel_file)
    data_xls = pd.read_excel(excel_file, 'Untitled', index=0,skiprows=1, sep='|',encoding='utf-8')
    #data_xlx.pop
    data_xls1=data_xls.replace('\r\n','')
    data_xls1.to_csv('C:/Talend/DEV/MARKET_OPTIMISATION/IMS/IMS_Raw_data.csv',sep='|',encoding='utf-8')

The output of the above code is:

but I need out put like this

can anyone please help me in removing the line breaks on the excel file.

Thank you in advance.

Serge Ballesta · Accepted Answer · 2019-01-28 16:34:27Z

3

In your dataframe, the newlines are in the column names. And the column names are not affected when you use the replace method of the dataframe, only the data are.

So in your example, you should explicitely change the column names:

data_xls = pd.read_excel(excel_file, 'Untitled', index=0,skiprows=1, sep='|',encoding='utf-8')
data_xls.columns = data_xls.columns.map(lambda x: x.replace('\r','').replace('\n', ''))

answered Jan 28, 2019 at 16:34

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Supriya Rapelli Over a year ago

@Ballesta, I am facing one more problem here. I have data like 'NA' while converting to the xls file to csv, it is considering NA as null value. can you please suggest how read the data as is.

Hubert Dudek · Accepted Answer · 2019-01-28 16:07:46Z

0

try replace \r and \n separately

mystring = mystring.replace('\n', ' ').replace('\r', '')

if it fails just .split() string and then .join() list elements

answered Jan 28, 2019 at 16:07

Hubert Dudek

1,7303 gold badges15 silver badges21 bronze badges

1 Comment

Supriya Rapelli Over a year ago

tried using replace separately also but it didn't work.

Amir Imani · Accepted Answer · 2019-01-28 16:18:09Z

0

You can use something like this:

import re
re.sub("\n|\r", "", mystring)

answered Jan 28, 2019 at 16:18

Amir Imani

3,2552 gold badges25 silver badges27 bronze badges

Comments

Azhar Ansari · Accepted Answer · 2019-06-27 17:31:34Z

0

You have to use regex=True in your command, like below:

import re
data_xls = data_xls.replace('\n', ' ', regex=True)

or better would be to replace any kind of whitespace with a single space:

data_xls = data_xls.replace('\s', ' ', regex=True)

edited Jun 27, 2019 at 17:31

answered Jun 22, 2019 at 15:20

Azhar Ansari

1361 silver badge4 bronze badges

Comments

Hrvoje · Accepted Answer · 2020-12-04 12:10:52Z

0

You need to replace \t (Tabs). This will get you all records inline.

mystring = mystring.replace('\t','')

answered Dec 4, 2020 at 12:10

Hrvoje

15.4k11 gold badges104 silver badges121 bronze badges

Comments

Hrvoje · Accepted Answer · 2020-12-04 12:16:44Z

0

You need to replace \t (Tabs). This will get you all records inline.

mystring = mystring.replace('\t','')

You can paste part of your data here to see what chars are hidden in your data.

answered Dec 4, 2020 at 12:16

Hrvoje

15.4k11 gold badges104 silver badges121 bronze badges

Collectives™ on Stack Overflow

Remove line breaks from an excel cells using python

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related