I am writing a python script to convert old DayLite contacts into CSV format to be imported into Outlook. I have a script that functions completely almost perfectly except for one small issue but due to being mass data fixing it in the file will take way to long.
The list of contacts is very long 1,100+ rows in the spreadsheet. When the text gets written into the CSV file everything is good except certain/random phone numbers lose their leading 0 and gain a '.0' at the end. However the majority of the phone numbers are left in the exact format.
This is my script code:
import xlrd
import xlwt
import csv
import numpy
##########################
# Getting XLS Data sheet #
##########################
oldFormatContacts = xlrd.open_workbook('DayliteContacts_Oct16.xls')
ofSheet = oldFormatContacts.sheet_by_index(0)
##################################
# Storing values in array medium #
##################################
rowVal = [''] * ofSheet.nrows
x = 1
for x in range(ofSheet.nrows):
rowVal[x] = (ofSheet.row_values(x))
######################
# Getting CVS titles #
######################
csvTemp = xlrd.open_workbook('Outlook.xls')
csvSheet = csvTemp.sheet_by_index(0)
csv_title = csvSheet.row_values(0)
rowVal[0] = csv_title
##############################################################
# Append and padding data to contain commas for empty fields #
##############################################################
x = 0
q = '"'
for x in range(ofSheet.nrows):
temporaryRow = rowVal[x]
temporaryRow = str(temporaryRow).strip('[]')
if x > 0:
rowVal[x] = (','+str(q+temporaryRow.split(',')[0]+q)+',,'+str(q+temporaryRow.split(',')[1]+q)+',,'+str(q+temporaryRow.split(',')[2]+q)+',,,,,,,,,,,,,,,,,,,,,,,,,,'+str(q+temporaryRow.split(',')[4]+q)+','+str(q+temporaryRow.split(',')[6]+q)+',,,,,,,,,,,,,,,,,,,,,,,,,'+str(q+temporaryRow.split(',')[8])+q)
j = 0
for j in range(0,21):
rowVal[x] += ','
tempString = str(rowVal[x])
tempString = tempString.replace("'","")
#tempString = tempString.replace('"', '')
#tempString = tempString.replace(" ", "")
rowVal[x] = tempString
######################################
# Open and write values too new file #
######################################
csv_file = open('csvTestFile.csv', 'w')
rownum = 0
for rownum in range(ofSheet.nrows):
csv_file.write(rowVal[rownum])
csv_file.write("\n")
csv_file.close()
Sorry if my coding is incoherent I am a beginner to python scripts.
Unfortunately I cannot show or provide the contact details due to privacy reasons however I will give some examples in the exact format that it occurs.
So in the DayLite document a contact would be saved as "First name, Second name, Company, phone number 1, phone number 2, email" for example: "Joe, Black, Stack Overflow, 07472329584," but when written into the CSV file it will be "Joe","Black","Stack Overflow","7472329584.0".
This is odd because for each occurrence of that problem there will be 10 or so fine numbers that get saved exactly the same e.g. In DayLite: "+446738193583" when written in CSV: "+446738193583".
It seems to me to be a very weird error and this is why I have come here for help! If anyone has any ideas I'd be more than happy to hear them. Cheers guys.