Hello,
I need to import a large 2 megs or so 271 EDI file into sql tables. EDI files
are not fun to parse, but I have it working fine. The problem is speed. It is
slow to me... 61 sec to parse 80k file (171 records).
Any ideas on how to improve it? This will be moved to a hosted site, I was
told that the site limits java calls.
BTW thanks Ray for a great udf......
Thanks in advance.
-David
Below is the code:
<!--- Set the path from this template --->
<cfset request.path = getDirectoryFromPath(GetBaseTemplatePath()) &
"\download\">
<!--- Write the uploaded file from the form page to the path and as
uploadedfile.csv --->
<!--- <cffile action="UPLOAD" destination="#request.path#271toparse.edi"
nameconflict="OVERWRITE" filefield="form.uploadfilename"> --->
<cfscript>
/**
* Fixes a list by replacing null entries.
* This is a modified version of the ListFix UDF
* written by Raymond Camden. It is significantly
* faster when parsing larger strings with nulls.
* Version 2 was by Patrick McElhaney ([EMAIL PROTECTED])
*
* @param list The list to parse. (Required)
* @param delimiter The delimiter to use. Defaults to a comma. (Optional)
* @param null Null string to insert. Defaults to "NULL". (Optional)
* @return Returns a list.
* @author Steven Van Gemert ([EMAIL PROTECTED]@placs.net)
* @version 3, July 31, 2004
*/
function listFix(list) {
var delim = ",";
var null = "NULL";
var special_char_list = "\,+,*,?,.,[,],^,$,(,),{,},|,-";
var esc_special_char_list = "\\,\+,\*,\?,\.,\[,\],\^,\$,\(,\),\{,\},\|,\-";
var i = "";
if(arrayLen(arguments) gt 1) delim = arguments[2];
if(arrayLen(arguments) gt 2) null = arguments[3];
if(findnocase(left(list, 1),delim)) list = null & list;
if(findnocase(right(list,1),delim)) list = list & null;
i = len(delim) - 1;
while(i GTE 1){
delim = mid(delim,1,i) & "_Separator_" & mid(delim,i+1,len(delim) -
(i));
i = i - 1;
}
delim = ReplaceList(delim, special_char_list, esc_special_char_list);
delim = Replace(delim, "_Separator_", "|", "ALL");
list = rereplace(list, "(" & delim & ")(" & delim & ")", "\1" & null & "\2",
"ALL");
list = rereplace(list, "(" & delim & ")(" & delim & ")", "\1" & null & "\2",
"ALL");
return list;
}
</cfscript>
<cfset filename = "271toparse.edi">
<!--- --->
<cffile action="READ" file="#request.path#271toparse.edi" variable="edifile">
<!--- <cffile action="READ" file="#path##filename#" variable="edifile"> --->
<cfset edifile = replacenocase(edifile,"~st","|","all")>
<cfset qryEdiData = queryNew("rectype,recnumber,segtype,segmentdata")>
<cfloop index="records" list="#edifile#" delimiters="|">
<cfif ListGetAt(listgetAt(records,1,"~"),1,"*") EQ "271">
<cfloop index="segments" from="2" to="#listLen(records,'~')#">
<cfset queryaddrow(qryEdiData,1)>
<cfset
querySetCell(qryEdiData,"recType",trim(ListGetAt(listgetAt(records,1,"~"),1,"*")))>
<cfset
querySetCell(qryEdiData,"recnumber",trim(ListGetAt(listgetAt(records,1,"~"),2,"*")))>
<cfset
querySetCell(qryEdiData,"segtype",trim(ListGetAt(ListGetAt(records,segments,"~"),1,"*")))>
<cfset
querySetCell(qryEdiData,"segmentdata",trim(ListGetAt(records,segments,"~")))>
</cfloop>
</cfif>
</cfloop>
<cffunction name="InsertData" access="private" output="No" returntype="void">
<cfargument name="patientProfileID" required="Yes" type="numeric">
<cfargument name="TransSetControlNumber" required="Yes" type="numeric">
<cfargument name="HLIDno" required="Yes" type="numeric" default="0">
<cfargument name="segData" required="Yes" type="string">
<cfargument name="batchID" required="Yes" type="string">
<cfset var qryInsertData = "">
<cfset var strSegType = "">
<cfset var cfarr1 = "">
<cfset strSegType = listfirst(arguments.segData,"*")>
<cfswitch expression="#strSegType#">
<cfcase value="BHT">
<cfquery name="qryInsertData"
datasource="mevs">
insert into tbl_bht
(patientProfileID,TransSetControlNumber,SubScriberStrutCode,PurposeCode,SubmitterTransId,TransCreateDate,TransCreateTime)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#',
<cfloop index="fields"
list="#arguments.segData#" delimiters="*">
<cfif fields neq
listFirst(arguments.segData,"*")>
<cfif isDate(fields)>
'#dateformat(fields,'mm/dd/yy')#'
<cfelse>
'#fields#'
<cfif fields neq listlast(arguments.segData,"*")>,</cfif>
</cfif>
</cfif>
</cfloop>)
</cfquery>
</cfcase>
<cfcase value="NM1">
<cfif listGetAt(arguments.segData,2,"*") EQ
"PR">
<cfquery name="qryInsertData"
datasource="mevs">
insert into tbl_NM1PR
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,EntityIDCode,EntityType,OrganizationalName,PayerId,MedicaidNo)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#','#ListGetAt(arguments.segData,2,"*")#','#ListGetAt(arguments.segData,3,"*")#','#ListGetAt(arguments.segData,4,"*")#','#ListGetAt(arguments.segData,9,"*")#','#ListGetAt(arguments.segData,10,"*")#')
</cfquery>
<cfelseIf listGetAt(arguments.segData,2,"*") EQ
"1P">
<cfquery name="qryInsertData"
datasource="mevs">
insert into tbl_NM1P
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,EntityIDCode,EntityType,OrganizationalName,IDCode,ProviderId)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#','#ListGetAt(arguments.segData,2,"*")#','#ListGetAt(arguments.segData,3,"*")#','#ListGetAt(arguments.segData,4,"*")#','#ListGetAt(arguments.segData,9,"*")#','#ListGetAt(arguments.segData,10,"*")#')
</cfquery>
<cfelseIf listGetAt(arguments.segData,2,"*") EQ
"IL">
<cfquery name="qryInsertData"
datasource="mevs">
insert into tbl_NM1IL
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,InsuredSubscriber,EntityType<!---
,LastName,FirstName,MiddleInitial,RecipientNo,MedicaidIDNo --->)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#','#ListGetAt(arguments.segData,2,"*")#'
,'#ListGetAt(arguments.segData,3,"*")#'<!---,'#ListGetAt(arguments.segData,4,"*")#','#ListGetAt(arguments.segData,9,"*")#','#ListGetAt(arguments.segData,10,"*")#'
--->)
</cfquery>
</cfif>
</cfcase>
<cfcase value="REF">
<cfquery name="qryInsertData" datasource="mevs">
insert into tbl_ref
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,RefIDQualifer,IDNumber<cfif
listgetAT(arguments.segData,2,"*") EQ "IG">,Carrier</cfif>)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#',
<cfloop index="fields"
list="#arguments.segData#" delimiters="*">
<cfif fields neq
listFirst(arguments.segData,"*")>
'#replace(fields,"'","","all")#' <cfif fields neq
listlast(arguments.segData,"*")>,</cfif>
</cfif>
</cfloop>)
</cfquery>
</cfcase>
<cfcase value="AAA">
<cfquery name="qryInsertData" datasource="mevs">
insert into tbl_aaa
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,RequestStatus,RejectReasonCode,ActionCode)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#','#ListGetAt(arguments.segData,2,"*")#','#ListGetAt(arguments.segData,4,"*")#','#ListGetAt(arguments.segData,5,"*")#')
</cfquery>
</cfcase>
<cfcase value="eb">
<cfset eb_detail_firstPass =
listDeleteAt(arguments.segData,1,"*")>
<cfset eb_detail =
listDeleteAt(eb_detail_firstPass,1,"*")>
<cfquery name="qryInsertData" datasource="mevs">
insert into tbl_eb
(patientProfileID,TransSetControlNumber,HierachicalLevelIDNo,eb_status,eb_detail)
values
(#arguments.patientProfileID#,'#arguments.TransSetControlNumber#','#arguments.HlIdNo#','#ListGetAt(arguments.segData,2,"*")#','#eb_detail#')
</cfquery>
</cfcase>
</cfswitch>
</cffunction>
<!--- <cfdump var="#QryEdiData#"> --->
<cfset HLIDno = "0">
<cfloop query="QryEdiData">
<cfquery name="FindTrnID" dbtype="query">
select segmentData
from qryEdiData
where qryEdiData.recnumber = #trim(QryEdiData.recnumber)# and
segType = 'TRN'
</cfquery>
<cfif SegType EQ "HL">
<cfset HLIDno = ListGetAt(SegmentData,2,"*")>
</cfif>
<cfset PatientProfileID = ListGetAt(FindTrnID.segmentdata,3,"*")>
<cfquery name="GetBatchID" datasource="Mevs">
select batchid
from tbl_MedicaidVerification
where id = #PatientProfileID#
</cfquery>
<cfloop index="fields" list="#listFix(qryEdiData.segmentData,'*')#"
delimiters="*">
<cfoutput>#fields# </cfoutput>
</cfloop>
<cfset tmp =
InsertData(patientProfileID,recnumber,HLIDno,listFix(qryEdiData.segmentData,'*'),GetBatchID.batchid)>
</cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287107
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
