insert flat file to database

[chingc]

Hi,

I need to import data from a flat file to the Oracle database. The file is
not a csv file. The file is not a comma delimited file. It is a fixed length
file (each record is 450 characters). Some of the fields also contain ?zoned
decimal? values. The size of the file is 300,000 K. In addition, each record
is in a variant record format. That is each record has a primary record type,
let?s say from position 1 to 6. Depends on the value of the primary record
type, we then determine the sub record type (can locate in different location).
Then depends on the sub record type, we determine the rest of the data.

I am using ColdFusion MX version 6. How can I use ColdFusion to perform the
above task? If I can?t use ColdFusion to do this, does anyone one know what
tool I can use? Thanks for your help in advance.

[JMGibson3]

I just happen to be working on a project like that right now.
The typical Import method is CFFILE to read the file, and a List type CFLOOP,
using CRLF's as the delimiter to process each record, and Mid() functions to
split out fields based on record types. I've attached my current code as a
starting point.

Problem I forsee for you is File Size. My file is 500K and runs in 30 seconds
on a fairly primitive server. At 300,000K, your file would take at least 5
hours on my server. Certainly much much more with memory swapping. You will
have to carve that beast into chunks, and/or reduce the 450 record length to
the absolute minimum fields you need.

<cfset impPath ="\\WBK_COMMON_FS1\CTM_DATA\COLDFUSION_IMPORTS\ ">
<cfset impArchPath ="\\WBK_COMMON_FS1\CTM_DATA\COLDFUSION_SUCCESSFUL_ IMPORTS\">
<cfset impFileName = "Payroll_Data.txt">
<cfset impFile = "#impPath##impFileName#">

<cfset impFrom = DateFormat(DateAdd("d","-6",Session.ImpDate),"YYMMDD")>
<cfset impThru = DateFormat(Session.ImpDate,"YYMMDD")>
<cfset wrkImpStat = "OK">

<!--- Import Active AS400 Time Data --->
<cffile ACTION ="read"
FILE ="#impFile#"
VARIABLE="varWork">


<cfset impRowCtr = 0>
<cfset impAddCtr = 0>
<cfset impBypCtr = 0>
<cfset impDupCtr = 0>
<cfset impBadCtr = 0>

<cfset impHiDate="01/01/00">
<cfset logLStat = "OK">
<cfset logLMsg = "Imported">
<cfset logLeMsg = impFile>

<!--- Delete what we're Importing --->
<CFQUERY datasource=#constPrimaryDataSource# NAME="delAS400">
DELETE *
FROM tblAS400
WHERE WeekEnding = '#impThru#'
</CFQUERY>

<!--- Insert Imports one by one --->
<CFLOOP Index="varLine" List="#varWork#" Delimiters="#chr(13)##chr(10)#">
<cfset impRowCtr = impRowCtr + 1>
<cfset iWorkDate = Mid(varLine,001,006)>
<cfif iWorkDate LT impFrom OR
iWorkDate GT impThru>
<cfset impBypCtr = impBypCtr + 1>
<cfelse>
<cfset iSelShift = Mid(varLine,007,001)>
<cfset iPayGroup = Mid(varLine,008,003)>
<cfset iPaySeq = Mid(varLine,011,003)>
<cfset iJobCode = Mid(varLine,014,007)>
<cfset iKeySub = Mid(varLine,021,001)>
<cfset iWeekDay = Mid(varLine,022,001)>
<cfset iDayDesc = Mid(varLine,023,011)>
<cfset iActShift = Mid(varLine,034,001)>
<cfset iTourSwing = Mid(varLine,035,001)>
<cfset iTour40 = Mid(varLine,036,001)>
<cfset iProcCode = Mid(varLine,037,001)>
<cfset iJobSpec = Mid(varLine,038,001)>
<cfset iPrintCode = Mid(varLine,039,001)>
<cfset iPosType = Mid(varLine,040,001)>
<cfset iTourAssign = Mid(varLine,041,001)>
<cfset iJobName = Mid(varLine,042,018)>
<cfset iDept = Mid(varLine,062,005)>
<cfset iEmpNo = Mid(varLine,067,005)>
<cfset iShiftDiff = Mid(varLine,072,003)>
<cfset iActHH = Mid(varLine,075,003)>
<cfset iComp40HH = Mid(varLine,078,003)>
<cfset iOTCode = Mid(varLine,081,001)>
<cfset iOTHH = Mid(varLine,082,003)>
<cfset iAllowCode = Mid(varLine,085,001)>
<cfset iAllowHH = Mid(varLine,086,003)>
<cfset iPayRate = Mid(varLine,089,004)>
<cfset iRateCode = Mid(varLine,093,001)>
<cfset iElement = Mid(varLine,094,003)>
<cfset iPayrollCode = Mid(varLine,097,001)>
<cfset iBlankLines = Mid(varLine,098,002)>
<cfset iTrainCode = Mid(varLine,100,001)>
<cfset iSwapFlag = Mid(varLine,101,001)>
<cfset iTimeOffFlag = Mid(varLine,102,001)>
<!--- From here on, not captured --->
<cfset iLastUpdBy = Mid(varLine,107,004)>
<cfset iAS4RunDate = Mid(varLine,111,006)>
<cfset iAS4RunTime = Mid(varLine,117,006)>
<cfset iAS4RunUser = Mid(varLine,123,004)>
<cfset iAS4RunTerm = Mid(varLine,127,002)>
<cfset iTempJob = Mid(varLine,129,007)>
<cfset iWKYJB = Mid(varLine,133,007)>
<cfset iJBTRN = Mid(varLine,137,007)>
<cfset iPYRAT = Mid(varLine,141,005)>
<cfset iPRCD = Mid(varLine,144,001)>
<cftry>
<CFSET impAddCtr = impAddCtr + 1>
<CFQUERY datasource=#constPrimaryDataSource# NAME="impAS400">
INSERT INTO tblAS400 (
WeekEnding,
EmpNo,
WeekDay,
ActShift,
WorkDate,
SelShift,
PayGroup,
PaySeq,
JobCode,
KeySub,
DayDesc,
TourSwing,
Tour40,
ProcCode,
JobSpec,
PrintCode,
PosType,
TourAssign,
JobName,
Dept,
ShiftDiff,
ActHH,
Comp40HH,
OTCode,
OTHH,
AllowCode,
AllowHH,
PayRate,
RateCode,
Element,
PayrollCode,
BlankLines,
TrainCode,
SwapFlag,
TimeOffFlag
)
VALUES (
'#impThru#',
'#iEmpNo#',
'#iWeekDay#',
'#iActShift#',
'#iWorkDate#',
'#iSelShift#',
'#iPayGroup#',
'#iPaySeq#',
'#iJobCode#',
'#iKeySub#',
'#iDayDesc#',
'#iTourSwing#',
'#iTour40#',
'#iProcCode#',
'#iJobSpec#',
'#iPrintCode#',
'#iPosType#',
'#iTourAssign#',
'#iJobName#',
'#iDept#',
'#Left(iShiftDiff,1)#.#Right(iShiftDiff,2)#',
'#Left(iActHH,2)#.#Right(iActHH,1)#',
'#Left(iComp40HH,2)#.#Right(iComp40HH,1)#',
'#iOTCode#',
'#Left(iOTHH,2)#.#Right(iOTHH,1)#',
'#iAllowCode#',
'#Left(iAllowHH,2)#.#Right(iAllowHH,1)#',
'#Left(iPayRate,2)#.#Right(iPayRate,2)#',
'#iRateCode#',
'#iElement#',
'#iPayrollCode#',
'#iBlankLines#',
'#iTrainCode#',
'#iSwapFlag#',
'#iTimeOffFlag#'
)
</CFQUERY>
<cfcatch type="Database">
<CFSET impAddCtr = impAddCtr - 1>
<cfif CFCatch.SQLSTATE eq 23000 or
CFCatch.SQLSTATE eq 3022>
<cfset impDupCtr = impDupCtr +1>
<cfelse>
<cfset logLStat = CFCatch.SQLSTATE>
<cfset logLMsg = CFCatch.Message>
<cfset logLeMsg = CFCatch.Detail>
<cfset logLeMsg = ListGetAt(logLeMsg,1,"<")>
<cfset impBadCtr = impBadCtr +1>
</cfif>
</cfcatch>
</cftry>
</cfif>
</cfloop>

[chingc]

Thank you very much. It helpsme a log.