1

I am loading a comma separate file into python and loading into SQL Server table. But facing an issue while reading Company column. Reason: ., special characters in Company column values. So that my python code reading two columns for Company values XYZ Co., Ltd,YZA Co., Ltd and XYZ Co., Ltd

How to fix this issue? Source data in CSV:

ID,Company,Date,Sales
1,ABC Company Ltd,10-Jun-19,10
2,BCD Company Ltd,11-Jun-19,20
3,XYZ Co., Ltd,12-Jun-19,30
4,YZA Co., Ltd,13-Jun-19,40
5,CDF Company Ltd,14-Jun-19,50
6,XYZ Co., Ltd,15-Jun-19,60
7,ABC Company Ltd,16-Jun-19,70
8,ABC Company Ltd,17-Jun-19,80

Expected out from python Pandas: enter image description here

Improve this question
1
  • 1
    Data which might contain 'comma' in the text should be inside quotes for proper parsing. If that's possible in your case: follow stackoverflow.com/questions/21527057/… Commented Aug 13, 2019 at 10:37

1 Answer 1

Reset to default
2

Use pandas.read_csv with escapechar:

import pandas as pd

df = pd.read_csv('/path/to/your/file', sep = ",",escapechar='.')
print(df)

Output:

   ID          Company       Date  Sales
0   1  ABC Company Ltd  10-Jun-19     10
1   2  BCD Company Ltd  11-Jun-19     20
2   3      XYZ Co, Ltd  12-Jun-19     30
3   4      YZA Co, Ltd  13-Jun-19     40
4   5  CDF Company Ltd  14-Jun-19     50
5   6      XYZ Co, Ltd  15-Jun-19     60
6   7  ABC Company Ltd  16-Jun-19     70
7   8  ABC Company Ltd  17-Jun-19     80

If you want your . back, do manual replacement after reading it:

df['Company'] = df['Company'].replace('Co,', 'Co.,', regex=True)

Output:

   ID          Company       Date  Sales
0   1  ABC Company Ltd  10-Jun-19     10
1   2  BCD Company Ltd  11-Jun-19     20
2   3     XYZ Co., Ltd  12-Jun-19     30
3   4     YZA Co., Ltd  13-Jun-19     40
4   5  CDF Company Ltd  14-Jun-19     50
5   6     XYZ Co., Ltd  15-Jun-19     60
6   7  ABC Company Ltd  16-Jun-19     70
7   8  ABC Company Ltd  17-Jun-19     80
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.