Python Forum
csv to xlsx, remove " while writing to excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
csv to xlsx, remove " while writing to excel
#1
Hi All,

My sample data from csv file is as below, I am reading csv file and writing to excel, but " not getting removed, however, I pasting same to data in excel and just spiting (text to column) by '^', excel is not showing any " character.

from openpyxl import Workbook
import csv
import re
lines = []
wb = Workbook()
ws = wb.active
with open('netcool.csv', 'r') as csvfile:
     data = csv.reader(csvfile, delimiter='^', quotechar='|')
     for row in data:
         #row = re.sub('"', '', row)
         print((row))
         ws.append(row)
wb.save('netcool.xlsx')
sample data
Quote:IDENTIFIER^LASTOCCURRENCE^ALERTGROUP^FMS_OFF_TIME^FMS_ON_TIME^NODE^NE_ON_TIME^NE_OFF_TIME^REGION^CIRCLE^CITY^ALERT_NAME^ALERT_COUNT^MANAGER^TECHNOLOGY^ALARM_CATEGORY^EQUIPMENT_TYPE^CLEARED_BY^CLEAREDTIME_TEXT^OPERATOR^TTNUMBER^TT_STATUS^LOCATIONID^AMO^ACKNOWLEDGED^FRIENDLY_NAME^SEVERITY^SERIAL^SUMMARY
"UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02_DEGRADED_SIGNAL-MsTTP^""2019-03-28-16.14.09.000000""^""AlarmClear""^""1970-01-01-05.30.00.000000""^""2019-03-28-16.14.09.000000""^""UE62-LUC_TP_Nagar""^""2019-03-28-16.14.24.000000""^""1970-01-01-05.30.00.000000""^""""^""""^""""^""DEGRADED_SIGNAL-MsTTP""^319^""UP_LUCK_IOO_FM""^""TXN_ALCATEL_NM""^""""^""""^""""^""""^""OSPUN185""^""11551840""^""""^""""^""UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02""^1^""UE62-LUC_TP_Nagar""^3^13215461^""000:"""
"OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01_LOSS_OF_SIGNAL-OpS^""2019-03-28-12.21.35.000000""^""AlarmRaise""^""2019-03-28-16.27.49.000000""^""2019-03-28-12.21.35.000000""^""OR62_ANG_HINDOL_RD""^""2019-03-28-12.21.19.000000""^""2019-03-28-16.27.48.000000""^""EAST""^""ORISSA""^""DHENKANAL""^""LOSS_OF_SIGNAL-OpS""^72^""NULL""^""TXN_ALCATEL_NM""^""COMMUNICATIONSALARM""^""SDH""^""""^""""^""BA80542248""^""11477261""^""RESOLVED""^""ORCUTDHE1450VI0003""^""OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01""^1^""OR62_ANG_HINDOL_RD""^0^2118297644^""No Description"""
"PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003_AIS-MsTTP^""2019-03-28-17.58.54.000000""^""AlarmClear""^""1970-01-01-05.30.00.000000""^""2019-03-28-17.50.26.000000""^""PB60_BTD_BSC_02""^""2019-03-28-17.50.18.000000""^""1970-01-01-05.30.00.000000""^""NORTH""^""PUNJAB""^""BATHINDA""^""AIS-MsTTP""^131255^""PB_MOHA_IOO""^""TXN_ALCATEL_NM""^""""^""SDH""^""""^""""^""""^""""^""""^""PJBATBATBHATVI0004""^""PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003""^0^""Bhatinda BSC""^4^16456317^""No Description"""

script output

Output:
['"UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02_DEGRADED_SIGNAL-MsTTP', '""2019-03-28-16.14.09.000000""', '""AlarmClear""', '""1970-01-01-05.30.00.000000""', '""2019-03-28-16.14.09.000000""', '""UE62-LUC_TP_Nagar""', '""2019-03-28-16.14.24.000000""', '""1970-01-01-05.30.00.000000""', '""""', '""""', '""""', '""DEGRADED_SIGNAL-MsTTP""', '319', '""UP_LUCK_IOO_FM""', '""TXN_ALCATEL_NM""', '""""', '""""', '""""', '""""', '""OSPUN185""', '""11551840""', '""""', '""""', '""UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02""', '1', '""UE62-LUC_TP_Nagar""', '3', '13215461', '""000:"""'] ['"OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01_LOSS_OF_SIGNAL-OpS', '""2019-03-28-12.21.35.000000""', '""AlarmRaise""', '""2019-03-28-16.27.49.000000""', '""2019-03-28-12.21.35.000000""', '""OR62_ANG_HINDOL_RD""', '""2019-03-28-12.21.19.000000""', '""2019-03-28-16.27.48.000000""', '""EAST""', '""ORISSA""', '""DHENKANAL""', '""LOSS_OF_SIGNAL-OpS""', '72', '""NULL""', '""TXN_ALCATEL_NM""', '""COMMUNICATIONSALARM""', '""SDH""', '""""', '""""', '""BA80542248""', '""11477261""', '""RESOLVED""', '""ORCUTDHE1450VI0003""', '""OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01""', '1', '""OR62_ANG_HINDOL_RD""', '0', '2118297644', '""No Description"""'] ['"PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003_AIS-MsTTP', '""2019-03-28-17.58.54.000000""', '""AlarmClear""', '""1970-01-01-05.30.00.000000""', '""2019-03-28-17.50.26.000000""', '""PB60_BTD_BSC_02""', '""2019-03-28-17.50.18.000000""', '""1970-01-01-05.30.00.000000""', '""NORTH""', '""PUNJAB""', '""BATHINDA""', '""AIS-MsTTP""', '131255', '""PB_MOHA_IOO""', '""TXN_ALCATEL_NM""', '""""', '""SDH""', '""""', '""""', '""""', '""""', '""""', '""PJBATBATBHATVI0004""', '""PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003""', '0', '""Bhatinda BSC""', '4', '16456317', '""No Description"""']
Reply
#2
done, is there alternative or better way?

from openpyxl import Workbook
import datetime
import csv
import re
#from datetime import datetime
def string2date(date):
    datetime_object = datetime.datetime.strptime(date, '%Y-%m-%d-%H.%M.%S.%f').strftime("%d/%m/%Y %H:%M:%S")
    return datetime_object
newrow = []
wb = Workbook()
ws = wb.active
with open('netcool.csv', 'r') as csvfile:
     data = csv.reader(csvfile, delimiter='^',quotechar='"', quoting=csv.QUOTE_NONE)
     for row in data:
         if row[0] == 'IDENTIFIER':
             pass
         else:
             LASTOCCURRENCE_date_time = row[1].strip('"')
             FMS_OFF_TIME_date_time = row[3].strip('"')
             FMS_ON_TIME_date_time = row[4].strip('"')
             NE_ON_TIME_date_time = row[6].strip('"')
             NE_OFF_TIME_date_time= row[7].strip('"')
             LASTOCCURRENCE = string2date(str(LASTOCCURRENCE_date_time))
             FMS_OFF_TIME = string2date(str(FMS_OFF_TIME_date_time))
             FMS_ON_TIME = string2date(str(FMS_ON_TIME_date_time))
             NE_ON_TIME  = string2date(str(NE_ON_TIME_date_time))
             NE_OFF_TIME  =  string2date(str(NE_OFF_TIME_date_time))
             newrow = (row[0].replace('"',''),LASTOCCURRENCE,row[2].replace('""""','na').strip('"'),FMS_OFF_TIME,\
                      FMS_ON_TIME,row[5].replace('""""','na').strip('"'),NE_ON_TIME,NE_OFF_TIME,row[8].replace('""""','na').strip('"'),\
                      row[9].replace('""""','na').strip('"'),row[10].replace('""""','na').strip('"'),row[11].replace('""""','na').strip('"'),\
                      row[12].replace('""""','na').strip('"'),row[13].replace('""""','na').strip('"'),row[14].replace('""""','na').strip('"'),\
                      row[15].replace('""""','na').strip('"'),row[16].replace('""""','na').strip('"'),row[17].replace('""""','na').strip('"'),\
                      row[18].replace('""""','na').strip('"'),row[19].replace('""""','na').strip('"'),row[20].replace('""""','na').strip('"'),\
                      row[21].replace('""""','na').strip('"'),row[22].replace('""""','na').strip('"'),row[23].replace('""""','na').strip('"'),\
                      row[24].replace('""""','na').strip('"'),row[25].replace('""""','na').strip('"'),row[27].replace('""""','na').strip('"'),\
                      row[28].replace('""""','na').strip('"').strip(','))
             ws.append(newrow)
wb.save('netcool.xlsx')
Reply
#3
You can make your own reader instead of using csv.reader.

Just one generator to read the file and get the content out:
def custom_reader(file):
    stripper = lambda line: list(col.replace('"', '').strip() for col in line.split('^'))
    with open(file) as fd:
        for line in fd:
            yield stripper(line)
The stripper could be written as function.

Read and print the data:
for row in custom_reader('sample_data.txt'):
    print(row)
Output:
['IDENTIFIER', 'LASTOCCURRENCE', 'ALERTGROUP', 'FMS_OFF_TIME', 'FMS_ON_TIME', 'NODE', 'NE_ON_TIME', 'NE_OFF_TIME', 'REGION', 'CIRCLE', 'CITY', 'ALERT_NAME', 'ALERT_COUNT', 'MANAGER', 'TECHNOLOGY', 'ALARM_CATEGORY', 'EQUIPMENT_TYPE', 'CLEARED_BY', 'CLEAREDTIME_TEXT', 'OPERATOR', 'TTNUMBER', 'TT_STATUS', 'LOCATIONID', 'AMO', 'ACKNOWLEDGED', 'FRIENDLY_NAME', 'SEVERITY', 'SERIAL', 'SUMMARY'] ['UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02_DEGRADED_SIGNAL-MsTTP', '2019-03-28-16.14.09.000000', 'AlarmClear', '1970-01-01-05.30.00.000000', '2019-03-28-16.14.09.000000', 'UE62-LUC_TP_Nagar', '2019-03-28-16.14.24.000000', '1970-01-01-05.30.00.000000', '', '', '', 'DEGRADED_SIGNAL-MsTTP', '319', 'UP_LUCK_IOO_FM', 'TXN_ALCATEL_NM', '', '', '', '', 'OSPUN185', '11551840', '', '', 'UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02', '1', 'UE62-LUC_TP_Nagar', '3', '13215461', '000:'] ['OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01_LOSS_OF_SIGNAL-OpS', '2019-03-28-12.21.35.000000', 'AlarmRaise', '2019-03-28-16.27.49.000000', '2019-03-28-12.21.35.000000', 'OR62_ANG_HINDOL_RD', '2019-03-28-12.21.19.000000', '2019-03-28-16.27.48.000000', 'EAST', 'ORISSA', 'DHENKANAL', 'LOSS_OF_SIGNAL-OpS', '72', 'NULL', 'TXN_ALCATEL_NM', 'COMMUNICATIONSALARM', 'SDH', '', '', 'BA80542248', '11477261', 'RESOLVED', 'ORCUTDHE1450VI0003', 'OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01', '1', 'OR62_ANG_HINDOL_RD', '0', '2118297644', 'No Description'] ['PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003_AIS-MsTTP', '2019-03-28-17.58.54.000000', 'AlarmClear', '1970-01-01-05.30.00.000000', '2019-03-28-17.50.26.000000', 'PB60_BTD_BSC_02', '2019-03-28-17.50.18.000000', '1970-01-01-05.30.00.000000', 'NORTH', 'PUNJAB', 'BATHINDA', 'AIS-MsTTP', '131255', 'PB_MOHA_IOO', 'TXN_ALCATEL_NM', '', 'SDH', '', '', '', '', '', 'PJBATBATBHATVI0004', 'PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003', '0', 'Bhatinda BSC', '4', '16456317', 'No Description'] [''] ['']
The last two rows seems to be empty.

To get rid of then, just look how many elements are in the resulting list for each line.

def custom_reader(file):
    stripper = lambda line: list(col.replace('"', '').strip() for col in line.split('^'))
    with open(file) as fd:
        for line in fd:
            row = stripper(line)
            if len(row) != 29:
                # wrong number of columns, just take the next line
                continue
            yield row


for row in custom_reader('sample_data.txt'):
    print(row)
Output:
['IDENTIFIER', 'LASTOCCURRENCE', 'ALERTGROUP', 'FMS_OFF_TIME', 'FMS_ON_TIME', 'NODE', 'NE_ON_TIME', 'NE_OFF_TIME', 'REGION', 'CIRCLE', 'CITY', 'ALERT_NAME', 'ALERT_COUNT', 'MANAGER', 'TECHNOLOGY', 'ALARM_CATEGORY', 'EQUIPMENT_TYPE', 'CLEARED_BY', 'CLEAREDTIME_TEXT', 'OPERATOR', 'TTNUMBER', 'TT_STATUS', 'LOCATIONID', 'AMO', 'ACKNOWLEDGED', 'FRIENDLY_NAME', 'SEVERITY', 'SERIAL', 'SUMMARY'] ['UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02_DEGRADED_SIGNAL-MsTTP', '2019-03-28-16.14.09.000000', 'AlarmClear', '1970-01-01-05.30.00.000000', '2019-03-28-16.14.09.000000', 'UE62-LUC_TP_Nagar', '2019-03-28-16.14.24.000000', '1970-01-01-05.30.00.000000', '', '', '', 'DEGRADED_SIGNAL-MsTTP', '319', 'UP_LUCK_IOO_FM', 'TXN_ALCATEL_NM', '', '', '', '', 'OSPUN185', '11551840', '', '', 'UE62-LUC_TP_Nagar_Card=r01sr1sl09_PTP=02', '1', 'UE62-LUC_TP_Nagar', '3', '13215461', '000:'] ['OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01_LOSS_OF_SIGNAL-OpS', '2019-03-28-12.21.35.000000', 'AlarmRaise', '2019-03-28-16.27.49.000000', '2019-03-28-12.21.35.000000', 'OR62_ANG_HINDOL_RD', '2019-03-28-12.21.19.000000', '2019-03-28-16.27.48.000000', 'EAST', 'ORISSA', 'DHENKANAL', 'LOSS_OF_SIGNAL-OpS', '72', 'NULL', 'TXN_ALCATEL_NM', 'COMMUNICATIONSALARM', 'SDH', '', '', 'BA80542248', '11477261', 'RESOLVED', 'ORCUTDHE1450VI0003', 'OR62_ANG_HINDOL_RD_Card=r01sr1sl15_PTP=01', '1', 'OR62_ANG_HINDOL_RD', '0', '2118297644', 'No Description'] ['PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003_AIS-MsTTP', '2019-03-28-17.58.54.000000', 'AlarmClear', '1970-01-01-05.30.00.000000', '2019-03-28-17.50.26.000000', 'PB60_BTD_BSC_02', '2019-03-28-17.50.18.000000', '1970-01-01-05.30.00.000000', 'NORTH', 'PUNJAB', 'BATHINDA', 'AIS-MsTTP', '131255', 'PB_MOHA_IOO', 'TXN_ALCATEL_NM', '', 'SDH', '', '', '', '', '', 'PJBATBATBHATVI0004', 'PB60_BTD_BSC_02_Card=r01sr1sl09_PTP=003', '0', 'Bhatinda BSC', '4', '16456317', 'No Description']
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#4
Thanks
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Need Help writing data into Excel format ajitnayak87 8 2,438 Feb-04-2022, 03:00 AM
Last Post: Jeff_t
  Want to remove the text from a particular column in excel shantanu97 2 2,092 Jul-05-2021, 05:42 PM
Last Post: eddywinch82
Smile Set 'Time' format cell when writing data to excel and not 'custom' limors 3 6,198 Mar-29-2021, 09:36 PM
Last Post: Larz60+
  Writing to existing excel sheet jksvend 0 1,914 Oct-12-2020, 11:19 AM
Last Post: jksvend
  Write tables from Word (.docx) to Excel (.xlsx) using xlsxwriter jackie 1 3,139 May-27-2020, 11:47 PM
Last Post: mcmxl22
  How can I speed up my openpyxl program reading Excel .xlsx files? deac33 0 3,362 May-04-2020, 08:02 PM
Last Post: deac33
  Problem writing a variable value to Excel Chuck_Norwich 1 1,903 Jul-25-2019, 02:20 PM
Last Post: Chuck_Norwich
  remove underline from hyperlink in excel SriMekala 0 1,852 Jul-25-2019, 01:58 PM
Last Post: SriMekala
  Writing to Excel file aa8888 2 2,619 Mar-06-2019, 06:04 PM
Last Post: aa8888
  Getting error while loading excel(.xlsx) file using openpyxl module shubhamjainj 1 8,899 Mar-01-2019, 01:05 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020