How to remove all Special Character before to push the data to MSSQL

Question

I have an excel file that I want to push a sheet to database SQL, But before to push the data I want to replace or remove all Special chacater like brackets (), % and Spaces..

So for example I have a column name like RRC Setup Success Rate(%) so I want to make it like this RRCSetupSuccessRate before to push them to database MSSQL

as Here's my Simple Code looks like:

import pyodbc
import pandas as pd
import os
from sqlalchemy import create_engine

# connect db
engine = create_engine('mssql+pyodbc://xxxxxxxxxxx\xxxxxxxxxx/myDB?driver=SQL+Server+Native+Client+11.0')
cursor = engine.raw_connection().cursor()


mydir = (os.getcwd()).replace('\\', '/') + '/'

lte_details = pd.read_excel(r'' + mydir + 'input.xlsx', sheet_name='LTE Details')

lte_details.columns = lte_details.columns.str.replace(' ', '')

# reading and insert one file at a time
for file in os.listdir('.'):
    # only process excels files
    file_basename, extension = file.split('.')
    if extension == 'xlsx':
        lte_details.to_sql(file_basename.lower(), con=engine.connect(), if_exists='replace')

I tried to create some thing like this

lte_details.columns = lte_details.columns.str.replace('\(%\)', '').str.replace(' ','').str.lower()

but I want to add one more thing, that removes all the brackets in the columns name

Does this answer your question? https://stackoverflow.com/questions/5843518/remove-all-special-characters-punctuation-and-spaces-from-string — Kalma, Jan 12 '20 at 12:16
@Kalma thank your for your comment but how can I do this related to this line ` lte_details.columns = lte_details.columns.str.replace(' ', '')` — Mahmoud Al-Haroon, Jan 12 '20 at 12:17
I tried to do some thing like this `lte_details.columns.isalnum()` but I get this error `AttributeError: 'Index' object has no attribute 'isalnum'` — Mahmoud Al-Haroon, Jan 12 '20 at 12:25
Oh, I see. I can not reproduce your code, but what the interpreter is telling you is that lte_details.colums is not a string, but an Index object. You have to use ''.join(e for e in string if e.isalnum()) on a string! (maybe lte_details instead of lte_details.columns? not sure) — Kalma, Jan 12 '20 at 12:32
Can you try this on your code (sorry I can not): `lte_details.columns = ''.join(e for e in lte_details.columns if e.isalnum()` — Kalma, Jan 12 '20 at 12:51

score 2 · Accepted Answer · answered Jan 12 '20 at 13:27

2

I was able to solve it by using what was in the answer that @Kalma mentioned:

lte_details.columns = [''.join(e for e in x if e.isalnum()) for x in lte_details.columns]

lte_details.columns returns you a Index object that can be iterated.

answered Jan 12 '20 at 13:27

samlima

188
10

score 0 · Answer 2 · answered Jan 12 '20 at 15:42

0

I have found another solution for this but it's not smart enough

lte_details.columns = lte_details.columns.str.replace('\(%\)', '').str.replace(' ','').str.replace('.','').str.replace('-','_').str.replace('(','').str.replace(')','').str.lower()

answered Jan 12 '20 at 15:42

Mahmoud Al-Haroon

2,239
7
36
72

How to remove all Special Character before to push the data to MSSQL

2 Answers2