When doing a conversion, there are several “rules” that Alarm Biller data needs to follow to have a successful import. The scripts below will clean up most of those data errors:
begin tran
create table errors
(Error nvarchar(max),
customernumber nvarchar(max),
ColumnName nvarchar(max),
ColumnContent nvarchar(max),)
insert into errors
select 'Name too long!', CustomerNumber, 'LastName', LastName
from aCustomerImport
where len(lastname) > 50
insert into errors
select 'Name too long!', CustomerNumber, 'LastName', FirstName
from aCustomerImport
where len(FirstName) > 50
insert into errors
select 'Name too long!', CustomerNumber, 'LastName', SiteName
from aCustomerImport
where len(SiteName) > 50
insert into errors
select 'Address1 too long!', CustomerNumber, 'LastAddress1', Address1
from aCustomerImport
where len(Address1) > 50
insert into errors
select 'Address1 too long!', CustomerNumber, 'LastAddress1', SiteAddress1
from aCustomerImport
where len(SiteAddress1) > 50
update aCustomerImport
set CustomerNumber = left(CustomerNumber, 15)
from aCustomerImport
where len(customernumber) > 15
update aCustomerImport
set CustomerSInce = '2021-01-01'
where ISNULL(customersince,'') = ''
update aCustomerImport
set FirstName = ''
where IsBusiness = 'TRUE'
update aCustomerImport
set FirstName = left(FirstName,50)
where len(firstname) > 50
update aCustomerImport
set LastName = 'Unknown Name'
Where isnull(lastname,'') = ''
update aCustomerImport
set Lastname = left(Lastname,50)
where len(Lastname) > 50
update aCustomerImport
set Address1 = 'Unknown Address'
Where isnull(Address1,'') = ''
update aCustomerImport
set Address1 = left(Address1,50)
where len(Address1) > 50
update aCustomerImport
set Address2 = left(Address2,50)
where len(Address2) > 50
update aCustomerImport
set City = 'Unknown City'
Where isnull(City,'') = ''
update aCustomerImport
set [state] = 'ZZ'
where ISNULL(state,'') = ''
update aCustomerImport
set [state] = left([state], 2)
where len(state) > 2
update aCustomerImport
set zipcode = 99999
where isnull(zipcode,'') ='' or len(zipcode) < 4
update aCustomerImport
set zipcode = left(zipcode, 5)
where len(zipcode) > 5 and zipcode not like '% %'
update aCustomerImport
set zipcode = left(zipcode, 7)
where len(zipcode) > 7 and zipcode like '% %'
update aCustomerImport
set Phone1 = 9999999999
where isnull(Phone1, '') = ''
update aCustomerImport
set Phone1 = REPLACE(phone1,'(','')
update aCustomerImport
set Phone1 = REPLACE(phone1,')','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'-','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'a','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'b','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'c','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'d','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'e','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'f','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'g','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'h','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'i','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'j','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'k','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'l','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'m','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'n','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'o','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'p','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'q','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'r','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'s','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'t','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'u','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'v','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'w','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'x','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'y','')
update aCustomerImport
set Phone1 = REPLACE(phone1,'z','')
update aCustomerImport
set Email = REPLACE (email,' ','')
update aCustomerImport
set Email = REPLACE (email,',','')
update aCustomerImport
set Email = REPLACE (email,';','')
update aCustomerImport
set Email = REPLACE (email,'(','')
update aCustomerImport
set Email = REPLACE (email,')','')
update aCustomerImport
set Email = REPLACE (email,'<','')
update aCustomerImport
set Email = REPLACE (email,'>','')
update aCustomerImport
set Email = REPLACE (email,':','')
update aCustomerImport
set Email = ''
where Email not like '%@%.[a-z][a-z][a-z]'
update aCustomerImport
set Commnents = left(commnents,250)
update aCustomerImport
set SiteName = 'Unknown Name'
Where isnull(SiteName,'') = ''
update aCustomerImport
set SiteName = left(SiteName,50)
where len(SiteName) > 50
update aCustomerImport
set SiteAddress1 = 'Unknown Address'
Where isnull(SiteAddress1,'') = ''
update aCustomerImport
set SiteAddress1 = left(SiteAddress1,50)
where len(SiteAddress1) > 50
update aCustomerImport
set SiteAddress2 = left(SiteAddress2,50)
where len(SiteAddress2) > 50
update aCustomerImport
set SiteCity = 'Unknown SiteCity'
Where isnull(SiteCity,'') = ''
update aCustomerImport
set [state] = 'ZZ'
where ISNULL(state,'') = ''
update aCustomerImport
set [state] = left([state], 2)
where len(state) > 2
update aCustomerImport
set SiteZipcode = 99999
where isnull(SiteZipcode,'') ='' or len(SiteZipcode) < 4
update aCustomerImport
set SiteZipcode = left(SiteZipcode, 5)
where len(SiteZipcode) > 5 and SiteZipcode not like '% %'
update aCustomerImport
set SiteZipcode = left(SiteZipcode, 7)
where len(SiteZipcode) > 7 and SiteZipcode like '% %'
update aCustomerImport
set SitePhone1 = 9999999999
where isnull(SitePhone1, '') = ''
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'(','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,')','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'-','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'a','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'b','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'c','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'d','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'e','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'f','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'g','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'h','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'i','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'j','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'k','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'l','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'m','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'n','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'o','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'p','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'q','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'r','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'s','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'t','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'u','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'v','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'w','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'x','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'y','')
update aCustomerImport
set SitePhone1 = REPLACE(SitePhone1,'z','')
UPDATE aCustomerImport
SET SITESINCE = '2021-01-01'
WHERE ISNULL(SITESINCE,'') = ''
UPDATE aCustomerImport
SET SITESALESTAXID = 'Tax Exempt'
where ISNULL(sitesalestaxid,'') = ''
update aCustomerImport
set SITECOMMENTS = left(SITECOMMENTS,250)
update aCustomerImport
set SYSTEMNUMBER = 'NONE'
where isnull(SYSTEMNUMBER,'') = ''
update aCustomerImport
set SYSTEMTYPEID = 'N/A'
where ISNULL(SYSTEMTYPEID,'') = ''
update aCustomerImport
set SYSTEMPARTID = 'N/A'
where isnull(systempartid,'') = ''
update aCustomerImport
set SYSTEMWARRANTYLABORID = 'None'
where isnull(systemwarrantylaborid,'') = ''
update aCustomerImport
set SYSTEMWARRANTYPARTID = 'None'
where isnull(SYSTEMWARRANTYPARTID, '') = ''
update aCustomerImport
set SYSTEMWARRANTYSTARTDATE = '2021-01-01'
where isnull(systemwarrantystartdate,'') = ''
update aCustomerImport
set SYSTEMCOMMENTS = left(systemcomments, 250)
update aCustomerImport
set RMRITEMID = ''
where isnull(RMRDESCRIPTION, '') = '' and isnull(rmrmonthlyamount,'') = '' and isnull(RMRBILLINGCYCLEID, '') = ''
update aCustomerImport
set RMRDESCRIPTION = ''
where isnull(RMRITEMID, '') = '' and isnull(rmrmonthlyamount,'') = '' and isnull(RMRBILLINGCYCLEID, '') = ''
update aCustomerImport
set RMRBILLINGCYCLEID = ''
where isnull(RMRITEMID, '') = '' and isnull(rmrmonthlyamount,'') = '' and isnull(RMRDESCRIPTION, '') = ''
update aCustomerImport
set RMRRECURRINGREASONID = ''
where ISNULL(rmritemid,'') = ''
update aCustomerImport
set RMRMONTHLYAMOUNT = replace(rmrmonthlyamount,'$','')
select * from errors
select * from aCustomerImport
--rollback tran
--commit tran
