Salesforce 數(shù)據(jù)清洗資料

Salesforce數(shù)據(jù)清洗新系統(tǒng)上線后，需要導(dǎo)入歷史數(shù)據(jù)，但是舊數(shù)據(jù)格式，數(shù)據(jù)缺失，數(shù)據(jù)錯(cuò)誤，奇異值，屬性歸類(lèi)與新系統(tǒng)有很大的gap。因此我們需要建立一套數(shù)據(jù)動(dòng)態(tài)清洗規(guī)則給Salesforce系統(tǒng)，通過(guò)這些規(guī)則自動(dòng)清洗導(dǎo)入數(shù)據(jù)，清洗規(guī)則可以讓function自己配置。而不需要IT負(fù)責(zé)下面將詳細(xì)舉一個(gè)例子如何在salesforce中做數(shù)據(jù)處理。數(shù)據(jù)清洗需要分成5個(gè)步驟1，建立2個(gè)關(guān)聯(lián)數(shù)據(jù)的Object的和一個(gè)數(shù)據(jù)清洗后臺(tái)設(shè)置的Object的2，數(shù)據(jù)導(dǎo)入頁(yè)面csv3，定義每個(gè)字段的范圍、屬性，如果是錯(cuò)誤的則自動(dòng)重新分配，或者修改成臨近值4，數(shù)據(jù)清洗合并。5，導(dǎo)出錯(cuò)誤數(shù)據(jù)到Excel第一步，新建立兩個(gè)關(guān)聯(lián)的Recruit和RecruitDepartment，并且建立一個(gè)清洗規(guī)則的Object，當(dāng)導(dǎo)入數(shù)據(jù)后我們可以讀取設(shè)置的清洗規(guī)則，并對(duì)導(dǎo)入的數(shù)據(jù)進(jìn)行清洗第二步，對(duì)于清洗規(guī)則，我們只能有一條規(guī)則被激活，因此我們?cè)诓迦胄乱?guī)則和更改舊規(guī)則的時(shí)候，我們需要添加一個(gè)tirgger針對(duì)Data_Washing_Setting，保證規(guī)則的唯一性。復(fù)制代碼1triggerIsActiveCheckingonData_Washing_Setting__c(beforeinsert,beforeupdate){23List<Data_Washing_Setting__c>ListOldData=[selectIdfromData_Washing_Setting__c4whereActive_this_Rule__c=true];5List<Data_Washing_Setting__c>ListNewData=trigger.new;67//system.debug('ListNewData:'+ListNewData.size());8integeritemNum=0;9if(trigger.isInsert)10{11if(trigger.isBefore)12{13for(Data_Washing_Setting__cdws:trigger.new)14{15if(dws.Active_this_Rule__c)16{17itemNum++;18}19}20itemNum+=ListOldData.size();2122if(itemNum>1)23{24for(Data_Washing_Setting__cdws:trigger.new){25dws.adderror('onlyonerecordcanbeactived!plscheckyourhistorydataandtryagain.');26}27}28}29}30elseif(trigger.isUpdate)31{32if(trigger.isBefore)33{34//去掉更新的數(shù)據(jù)35for(Data_Washing_Setting__cdws:trigger.new)36{37for(integeri=0;i<ListOldData.size();i++){38if(dws.Id==ListOldData[i].Id)39{40ListOldData.remove(i);41}42}43if(dws.Active_this_Rule__c)44{45itemNum++;46}47}48itemNum+=ListOldData.size();49if(itemNum>1)50{51for(Data_Washing_Setting__cdws:trigger.new){52dws.adderror('onlyonerecordcanbeactived!plscheckyourhistorydataandtryagain.');53}54}5556}57}58}復(fù)制代碼第三步，我們需要建立導(dǎo)入頁(yè)面，并添加相應(yīng)的驗(yàn)證按鈕VF的代碼復(fù)制代碼1<apex:pagecontroller="BatchInsertByCsvController">2<apex:form>3<apex:sectionHeadertitle="UploadRecruitData"/>4<apex:pageMessages/>5<apex:pageblock>6<center>7<apex:inputFilevalue="{!contentFile}"fileName="{!fileName}"/>8<apex:commandButtonaction="{!LoadData}"value="BatchInsert"/>9<apex:commandButtonaction="{!LoadBlankList}"value="FilterBlankData"/>10<apex:commandButtonaction="{!ExportBlankToCSV}"value="ExportCSV"/>1112</center>13</apex:pageblock>14<apex:pageBlocktitle="ImportData">15<apex:pageblocktablevalue="{!RecruitList}"var="ReList">16<apex:columnvalue="{!ReList.Name}"/>17<apex:columnvalue="{!ReList.Position_Name__c}"/>18<apex:columnvalue="{!ReList.Recruit_Department__c}"/>19<apex:columnvalue="{!ReList.Recruit_Type__c}"/>20<apex:columnvalue="{!ReList.Recruit_Number__c}"/>21</apex:pageblocktable>22</apex:pageBlock>23<apex:pageBlocktitle="BlankData">24<apex:pageblocktablevalue="{!BlankList}"var="BList">25<apex:columnvalue="{!BList.Name}"/>26<apex:columnvalue="{!BList.Position_Name__c}"/>27<apex:columnvalue="{!BList.Recruit_Department__c}"/>28<apex:columnvalue="{!BList.Recruit_Type__c}"/>29<apex:columnvalue="{!BList.Recruit_Number__c}"/>30</apex:pageblocktable>31</apex:pageBlock>32</apex:form>33</apex:page>復(fù)制代碼后臺(tái)APEX導(dǎo)入代碼復(fù)制代碼1publicclassBatchInsertByCsvController{23publicstringfileName{get;set;}4//Blob:二進(jìn)制對(duì)象類(lèi)型。通過(guò)inputFile選中后的文件在后臺(tái)獲取的時(shí)候是一個(gè)Blob類(lèi)型，5publicBlobcontentFile{get;set;}6publicString[]filelines=newString[]{};7publicList<Recruit__c>RecruitList{get;set;}8publicList<Recruit__c>BlankList{get;set;}9publicList<Recruit__c>invaildList{get;set;}10//初始化11publicPageReferenceLoadData()12{13try{14filename=bitToString(contentFile,'ISO-8859-1');15filelines=fileName.split('\n');16//ApexPages.Messagemsgs=newApexPages.Message(ApexPages.Severity.INFO,'importaccount:'+filelines.size());17//ApexPages.addMessage(msgs);18RecruitList=newList<Recruit__c>();19string[]inputvalues;20stringSwpNumber;2122for(Integeri=1;i<filelines.size();i++)23{24inputvalues=newstring[]{};25inputvalues=filelines[i].split(',');26Recruit__crecruits=newRecruit__c();27recruits.Name=inputvalues[0];28recruits.Position_Name__c=inputvalues[1];29recruits.Recruit_Department__c=[SELECTId30FROMRecruit_Department__c31WHEREName=:inputvalues[2]LIMIT1].Id;32recruits.Recruit_Type__c=inputvalues[3];33SwpNumber=inputvalues[4];34recruits.Recruit_Number__c=Decimal.valueOf(SwpNumber.trim());35RecruitList.add(recruits);36}37}38catch(exceptione){39ApexPages.Messageerrormsg=newApexPages.Message(ApexPages.Severity.ERROR,'AnerrorhasoccuredreadingtheCSVfile:'+e.getMessage());40ApexPages.addMessage(errormsg);41}42try{43//insertRecruitList;44//ApexPages.MessagesuccessMsg=newApexPages.Message(ApexPages.severity.INFO,'importsuccess');45//ApexPages.addMssage(successMsg);46}47catch(Exceptione)48{49//ApexPages.Messageerrormsg=newApexPages.Message(ApexPages.severity.ERROR,'Anerrorhasoccuredinsertingtherecords'+e.getMessage());50//ApexPages.addMessage(errormsg);51}52returnnull;53}54//blob是二進(jìn)制存儲(chǔ)的，String是16進(jìn)制存儲(chǔ)的，所以使用此種方式加上編碼解碼等操作肯定會(huì)更加適應(yīng)，包括中文55privateStringbitToString(Blobinput,StringinCharset){56//轉(zhuǎn)換成16進(jìn)制57Stringhex=EncodingUtil.convertToHex(input);58//一個(gè)String類(lèi)型兩個(gè)字節(jié)32位（bit）,則一個(gè)String長(zhǎng)度應(yīng)該為兩個(gè)16進(jìn)制的長(zhǎng)度，所以此處向右平移一個(gè)單位，即除以259//向右平移一個(gè)單位在正數(shù)情況下等同于除以2，負(fù)數(shù)情況下不等60//eg900001001>>100000100結(jié)果為461finalIntegerbytesCount=hex.length()>>1;62//聲明String數(shù)組，長(zhǎng)度為16進(jìn)制轉(zhuǎn)換成字符串的長(zhǎng)度63String[]bytes=newString[bytesCount];64for(Integeri=0;i<bytesCount;++i){65//將相鄰兩位的16進(jìn)制字符串放在一個(gè)String中66bytes[i]=hex.mid(i<<1,2);67}68//解碼成指定charset的字符串69returnEncodingUtil.urlDecode('%'+String.join(bytes,'%'),inCharset);70}71//篩選空值72publicPageReferenceLoadBlankList()73{74try75{76BlankList=newlist<Recruit__c>();77DataWashingSettingdws=newDataWashingSetting();78string[]flines=dws.AddQuestionsData(filelines);79string[]inputvalues;80stringSwpNumber;8182for(Integeri=0;i<flines.size();i++)83{84inputvalues=newstring[]{};85inputvalues=flines[i].split(',');86Recruit__crecruits=newRecruit__c();87recruits.Name=inputvalues[0];88recruits.Position_Name__c=inputvalues[1];89recruits.Recruit_Department__c=[SELECTId90FROMRecruit_Department__c91WHEREName=:inputvalues[2]LIMIT1].Id;92recruits.Recruit_Type__c=inputvalues[3];93SwpNumber=inputvalues[4];94recruits.Recruit_Number__c=Decimal.valueOf(SwpNumber.trim());95BlankList.add(recruits);96}97ApexPages.Messagemsgs=newApexPages.Message(ApexPages.Severity.INFO,'blanknum:'+BlankList.size());98ApexPages.addMessage(msgs);99}100catch(Exceptione)101{102ApexPages.Messageerrormsg=newApexPages.Message(ApexPages.Severity.ERROR,'AnerrorhasoccuredreadingtheCSVfile:'+e.getMessage());103ApexPMessage(errormsg);104}105returnnull;106}107publicPageReferenceExportBlankToCSV()108{109returnnewPageReference('/apex/ExportCSV');110}111}復(fù)制代碼后臺(tái)調(diào)用的驗(yàn)證清洗代碼，可以根據(jù)需要任意添加復(fù)制代碼1publicclassDataWashingSetting{23//消除重復(fù)數(shù)據(jù)4publicList<Recruit__c>DelDuplicateData(List<Recruit__c>OriginalList)5{6set<Recruit__c>myset=newset<Recruit__c>();7List<Recruit__c>result=newList<Recruit__c>();89myset.addAll(OriginalList);10result.addAll(myset);1112returnresult;13}14//篩選為空數(shù)據(jù)15publicstring[]AddQuestionsData(string[]filelines)16{17string[]result=newstring[]{};18string[]inputvalues;19for(Integeri=1;i<filelines.size();i++)20{21inputvalues=newstring[]{};22inputvalues=filelines[i].split(',');23if(inputvalues[0]==''||inputvalues[1]==''||inputvalues[2]==''24||inputvalues[3]==''||inputvalues[4]=='')25{26result.add(filelines[i]);27}28}29returnresult;30}31//檢測(cè)各個(gè)字段的合理性32publicstring[]CheckFiled(string[]filelines)33{34//讀取規(guī)則35Data_Washing_Setting__cdws=[selectPosition_Name_Rule__c,36Recruit_End_Number__c,Recruit_Department_Rule__c,Recruit_Start_Number__cfromData_Washing_Setting__cwhereActive_this_Rule__c=true];37stringPositionNameRule=dws.Position_Name_Rule__c;//部門(mén)規(guī)則是否允許重復(fù)38decimalstartNumber=dws.Recruit_Start_Number__c;//招聘人數(shù)底線39decimalendNumber